Cron Job

[Collegeboox]

Does anyone know how to write the php for a cron job (delete date from a db) this is what I have but it is not doing anything...the cron on my server site runs the code fine but the code does not do anything.

 

<?php

$date = ('Y-m-d');

//Auto delete

// Connect to MySQL
$connect = mysql_connect("db","username","password") or die("Not connected");
mysql_select_db("name") or die("could not log in");

// Delete entry  where date equals today from the "example" MySQL table
mysql_query("DELETE FROM boox WHERE date='$date'") 
or die(mysql_error());  


//
?>

[Pikachu2000]

Does the script do what it's supposed to if you execute it via a browser?

[Collegeboox]

No it does nothing

[Pikachu2000]

So after it runs, you can view the database, and the records haven't been deleted, right? What is the data type of the `date` field in the table?

Edit your database credentials into this code and post the output, please.

 

$connect = mysql_connect("db","username","password") or die("Not connected");
mysql_select_db("name") or die("could not log in");

$query = "SELECT date FROM boox LIMIT 10";
if( !result = mysql_query($query) ) {
     echo mysql_error();
} else {
     while( $array = mysql_fetch_row($result) ) {
          echo $array[0] . "<br>";
     }
}

[Collegeboox]

If i run it by typing it in the address bar I get...

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM boox WHERE date='Y-m-d'' at line 1

[Jessica]

You're not showing us the right code then. There is not a single * in your code.

[Pikachu2000]

Wait a minute. I just spotted a typo in the original code. $date = ('Y-m-d') is not what you need. It literally assigns the value 'Y-m-d' to $date. Remove that line, then change the query string to this. As long as the date is formatted correctly, this should work just fine.

mysql_query("DELETE FROM boox WHERE date = CURDATE()")

 

[Collegeboox]

thank you pika, everything worked perfect. I have one more question I was wondering how to take current date and subtract a month? I am trying to delete posts that have been up for a month.

[Pikachu2000]

You'd need to use the DATE_SUB() MySQL function.

 

WHERE date < DATE_SUB( CURDATE(), INTERVAL 1 month )