[SOLVED] Dates

[wernervantonder]

I would like to delete all the records in mysql database that is older than 7 days.

 

How do i go about selecting it in the database ( currentdate - 7).

 

my database is ctrack1

table is operator

fields is: ID,Username,Password,Date_created,Level

 

Please assist 

Greatley appreciated!

 

[jitesh]

DELETE FROM `operator` WHERE ((TO_DAYS(CURDATE()) - TO_DAYS(Date_created)) > 7)

[igor berger]

You may want to set up a UNIX date stamp field for each record for when the record was created another one when the record was updated etc....

Then you have more flexability to delete the records you want!

[wernervantonder]

Thank you for the quick response! you guys are magnificent.

 

How do i insert a datestamp via php? i am more familiar with Delphi and there i use date(now) function.

 

Please can you supply or point me in the right direction with php code.

 

Thank you verry much

Werner

[igor berger]

you can use date() with a special command for the unix stamp.

Look up the syntax at www.php.net

 

[jitesh]

http://in2.php.net/date

[igor berger]

Service on demand.

[wernervantonder]

 

Good day to all that help me

 

i would like to thank you for your assistance so far. how ever the advice did not exactley help me yet. this is what i tried and it failed:

 

mysql_select_db($database_operator, $operator);

$query_delete = "DELETE FROM operator WHERE ((TO_DAYS(CURDATE() - TO_DAYS(Date_created)) > 7) AND Level='0'";

$delete = mysql_query($query_adminlogin) or die(mysql_error());

?>

 

the above code generates an error when i execute it stating that im using wrong version of MySQL. i am using for test purposes (as i know it works on my host) EasyPHP 1.8.

 

 

Then i tried the following :

 

$curdate = date("Y.m.d");

mysql_select_db($database_operator, $operator);

$query_cleaunup = "DELETE FROM operator WHERE '$curdate' - 'Date_created' < 7 AND Level='0'";

$cleaunup = mysql_query($query_cleaunup) or die(mysql_error());

 

 

When i execute this code it does nothing. but note that when i change the < sigh to > then it deletes all the Level 0 entries!

 

I just basically want to delete all the entries that is older than 7 days and is lovel 0 in my database. How do i do it? please i am desperate for an answer. I appreciate your response!

 

[jitesh]

$query_delete = "DELETE FROM operator WHERE ((TO_DAYS(CURDATE()) - TO_DAYS(Date_created)) > 7) AND Level='0'";

 

 

Check paranthesis.

 

---------------

OR

 

$query_delete = "DELETE FROM operator WHERE  (DATEDIFF(CURDATE(),Date_created) > 7) AND Level='0'";

 

[igor berger]

If you going to use this, then do like this!

 

 

or use time() to get the unix stamp for the date you create the record

use the time() when you modified the record or time() for now - you want to delete the record

or add a number of days to the date created

 

 

so you will have Date_created field in the database that will store time - it is a Unix time stamp

 

select Date_created from mysql

 

do $date_created = Date_created

 

use this to calculate how many days you need to delete

$h = 24 *number of days;

$hm = $h * 60;

$ms = $hm * 60;

 

So you will be deliting from $date_created to $date_created + $ms

 

 

$curdate =  mysql Date_created

mysql_select_db($database_operator, $operator);

$query_cleaunup = "DELETE FROM operator WHERE 'Date_created' >= $date_created AND 'Date_created' <= '$date_created + $ms'";

$cleaunup = mysql_query($query_cleaunup) or die(mysql_error());

 

 

You can look up the syntax for MySql and PHP if you are interested in using this technique.

 

Also learn how date() and strtotime() works.

 

Explore the full power of PHP!

[wernervantonder]

Thank you.

 

I dont use timestamp though to save the date. insted i use this :

 

$curdate = date('Y.m.d');

 

so when i save into the database it saves the date in format = date('Y.m.d');

 

i tried to say :

 

delete from operator where ((date('Y.m.d') - 7) < 7 ) and level ='0'

 

but its not working.

 

Where does my whole problem lie? am i saving it wrong to to the database? date('Y.m.d') is a timestamp of today though.

 

Thank you 

 

[igor berger]

compare time stamp to time stamp not 7 and 0

[wernervantonder]

 

Sorry that i ask so many dumb questions.

 

does this look better?

 

delete from operator where ((date('Y.m.d') - 'Date_Created')< 7 ) ;

 

cause this is what i used before and it only deleted if the sign was > instead of < altough there was dates in the records wich was smaller.(look below)

 

"DELETE FROM operator WHERE date('Y.m.d')- 'Date_created' < 7 AND Level='0'";

 

how do i get it to also only delete records with level 0?

 

[jitesh]

$query_delete = "DELETE FROM operator WHERE ((TO_DAYS(CURDATE()) - TO_DAYS(Date_created)) > 7) AND Level='0'";

 

 

Check paranthesis.

 

---------------

OR

 

$query_delete = "DELETE FROM operator WHERE  (DATEDIFF(CURDATE(),Date_created) > 7) AND Level='0'";

 

Have you try this ?

[wernervantonder]

no not yet. i have to test it but can only test in few hours time. so by tomorrow i shall let you know if i have success or not. Thank you for your assistance.

[wernervantonder]

 

Thank you very much !

 

SOLVED!