MySQL question about delete code

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
paultfh
Forum Commoner
Posts: 31
Joined: Thu Jul 20, 2006 6:24 pm

MySQL question about delete code

Post 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++;
		}
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
paultfh
Forum Commoner
Posts: 31
Joined: Thu Jul 20, 2006 6:24 pm

Post by paultfh »

One thing I was wondering was, does it match the date field to the calculation made, or does it match any field?
User avatar
daedalus__
DevNet Resident
Posts: 1925
Joined: Thu Feb 09, 2006 4:52 pm

Post 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.
Post Reply