Create New Page
 View Previous Changes    ( Last updated by Ingmar Greil )

Automatically close expired entries

Q: How can I close expired entries automatically?
A: By setting up a query that checks for expired entries that are not yet closed, and closes them. We could use the cron plugin for that purpose, but I found it desirable to run an external script via “real” cron. This script is here:

#!usr/local/bin/php -n

<?php global $PREFS;                            // ee_cli_sql.php v0.3.5

/* Copyright (c) 2003 - 2007 EllisLab, Inc.  ---  ExpressionEngine 1.6.0.

   Some code by Ingmar Greil, 2007. <office@ingmar.at>
   My modfications and code released under a Creative Commons
   "Attribution" license: http://creativecommons.org/licenses/by/2.0/

   This PHP command line script allows you to perform arbitrary
   SQL queries on your EE database. There will be no visible output
   (in this case we'd simply use the Query module in a template, right?),
   since the whole point is to run this script unattended.

   Put this file in your EE "system" folder, make sure the executable
   bit is set (chmod +x ee_cli_sql.php), then call manually or via cron.

   Try "crontab -e".
   "5 * * * * command" will run your script 5 minutes past the hour, every hour.
   "0,10,20,30,40,50 6-22 * * * 1-5" will run your script every ten minutes
   between 6am and 10pm, except on weekends. The general syntax is:
   <Minute> <Hour> <Day> <Month> <Day of Week> <Command line>


// This query will set all expired entries to "closed" status:

$query "      UPDATE `exp_weblog_titles` SET status = 'closed' WHERE status <> 'closed'
                AND expiration_date <> '0' AND expiration_date < UNIX_TIMESTAMP()"

// Change the above query to suit your needs.
// That's it, folks! No user-serviceable parts below.

define("EXT",".php");                           // Get around EE's security mechanisms. Kludgy? Hell, yes.
                                                // Got a better solution? I am all ears.
require("config".EXT);                          // Read the config file
require("core/core.prefs".EXT);                 // Load the PREFS cass

$PREFS = new Preferences(); $PREFS->core_ini $conf; unset($conf);

$db mysql_connect(                            // Handle the connection to the database:
$PREFS->core_ini['db_hostname'],          // hostname,
$PREFS->core_ini['db_username'],          // username and
$PREFS->core_ini['db_password']);         // password are all pulled automatically.

mysql_select_db($PREFS->core_ini['db_name']);   // Now it's selecting the appropriate db,
mysql_query($query,$db);                        // performing the actual query, then
mysql_close();                                  // cleaning up after ourselves. Done.

Category:Tricks Category:Queries