php/sql conditional delete question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
Obadiah
Forum Regular
Posts: 580
Joined: Mon Jul 31, 2006 9:13 am
Location: Ashland, KY
Contact:

php/sql conditional delete question

Post 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?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

check out the DATE_ADD() function for MySQL.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Why was this posted in PHP - Code? :roll:
User avatar
Obadiah
Forum Regular
Posts: 580
Joined: Mon Jul 31, 2006 9:13 am
Location: Ashland, KY
Contact:

Post by Obadiah »

:oops: sorry bout that...habit i guess...its where i post most my questions
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

Also consider using a unix timestamp as a column in your table, and compare that against the current time.
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

what is the format of the date you are storing on your field ?

Code: Select all

1. date
2. datetime
3. timestamp
and also the version of MySQL too.
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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
User avatar
Obadiah
Forum Regular
Posts: 580
Joined: Mon Jul 31, 2006 9:13 am
Location: Ashland, KY
Contact:

Post 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 :lol: ...once again hamster is running on the wheal but the gears are stuck :oops:

@everah="version 5.0.21" this will be my first time using the date/time function :P

@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
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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 :lol: ...once again hamster is running on the wheal but the gears are stuck :oops:

@everah="version 5.0.21" this will be my first time using the date/time function :P

@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
User avatar
Obadiah
Forum Regular
Posts: 580
Joined: Mon Jul 31, 2006 9:13 am
Location: Ashland, KY
Contact:

Post 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. :(
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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
User avatar
Obadiah
Forum Regular
Posts: 580
Joined: Mon Jul 31, 2006 9:13 am
Location: Ashland, KY
Contact:

Post 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]
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
Obadiah
Forum Regular
Posts: 580
Joined: Mon Jul 31, 2006 9:13 am
Location: Ashland, KY
Contact:

Post 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
Post Reply