Time()

[westman]

am trying to get a working piece of code to delete after 4 weeks and 12 weeksso fear i have this... $time1 = time() - (60*60*24*7*4);$sql1 = mysql_query("DELETE FROM post WHERE date > '$time1' AND id ='0'")or die (mysql_error()); $time2 = time() - (60 * 60 * 24 * 7 * 12); $sql2 = mysql_query("DELETE FROM post WHERE date > '$time2' AND id ='1'")or die (mysql_error()); but its not workinghow do i get this right?

[Ingolme]

You're giving it a timestamp, are your dates stored as timestamps?If they're stored as dates then you should give it a date string instead. Anyways, how are you testing this? You know it won't delete the record unless that amount of time really has passed.

[westman]

am using timestamps in my database but i can change it to time if i knew the code needed to delete 30 day old posts.am new to php and any help with my ode is mush appreciated.

[Ingolme]

You don't need quotes around numeric values in a query. If it's stored as a timestamp then your code has no problems. How do you know it's not working?

[westman]

i still have posts in my database that is older than 30 days

[Ingolme]

Maybe you can remove this part from your query: AND id ='0' If there's only one post with ID 0, then only one post would have been deleted.

[justsomeguy]

You're using greater than, you should be using less than. You also don't need to check for the 12 week, because the 4 week will delete everything older than 4 weeks (including everything older than 12 weeks).

[Krewe]

Ah, I understand what JSG is saying. Took some time, but I understand now. If your date = 13143124(Some random Unix Timestamp)and your time() - (60*60*24*7*4) = 4 weeks BEFORE the current date. SO time() - (60*60*24*7*4) < date.Once your 4 weeks is up it will be equal to the unix timestamp the exact time "date" was made. So you need it to be deleted when $time1 > date I just wanted to share why JSG was saying $time1 > date because I was confused

[westman]

i tried $time2 = time() - (60*60*24*7*4); $sql1 = mysql_query("DELETE FROM post WHERE date <'$time2' AND item ='0'")or die (mysql_error());and$sql1 = mysql_query("DELETE FROM post WHERE date >'$time2' AND item ='0'")or die (mysql_error()); but it still dose not work date is set to timestamp

[Krewe]

i tried $time2 = time() - (60*60*24*7*4); $sql1 = mysql_query("DELETE FROM post WHERE date <'$time2' AND item ='0'")or die (mysql_error());and$sql1 = mysql_query("DELETE FROM post WHERE date >'$time2' AND item ='0'")or die (mysql_error()); but it still dose not work date is set to timestamp

Two things.Since $time2 is going to be an Integer you do not want it in single quotes ' '. Putting ' ' around $time2 tells MySQL it is a string, not an integer. (Ingolme already stated this)2nd, try putting { } around $time2 in the MySQL query:Ex:{$time2} The curly brackets make sure the query knows it is a variable. One more thing to try is take out "AND item ='0'"Since you want to delete entries where the date < $time2 the item shouldn't matter. Try those two things.

[westman]

thank you for your reply,however it is still not working i tied with and without {} curly bracketsit loos like this now... $time2 = time() - (60*60*24*7*4);$sql1 = mysql_query("DELETE FROM post WHERE date <$time2")or die (mysql_error()); date is set to timestamp and its still not working i have been working on this just under 7 days now

[Ingolme]

Well, given the information I have, there's no reason why it shouldn't work.Check to see if the query looks like you expect it to be, like this:

echo "DELETE FROM post WHERE date <$time2";

[Krewe]

Hmm not sure if it will really do anything but you can try this. Take out the part after the end of the query, or die (mysql_error());Remove all of that so the $sql1 = mysql_query("DELETE FROM post WHERE date < $time2") Then just make a function called something like: confirm_query and make the function do this:

function confirm_query($result_set) {  if (!$result_set) {   die("Database query failed: " . mysql_error());  }}

So all the function does is make sure $sq1 is really set.'So take $sq1 and put it in the function.Then the end code should look something like this.

$time2 = time() - (60*60*24*7*4);$sql1 = mysql_query("DELETE FROM post WHERE date < $time2");confirm_query($sql1); 

Only reason I am suggestion that is make the die function is taking over for some reason. (Also make sure you add a space after the <, that could be causing a problem as well. Because I know white space IS important in MySQL)

[westman]

am sorry its still not working

[Krewe]

Hmmm well the code should be working.What is setting off the code? Is it running on every page? Do you click a button?Because the only problem I see now is how is the Query going to know how/when to run?

[Krewe]

Just an idea...

$time2 = time() - (60*60*24*7*4);$query = "SELECT * FROM table WHERE date < $time2";$expired_messages = mysql_query($query);$expired = mysql_fetch_array($expired_messages);$number_of_expired = mysql_num_rows($expired);if(!empty($expired)){$query2 = "DELETE FROM table WHERE date < $time2";$delete_expired = mysql_query($query2);if(mysql_affected_rows() == $number_of_expired){//Success Message!}else{//Failed Message}}

Alright... i think that is it. Try it

[dsonesuk]

I think the problem is you have to convert it to a time format of yyyy-mm-dd then use TIMESTAMP to do a calculation against the value stored in database

$time1 = date("Y-m-d",time() - (60*60*24*7*4));

$sql1 = mysql_query("DELETE FROM post WHERE date < TIMESTAMP('$time1')")or die (mysql_error());

[Krewe]

I think the problem is you have to convert it to a time format of yyyy-mm-dd then use TIMESTAMP to do a calculation against the value stored in database

$time1 = date("Y-m-d",time() - (60*60*24*7*4));

$sql1 = mysql_query("DELETE FROM post WHERE date < TIMESTAMP('$time1')")or die (mysql_error());

... I totally forgot that.MySQL wants time in the format of Y,M,D H,M,S ha do what Dsone said. Smart.... here is the strftime() version if you are used to that. $time1 = strftime("%Y - %m - %d %H:%M:%S", time() - (60*60*24*7*4) ) Thanks Dsone for catching that.

[Ingolme]

If the field is an integer field, which is what I meant when I asked if it was a timestamp in the beginning, then you won't have to do that. If it's a MySQL date format, then it needs to be converted to the proper date format in the query.

[birbal]

sql1 = mysql_query("DELETE FROM post WHERE date <'$time2' AND item ='0'")or die (mysql_error());

if the column data type is integer you should not quote it around. try removing the quote arounf $time2.

[dsonesuk]

I don't know if we are getting confused with timestamp meaning here the timestamp field type uses format yyyy-mm-dd hh:mm:ss, the time field type only uses hh:mm:ss, if we are talking about an INT field type of a 10 digit timestamp value being used such as 1320021014 then the code used before mine should have worked. Did you save it as an integer in the correct format take for instance 2011/02/10 in uk that would be yyyy/mm/dd in usa that would be yyyy/dd/mm, if you save it in that latter format it would be an integer value for yyyy/mm/dd, just something to consider.

[westman]

$time2 = date("Y-m-d",time() - (60*60*24*7*4));$sql1 = mysql_query("DELETE FROM post WHERE item='0' AND date < TIMESTAMP('$time2')")or die (mysql_error()); this is working and my date in my database is set to date not timestamp. thank you very much to every one who helped me with my scripted am so happy i could party ;)i have been working on the other a week now ones again id like to thank this site for having a forum with a live community and i thank the people of the community becausewith out the people there is no community. thank you