Jump to content

Recommended Posts

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?

Link to post
Share on other sites

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.

Link to post
Share on other sites

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?

Link to post
Share on other sites

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 :P

Link to post
Share on other sites

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

Link to post
Share on other sites
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. Edited by Krewe
Link to post
Share on other sites

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

Link to post
Share on other sites

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";

Link to post
Share on other sites

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)

Edited by Krewe
Link to post
Share on other sites

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?

Edited by Krewe
Link to post
Share on other sites

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 :P

Edited by Krewe
Link to post
Share on other sites

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());

Edited by dsonesuk
Link to post
Share on other sites
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 :P 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. Edited by Krewe
Link to post
Share on other sites

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.

Link to post
Share on other sites
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.
Link to post
Share on other sites

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.

Edited by dsonesuk
Link to post
Share on other sites

$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 ;)

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...