Date problem!

warmasice

Hi guys,

Im making a script to prune my table which currently holds a log of all bookings ever made.
I am trying to make a script that will delete all entries older than a certain number of days old,
say, $numberofDays = 60.

This is where im stuck. I have little idea how to go about this. I can confirm that my table stores
the date as yyyy-mm-dd format.

I was thinking somewhere along the lines of...

$aDate = date('Y-m-d', strtotime("-".$numberofDays." days"))
$sql="DELETE FROM bookings WHERE dateofbooking < aDate";

Does that make sense, surely not? Newbie here, so be gentle 🙂

Any help would be much appreciated. Thank you.

dalecosp

Well, it looks pretty good, except that you've forgotten to make "adate" a variable in the sql query....

planetsim

Im not great with the SQL however this MySQL Manual page may help

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

Something like this

SELECT something FROM tbl_name
WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

warmasice

Thank you both. I will give them a try and get back to you 🙂

warmasice

'planetsim', the code you provided selects all records with a date_col value from within the last 30 days. Whereas I want to delete
all records that are greater than 60 days. 🙁

As 'dalecosp' suggested, I thought this would work..

$numberofDays = 60;

$aDate = date('Y-m-d', strtotime("-".$numberofDays." days"))
$sql="DELETE FROM bookings WHERE dateofbooking < \"$aDate\"";

..but it doesn't seem to be behaving correctly. Ive tried using the '<' and '>' symbols, but the only thing
that happened was I lost ALL the records! Strange.

Anyone help please!

EDIT: Problem sorted. Simple newbie error See the red colored font above

dalecosp

Ooh, yeah. But not a "newbie" thing. Just a split-second brain fart that can happen to almost anyone.

"Single quotes" should work there too, IIRC.