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!

Dave Edis - Senior Developer
interactivetools.com

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

Greg Thomas







PHP Programmer - interactivetools.com
Attachments:

deleteOldRecords.php 1K

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!

Dave Edis - Senior Developer
interactivetools.com

By rez - July 11, 2014

Incredible support!