Execute cron job php function..

[drewpark88]

Hey guys,

I understand how to setup the cron job to run once a day. Now I am having trouble putting together a function to do the following:

I need to delete all content (rows) automatically where the "expire_date" field is equal to or greater than todays date. Keep in mind I need a php file like (expire.php) to make my cron job execute, so it needs to delete all expired content.

I am still new to PHP, but I figure trying to do something like this will help me learn a bit faster.

So far I have something like:

function delete_expired() {
    $current_date = date("m.d.y"); 
    $sql = "DELETE * FROM cms_table WHERE delete_date => $current_date"; 
    $res = mysql_query($sql) or die(mysql_error());


// NOT SURE WHAT TO PUT HERE, MAYBE AN IF STATEMENT? //


        }  // END delete_expired function

Any help would be greatly appreciated, thank you!

[pritaeas]

This is an sql issue. Mysql dates are in the format yyyy-mm-dd. Another method would be to use the mysql now() function, which represents the current date. Also DELETE * FROM is invalid.

$sql = "DELETE FROM cms_table WHERE delete_date >= NOW()";

[drewpark88]

Thanks Pritaeas, now I just need to tie it all together. Can you possibly provide an example of what this function would look like so that when "expire.php" is called from my cron job, it automatically deletes the expired content (rows)?

Either way, I appreciate the information you have provided and it was very helpful! :)

This is an sql issue. Mysql dates are in the format yyyy-mm-dd. Another method would be to use the mysql now() function, which represents the current date. Also DELETE * FROM is invalid.

$sql = "DELETE FROM cms_table WHERE delete_date >= NOW()";

[pritaeas]

I'm not sure what you want me to show you, that is not in my above code.

[drewpark88]

I am a novice php coder, mainly do front-end so I sometimes have trouble. What I am asking is an example of basic code to put in a php file (like expire.php) that can be executed by a cron job. If I have a cron job setup to execute "expire.php" once a day...what needs to be in the php file for the cronjob to activate the function? I am used to basic if statements, but this is new to me. I know I can't just put:

<?php

  function delete_expired() {
    $current_date = date("m.d.y"); 
    $sql = "DELETE FROM cms_table WHERE delete_date >= NOW()";
    $res = mysql_query($sql) or die(mysql_error());


// What do I put here? Some type of if statement? //


        }  // END delete_expired function

?>

I'm not sure what you want me to show you, that is not in my above code.

[mschroeder]

You have basically finished the script, with the exception of two things.
You need to call the function, and your SQL statement should be looking for any delete_date values that are less than or equal to the current time. If you leave it as >= you will be deleting all articles that are set to expire in the future.

I'm not sure how your dates are stored in your database, but $current_date is unused in your function, and NOW() will only work if the delete_date column is a date/datetime/timestamp etc. (http://dev.mysql.com/doc/refman/5.0/en/datetime.html)

Also just a note, in files that are purely php, it is good practice to drop the closing ?>. This prevents any issues with whitespace after the closing tag.

<?php

function delete_expired() {
    $current_date = date("m.d.y"); 
    $sql = "DELETE FROM cms_table WHERE delete_date <= NOW()";
    $res = mysql_query($sql) or die(mysql_error());
}

delete_expired();

[drewpark88]

Thanks mschroeder, you are awesome! That is exactly what I wanted to know. So a quick question on the date issue. What you you recommend would be the best way to store dates and use to retrieve todays date? I would like the dates in my database to be pretty easy for others to edit. Either way, thank you so much for all the help! :icon_biggrin:

You have basically finished the script, with the exception of two things.
You need to call the function, and your SQL statement should be looking for any delete_date values that are less than or equal to the current time. If you leave it as >= you will be deleting all articles that are set to expire in the future.

I'm not sure how your dates are stored in your database, but $current_date is unused in your function, and NOW() will only work if the delete_date column is a date/datetime/timestamp etc. (http://dev.mysql.com/doc/refman/5.0/en/datetime.html)

Also just a note, in files that are purely php, it is good practice to drop the closing ?>. This prevents any issues with whitespace after the closing tag.

<?php

function delete_expired() {
    $current_date = date("m.d.y"); 
    $sql = "DELETE FROM cms_table WHERE delete_date <= NOW()";
    $res = mysql_query($sql) or die(mysql_error());
}

delete_expired();

[mschroeder]

Personally I prefer to store dates using the mysql date or datetime format, usually the later, but it is a personal preference.

You'll often see a unix timestamp in an int column, a mysql timestamp cplumn, and even sometimes a date stored as a string, which is the only one I would say is unacceptable and should be avoided.

Just however you choose to handle your dates, stick with it, don't mix and match date types throughout the application.

[phper]

I prefer storing dates using the time() function.

Id then just do a query called

$today = mktime(00,00,00,date('n', time()), date('j', time()), date('Y', time()));
$query = "DELETE FROM datatable WHERE date_time > $today";
$result = mysql_query($query);

I find this easiest to use especially when I want to SELECT data between date ranges.

[mschroeder]

-phper

The problem I have with timestamps mostly has to do with the limited range they are available for, 1970 - 2038 approximately. If you were to try to store a date 30 years from today, say for a 30year mortgage, you will exhaust the 32bit integer timestamp.

Mysql's datetime and date data types do not suffer from this. It is also just as easy to do a range calculation with mysql's datetime columns.

e.g.

SELECT * FROM table WHERE table.`date` BETWEEN NOW() AND NOW() + INTERVAL 30 YEAR

Downside with that query would be the inability for mysql to cache its results because of the use of NOW(). However, if that is an issue it can be remedied by using the php DateTime class instead of the time() and date() functions, which does not suffer from the same range as the unix timestamp.

<?php
//Current DateTime
$now = new DateTime();
$start = $now->format( 'Y-m-d H:i:s' );

//add() is php 5.3.0 functionality
$now->add( new DateInterval('P30Y') );
//For php < 5.3.0
//$now->modify( '+30 year' );

$end = $now->format( 'Y-m-d H:i:s' );

$query = sprintf( 'SELECT * FROM table WHERE table.`date` BETWEEN "%s" AND "%s"', $start, $end );
echo $query; 
//SELECT * FROM table WHERE table.`date` BETWEEN "2011-02-22 20:20:52" AND "2041-02-22 20:20:52"

[drewpark88]

Thanks for the great suggestions for the use of Date formats! I think it is fair to mark this thread as Solved! :)