About auto publish and sending expiration notifications

$days_intervals = array(30, 14, 7, 0); foreach($days_intervals as $days) { $query = 'SELECT i.id, i.publish_down, i.title ' // more cols ... .' FROM #__content AS i' // find previous notification record (if any ... using LEFT join) .' LEFT JOIN #__content_expired_notifications AS n .' ON n.item_id=i.id' .' WHERE' // Send notifications only for published content '. i.state IN (1,-5)' // that have expired ($days=0), or are $days before expiration .' AND i.publish_down > DATE_SUB(NOW(), INTERVAL '.$days.' DAY)' // but the notification date differs, // so no NEW notification was sent yet !! .' AND n.publish_down!=i.publish_down' ; $db->setQuery($query); $notify_items_list = $db->loadObjectList(); // NOTE 1: last AND clause detects different published_down date for a re-published_item !! so next time it re-expires e.g. after a year we will re-send a new notification // NOTE 2: if you use CRON, LOAD joomla framework (google to find how to this) or DO NOT load it and use PHP mysqli commands foreach ($notify_items_list as $item) { // 1. code to send notification email // ... // 2. Update notifications DB table, so that next run will not $query = 'REPLACE INTO #__content_expired_notifications' .' (item_id, publish_down, notify_days)' .' VALUES('. $item->id.', '. $db->Quote($item->publish_down).', '. $days .')'; $db->setQuery($query); $db->query(); // 3. create a log file or append to a log file // ... } } // end of foreach days, note THAT the VERY FIRST run will send for unexpired item 4 notifications at once BECAUSE the notifications table is empty, final code will be handling this ...