Jump to content

[SOLVED] Simple script>Delete old rows MYSQL


eevan79

Recommended Posts

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.

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.