Deleting old dates from a database 1

[smashing]

Hi
I have a mysql database loaded with info. 1 column is called "dotrav" & it contains a date in the format of yyyy-mm-dd.
I want to delete all the rows in the database that are older than 2 months going back from today's date. The foolowing is the script I've written.


$currentyear = date("Y"
$thismonth = date("m"
$today = date("d"
$two_months_ago = ($thismonth - 2);
$olddate = "$currentyear-$two_months_ago-$today";


$delete = &quot;DELETE from reserve where dotrav <= '$olddate'&quot;;
$deleta =@mysql_query($delete, $connection)or die(mysql_error());


1) First of all it will only remove dates from 2 months ago but not 3 moths ago or older
2) I'm sure there's a better way to do this (maybe using the strtotime function and converting it to the Unix Timestamp but I'm not sure how to intergrate this with the Mysql statement)
Please help!!!

 

[sleipnir214]

Given the following table:

+------------+----------+
| the_date   | the_text |
+------------+----------+
| 2003-01-01 | bjan     |
| 2003-01-15 | mjan     |
| 2003-02-01 | bfeb     |
| 2003-02-15 | mfeb     |
| 2003-03-01 | bmar     |
| 2003-03-15 | mmar     |
| 2003-04-01 | bapr     |
| 2003-04-15 | mapr     |
| 2003-05-01 | bmay     |
+------------+----------+

this query:

DELETE FROM foo WHERE the_date <= DATE_SUB(NOW(), INTERVAL 2 MONTH)

Leaves this in the database:

+------------+----------+
| the_date   | the_text |
+------------+----------+
| 2003-03-15 | mmar     |
| 2003-04-01 | bapr     |
| 2003-04-15 | mapr     |
| 2003-05-01 | bmay     |
+------------+----------+

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[smashing]

Sorry for taking so long to answer, but am running in and out from office all day. Tried what you suggested but am getting a blank page. This is my statement:

$delete = &quot;DELETE FROM reserve WHERE dotrav <= DATE_SUB(NOW(), INTERVAL 2 MONTH)&quot;;

$deleta =@mysql_query($delete, $connection)or die(mysql_error());

 

[sleipnir214]

You're going to get a blank page. You're not fetching any data from the database, nor are you outputting anything, just deleting from the database.

Also, do not use the &quot;@&quot; error-supression operator, particularly in a development environment. You certainly want to see errors as you develop.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[smashing]

1) ok I removed the @ but still am not getting any output.
2) There is plenty of other output on the rest of the script including regular html. (and if I precede the above 2 lines (The Delete statement) with // everything else works fine!!!
Please help!!!

 

[sleipnir214]

Insufficient data for a meaningful answer.

Were the lines executed?
Was the data deleted?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[KarveR]

$delete = &quot;DELETE FROM reserve WHERE dotrav <= DATE_SUB(NOW(), INTERVAL 2 MONTH)&quot;;

$deleta =@mysql_query($delete, $connection)or die(mysql_error());

printf (&quot;Records deleted: %d\n&quot;, mysql_affected_rows());


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.