auto remove expired from database
6 posts by 3 authors in: Forums > CMS Builder
Last Post: July 11, 2014 (RSS)
By rez - July 2, 2014 - edited: July 2, 2014
Hello.
I have been using code to show upcoming and todays performances for a long time and manually erasing past shows. I simply enter dates in the cms and dont even have to worry about record display removal dates. Something like (pubs are open until 2am here):
list($scheduleRecords, $scheduleMetaData) = getRecords(array(
'tableName' => 'schedule',
'limit' => '4',
'loadUploads' => true,
'allowSearch' => false,
'where' => " date >= TIMESTAMP( DATE(NOW() - INTERVAL 2 HOUR) ) ",
));
Can you give me some magic code for the advanced fields, on the page or anywhere that will delete expired shows from the database? It would be fantastic if admins could quickly enter a band name and performance date and that's it. The rest is auto. :) I am noticing they don't go in and erase old shows. They leave a hundred records in there. By the way, it's important that I show a performance up until 2am in case someone is going to a pub after midnight and checks the website for the current performance.
thanks.
By Dave - July 3, 2014
Hi rez,
There's a few options, but the simplest is problems just to add the show "data" field to the list of fields displayed in the CMS record list, then click that column to sort oldest to newest, then click the checkbox in the header to auto-check all the records, then click "Erase Selected" from the Advanced Commands menu. If you want to erase more at a time, just increase the per page value, and if you don't want to erase some, just uncheck them.
That's what I do. You could also add an advanced search fields to let you search by date, etc. But just sorting by date and selecting the old ones might be the simplest.
Hope that helps!
interactivetools.com
By rez - July 3, 2014 - edited: July 3, 2014
If I understand correctly, you are describing how to manually log in and delete "expired" records as fast as possible. What I would like is to automate that process with CMSB.
If a show / record has already displayed on the site and the date passes, I never need it again. I would like it to be auto deleted from the database. No logging in, sorting or deleting anything. The date is in the CMS. The CMS doesn't display it past the date. How can we set it up so it is deleted when it expires instead of just toggling the display?
I hope that is more clear. :)
By gregThomas - July 4, 2014 - edited: July 4, 2014
Hi rez,
I've written a quick plugin that will delete all records that were created before 2am on the the current date, IT's attached to this post. Here is the code for the plugin:
<?php
/*
Plugin Name: Delete old records from the scedule section
Description: This plugin will delete old records from the example section.
Version: 0.01
Requires at least: 2.53
*/
addCronJob('delete_old_records', "Cron Example Script", '0 8 * * *'); // Run at 8am
// this function is called by cron, and logged under: Admin > General > Background Tasks
function delete_old_records() {
//Delete old records
mysql_delete('schedule', null, " `date` < TIMESTAMP( DATE(NOW() - INTERVAL 2 HOUR) ) ");
echo "Old records deleted!";
return true;
}
If you want to test the plugin function without having to wait for the cron to run, you can do so by writing a script that includes the viewer functions, and then call the delete_old_records function:
<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php
/* STEP 1: LOAD RECORDS - Copy this PHP code block near the TOP of your page */
// load viewer library
$libraryPath = 'cmsAdmin/lib/viewer_functions.php';
$dirsToCheck = array('C:/wamp/www/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }
delete_old_records();
Ensure that you back up the entire site and database before testing this plugin! I'd also recommend testing it staging version of the site before adding the plugin to the live site.
Thanks!
Greg
PHP Programmer - interactivetools.com
By Dave - July 4, 2014
Hi Rez,
Or just add this line above "addCronJob" to add a "run manually" feature:
// Plugin Menu - Add link to allow users to "Run Manually", this can be useful for testing
pluginAction_addHandlerAndLink(t('Run Manually'), 'delete_old_records', 'admins');
Cheers!
interactivetools.com