Page 1 of 1

MySQL question about delete code

Posted: Thu Jul 27, 2006 7:26 pm
by paultfh
What this code does is find all entries that are older than $retention.

It works, but I was wondering it this is done correctly.

Code: Select all

$sql = "SELECT ID FROM bin 
            WHERE (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(date)) / 3600 / 24 > {$retention} {$gr}";
		$res = mysql_query($sql);
		echo "Deleting ".mysql_num_rows($res)." from bin\n";
		while($arr = mysql_fetch_assoc($res)) {
			$sql = "DELETE FROM parts WHERE binID = {$arr['ID']}";
			mysql_query($sql);

			$sql = "DELETE FROM bin WHERE ID = {$arr['ID']}";
			mysql_query($sql);
			$count++;
		}

Posted: Thu Jul 27, 2006 7:29 pm
by Benjamin
Looks good to me. Another way to do it would be to calculate the timestamp in php rather than having MySQL do it, but in this case I don't see that their would be anything to gain either way.

Posted: Thu Jul 27, 2006 10:55 pm
by paultfh
One thing I was wondering was, does it match the date field to the calculation made, or does it match any field?

Posted: Fri Jul 28, 2006 1:46 am
by daedalus__
I would think that having MySQL do the conversion is faster than having PHP do it, even if it is only .000000000001 of a second. I could be wrong but that's the reason I do it.