[RESOLVED] compare dates then update an entry

[severndigital]

ok ..i'm stumped.

i have two tables.

1. empty_locations

2. games

the empty_locations table houses data for locationid and date.
it also houses an ENUM field called used.

the games table houses data for locationid and date of the game.

what i need to do is

check the empty_locations table against the game table.

and if the dates match, then set the used field of the empty_locations table to '1'.

I can't figure this query out for the life of me. i've never had to search and compare before.

please help out!

thanks a lot.

chris

[barand]

Is this what you want

$sql = "SELECT g.locationid, g.date
		FROM games INNER JOIN empty_locations e
			ON g.locationid = e.locationid
			AND g.date = e.date ";
$res = mysql_query($sql) or die(mysql_error());
while (list($loc, $date) = mysql_fetch_row($res))  {
	mysql_query("UPDATE empty_locations
				SET used = 1
				WHERE locationid = '$loc'
					AND date = '$date'");
}

I haven't installed later version yet but I think with MySQL 4.1+ you can

mysql_query("UPDATE games g, empty_locations e
		SET e.used = 1
		WHERE g.locationid = e.locationid
			AND g.date = e.date");

[severndigital]

that worked wonderfully .. thanks a million.