Page 1 of 3
php/sql conditional delete question
Posted: Mon Nov 27, 2006 12:06 pm
by Obadiah
hey guys, i need to delete a reocord that is more 30 days old in my database...how can i go about doing this...i know i would need a create date as a field but what do i write for the query...and do i write this on the output page?
i was thinking of using this...however this is untested
Code: Select all
DELETE FROM merchant by create_date desc WHERE create_date >30
so to recap the situation i have a page in which i output the data from my database and im wanting to know if i would write my conditional delete on it and also what is the proper way of writing this type of delete query?
Posted: Mon Nov 27, 2006 12:15 pm
by Burrito
check out the
DATE_ADD() function for MySQL.
Posted: Mon Nov 27, 2006 2:06 pm
by feyd
Why was this posted in PHP - Code?

Posted: Mon Nov 27, 2006 4:08 pm
by Obadiah

sorry bout that...habit i guess...its where i post most my questions
Posted: Mon Nov 27, 2006 7:18 pm
by RobertGonzalez
What version of MySQL are you running? The date functions didn't get really useful until 4.1. They got even better in 5.
Posted: Mon Nov 27, 2006 8:51 pm
by aaronhall
Also consider using a
unix timestamp as a column in your table, and compare that against the current time.
Posted: Tue Nov 28, 2006 5:50 am
by dibyendrah
what is the format of the date you are storing on your field ?
and also the version of MySQL too.
Posted: Tue Nov 28, 2006 5:56 am
by dibyendrah
If date format is YY-MM-DD, solution should be like this
Code: Select all
DELETE FROM merchant
WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <=create_date
Please try this.
Cheers,
Dibyendra
Posted: Tue Nov 28, 2006 8:58 am
by Obadiah
thanks to all you guys for replying, and sorry for the late reply(again much appologies for posting in the wrong forum) , i dont have internet at home so my post are limited to my 9-6:30 work hours for the time being but:
@burrito= i had trouble understanding this at first
Code: Select all
SELECT something FROM tbl_name WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;
i was under the impression that Date_Sub was the name of the date_column in the database

...once again hamster is running on the wheal but the gears are stuck
@everah="version 5.0.21" this will be my first time using the date/time function
@aaronhall= thanks for the link im definately gonna experiment into date-time a little deeper
@dibyendrah= aah...now i get what it was saying...now for some more testing
Posted: Tue Nov 28, 2006 9:50 am
by dibyendrah
Obadiah wrote:thanks to all you guys for replying, and sorry for the late reply(again much appologies for posting in the wrong forum) , i dont have internet at home so my post are limited to my 9-6:30 work hours for the time being but:
@burrito= i had trouble understanding this at first
Code: Select all
SELECT something FROM tbl_name WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;
i was under the impression that Date_Sub was the name of the date_column in the database

...once again hamster is running on the wheal but the gears are stuck
@everah="version 5.0.21" this will be my first time using the date/time function
@aaronhall= thanks for the link im definately gonna experiment into date-time a little deeper
@dibyendrah= aah...now i get what it was saying...now for some more testing
Oh sorry Obadiah !
My mistake ...
It should be something like this ...
Code: Select all
SELECT something FROM tbl_name WHERE create_date<=DATE_SUB(CURDATE(),INTERVAL 30 DAY);
Try this.
Hope this will help you.
With Best Regards,
Dibyendra
Posted: Tue Nov 28, 2006 10:05 am
by Obadiah
ok...now if i was wanting to put the current date into the database under create_date using the now for curdate() wouldnt i go about it in the similar fashion...i tried this
Code: Select all
$sql="INSERT INTO $table_name WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <=create_date ";
it doesnt like it too much and i cant figure out why
[edited]
dibyendrah wrote:
Oh sorry Obadiah !
My mistake ...
It should be something like this ...
Code: Select all
SELECT something FROM tbl_name WHERE create_date<=DATE_SUB(CURDATE(),INTERVAL 30 DAY);
Try this.
huh
shouldent it be DELETE FROM if im attempting to delete a record from the database?!? if not then if im attempting to insert into the databse then the syntax for the other thing im attempting to do is way off also then right? im seriously confused.

Posted: Tue Nov 28, 2006 10:37 am
by RobertGonzalez
Dude, I just checked the MySQL manual for Date and Time functions (for version 5.0). Take a look at the first few lines Obadiah...
http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
PS You might also be interested in DATEDIFF as well
Posted: Tue Nov 28, 2006 11:34 am
by Obadiah
Code: Select all
SELECT something FROM tbl_name WHERE create_date<=DATE_SUB(CURDATE(),INTERVAL 30 DAY);
ok...it says that it selects all rows where date_col is within 30 days of the
current date...so should i not worry about deleting the records? either way that would work fine
what about inserting the current date into the database under the date_col in the database how is this done...thats where i got confused...the book im looking in shows me how to select records from a database using date/time functions but nothing on actually inserting or deleting records using the date time functions...im not worried about deleting the records now since the select will work fine but, how do i insert the current date in the date_col?[/syntax]
Posted: Tue Nov 28, 2006 12:42 pm
by RobertGonzalez
The date functions will work the same regardless of the query type. As for entering a date, you should make sure your field type is either date or datetime. Then specify your default format for the data: dates I usually set to YYYY-MM-DD (Expressed as 0000-00-00), datetimes I usually set to YYYY-MM-DD HH:MM:SS (Expressed as 0000-00-00 00:00:00). Then to insert a date, throw the date in there. you could use CURRENT_DATE(), CURRENT_TIMESTAMP(), CURDATE() or NOW() to enter the date at the second of the insert.
Posted: Tue Nov 28, 2006 2:07 pm
by Obadiah
ok...i tried this in a attempt to insert the current date into the create_date column
Code: Select all
$today = date("Y-m-d");
$create_date = $today(CURDATE());//line10
$sql = "INSERT INTO $table_name values('$_POST[merchant_num]',
'$_POST[date_recieved]',
'$_POST[merchant_name]',
'$_POST[purchase_type]',
'$_POST[lease_score]',
'$_POST[amex]',
'$_POST[app_id]',
'$_POST[discover]',
'$_POST[user_name]',
'$_POST[check_conversion]',
'$_POST[gift_loyalty]',
'$_POST[app_type]',
'$_POST[terminal]',
'$_POST[serial_num]',
'$_POST[nms]',
'$_POST[ckmerchant_num]',
'$_POST[giftmerchant_num]',
'$_POST[comments]',
'$_POST[create_date])'";
and i got this error
Fatal error: Call to undefined function 2006-11-28() in C:\Program Files\status_modified.php on line 10
ok...that tells me i got something half-working but what do i do now?!? what does this error mean