[SOLVED] Simple script>Delete old rows MYSQL

[eevan79]

Hello,

 

I am a beginner it is regarding PHP and I hope that someone can help me about these tidbits. I need a script that deletes the rows older than  (for example) 3 months in the MySQL database. It is about the Joomla CMS.

 

This is the script that I wrote:

 

<?php
  include "connect.php";

  $time = 'SELECT sysdate() FROM dual';
  $stime = mysql_query($time, $con);
  $row = mysql_fetch_array($stime);
  print("Database current time: ". $row[0] ."\n");
  
  $sql = "delete FROM jos_content where created < $time";
  $del = mysql_query($sql, $con);
   print(" --> Deleted rows: ". $del[0] ."\n");
  mysql_close($con); 
?>

 

So something is wrong (I know that is about date).

 

I'd like to script using the server date (and <3 months) and than execute query, so that all rows older than 3 months will be deleted.

Also I can use something like this:

 

  $sql = "delete FROM jos_content where created < '2009-03-03' ";

 

but want to set a cronjob to do the work. Only problem is to set date (server date < 3 month).

 

Also, I noticed that something is wrong with:    print(" --> Deleted rows: ". $del[0] ."\n");  - it wont display the number of deleted rows.

 

Suggestion?

 

Thanks in advance.

[Maq]

*** Not tested

 

Try:

 

$sql="DELETE FROM jos_content WHERE created $result = mysql_query($sql) or die(mysql_error());
$num_deleted = mysql_num_rows($result);
echo "deleted rows" . $num_deleted;

[kickstart]

Hi

 

Take it as you are using "dual" you have experience of using Oracle.

 

To get the date you use currdate. To add (or subtract) to it you use date_add. So to take off 90 days use DATE_ADD(CURDATE(), INTERVAL -90 DAY), or for 3 months DATE_ADD(CURDATE(), INTERVAL -3 MONTH)

 

mysql_query doesn't return the number of rows deleted, just true or false. If you need to know the number of rows deleted then you need to do the delete and then use mysql_affected_rows()  to get the number of deleted rows.

 

All the best

 

Keith

[eevan79]

Maq and kickstart,

 

thanks for fast feedback 

 

I'll try this.