is this logic correct?

[$php_mysql$]

so i would be deleting entries from my db which are greater than 2 days so i made a function which i call and inside it i added this query, i just want to find out if its a correct logic or will it not work like how i want it to be?

 

$time_count = 2*24*60*60;

$match_time = date('Y-m-d h:i:s', time() + $time_count);

$sql="SELECT `id`, `image`,`time` FROM `tbl` WHERE `time` > '".$match_time."' ORDER BY RAND() LIMIT 1";

[AyKay47]

1. google why you should not ORDER BT RAND()

2. besides that your code looks to be fine.. have you tested this out yet?

[$php_mysql$]

yes i tested and it does delete entries if i enter a date greater than 2 days in mysql, but im having a feeling that suppose

 

1. user post an entry today and one posts tomorrow

 

by any means will the time_count will auto increase everyday and no post will be deleted?

[AyKay47]

you are using the time() function here, which the date function uses by default.. the time function uses the unix timestamp of the corrent time, so the timestamp you have specified will always equal the timestamp for the exact time now, plus 2 days..

[$php_mysql$]

so i just now entered 5 entries dating

 

15th

16th

17th

18th and 19th

 

and i added $time_count = 4*24*60*60;  and run my function and it deleted the 19th row only, so i guess my coding is wrong isn't it?

[AyKay47]

right now your $time_count variable is set for 4 days.. not 2. for 2 days, change to this.. $time_count = 2*24*60*60

[$php_mysql$]

so i set it to 2 then 19th, 18th and 17th got deleted. but the thing is my time_count will + 2 days everyday but post entry date will always remain same isn't it?

 

when i echo $sql i get this

 

SELECT `id`, `image`,`time` FROM `tbl` WHERE `time` > '2011-08-17 12:01:58' 

 

so tomorrow wont the time_count 2011-08-17 become 2011-08-18 and i make a post today which is 2011-08-15 and will always be the same in db i guess so how will that get deleted? i guess somewhere the logic is not good here?

[AyKay47]

yes i see what you are saying.. what you will need to do here is compare the date 2 days in the future to the date in your db for date posted..

 

$sql = "SELECT id,image,`time` FROM tbl WHERE `time` > DATE_ADD(NOW(),INTERVAL 2 DAYS)";

 

on second thought.. now you have me confused as to what the ssue is with the way you are doing it now..

[$php_mysql$]

will that be the solution to my issue? so how am i to implant this function into my query ising the $match_time?

[AyKay47]

really thats no different than what you were doing before, just a mysql way of doing it.. that will replace the query you have now..

[$php_mysql$]

so it will count from tables where rows are greater than 2 days from the time it is posted and it will delete it?

[AyKay47]

yes

[$php_mysql$]

thanks you soo much bro let me give it a try

[$php_mysql$]

so now my query looks like this

 

$time_count = 30; 
		$sql="SELECT `id`, `image`,`time` FROM `tbl` WHERE `time` > 'DATE_ADD(NOW(),INTERVAL ".$time_count." DAY)'";

 

instead of

 

$time_count = 2*24*60*60;
$match_time = date('Y-m-d h:i:s', time() + $time_count);
$sql="SELECT `id`, `image`,`time` FROM `tbl` WHERE `time` > '".$match_time."'";

 

so i removed the $match_time = date('Y-m-d h:i:s', time() + $time_count);

 

am i on the right path?

[AyKay47]

yes except for the fact that you want to interval by 2 days and not 30 right..?

or was that just for testing purposes..?

[$php_mysql$]

oh just testing purpose bro, Ok im having an issue testing it now. i made 5 entries on db now how must i check if it is working for changing date is not showing any result of deleting, sorry if im being a pain :-D not getting the idea how to make the interval on the entries

[AyKay47]

to be honest, I myself would prefer the PHP method that I helped you with originally for this..

[$php_mysql$]

which php method? im confused

[AyKay47]

$time_count = 2*24*60*60;
$match_time = date('Y-m-d h:i:s', time() + $time_count);
$sql="SELECT `id`, `image`,`time` FROM `tbl` WHERE `time` > '".$match_time."'";

[$php_mysql$]

oh yes thats what i want to use but its not doing things how it should nor am i getting the idea how to get it to selects rows in interval from the posted date  to $time_count

 

my time is stored like this

 

`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

 

output is  2011-08-15 18:37:01

[AyKay47]

maybe I am misunderstanding you.. you want to select a row that is in between the post time and 2 days after the post time..?

[$php_mysql$]

ok let me explain again :-) what im trying to achieve is say i make a post today or any day but then it should get deleted after 2 days from the original posted date with a interval of 2 days. so i got this code but im not getting the idea how to select rows with the interval of 2 days and then delete it

 

		$time_count = 2*24*60*60; 
		$match_time = date('Y-m-d h:i:s a', time() + $time_count); 
		$sql="SELECT `id`, `image`,`time` FROM `tbl` WHERE `time` > '".$match_time."'";
		echo $sql;
		$result = mysql_query($sql) or die(mysql_error());
		if(!$result){
		//echo 'SELECT failed: '.mysql_error();
		}else{
		while($row = mysql_fetch_array($result)){
		if (mysql_num_rows($result)) {
		$id = $row['id'];
		if(!unlink($row['image'])){
		//echo "unlink ".$row['image']." failed";
		}else{
		}
		}
		$sql="DELETE FROM tbl WHERE id = '".$id."'";
		$result= mysql_query($sql);
		if(!$result){
		//echo 'DELETE from tbl with id '.$id.' failed: '.mysql_error();
		}
		executeSql($sql);
} 
} 

[AyKay47]

alright lets work on the logic..

 

$time_count = 2*24*60*60; 			
$match_time = date('Y-m-d h:i:s a', time() + $time_count); 			
$sql="SELECT `id`, `image`,`time` FROM `tbl` WHERE `time` > '$match_time'";			
print $sql;			
$result = mysql_query($sql) or die(mysql_error());			
if(mysql_num_rows($result > 0)){					
while($row = mysql_fetch_array($result)){						
$id = $row['id'];						
$sql1="DELETE FROM tbl WHERE id = '$id'";			
$result1= mysql_query($sql1) or die(mysql_error());						
} 

[$php_mysql$]

so it get this

 

SELECT `id`, `image`,`time` FROM `tbl` WHERE `time` > '2011-08-17 02:45:10 pm'

[AyKay47]

looks right to me..

should delete anything older than 2 days in your query..