Delete Old Records from Database using Cron Job

[Farcell]

Hi there, I want to delete old archived customer records from my database automatically which have been there for more than 7 days. So, I started with a cron job which runs once a day and I tried the PHP script below

$myDb->setQuery("DELETE FROM `customer` WHERE `customer_archive` = '1' AND `delivery_date` < DATE_SUB(NOW(), INTERVAL 7 DAY)");
$myDb->execute();

Can you tell me where I went wrong?

 

[troester]

What is your problem (cron job doesn't run at all? it doesn't run automatically'...)

Is this your complete code? You'll need

$myDb = JFactory::getDbo();

$myQuery = $myDb->getQuery(true);

Does it run manually in backend?

 

[Farcell]

Hi there, that wasn't my complete code, this is

<?php
// No direct access
defined('_JEXEC') or die('Restricted index access');

// get a db object
$myDb = FabrikWorker::getDbo();
$myQuery = $myDb->getQuery(true);
$myDb->setQuery("DELETE FROM `customer` WHERE `customer_archive` = '1' AND `delivery_date` < DATE_SUB(NOW(), INTERVAL 7 DAY)");
$myDb->execute();

?>

With the exception of - $myQuery = $myDb->getQuery(true);
which I just added and run on Cron manually with no luck. I want to get rid of customer records which are 7 days past the delivery day, because later on I don't my Cron jobs sifting through records that shouldnt be there.

 

[troester]

Where did you put your code? File or "PHP code" field?
In a code field you must not use <?php
Enable "Log Events" and have a look at the #__fabrik_log table.

 

[Farcell]

Hi troester, I put the php file in plugins/fabrik_cron/php/scripts/Remove Customer From Database.php and selected the php from within the schedule item I created.

The cron runs, to no effect (with no errors messages, returning back to the schedule menu) and yes I have enabled log events before and checked the table (#__fabrik_log) the 3 times I ran the script, to see if anything would be stored in the table, but nothing was stored, just the previous issues I had from before.

 

[cheesegrits]

That should work. First step is figure out if the code is actually running, so just put an ...

exit;

... as the first line, and see if you get a blank screen when you hit the "Run" button.

-- hugh

 

[Farcell]

Did not get a blank page but got it to work, it was something really silly, sorry for wasting your time guys. Initially, I named the PHP file, Remove Customer From Database.php with spaces, but when I renamed it Remove-Customer-From-Database.php, it worked. Again, really sorry for wasting your time, should have known better.

 

[cheesegrits]

Ah, OK. I did wonder about that, but I would have expected something to error out if it couldn't load the file name.

-- hugh