MySQL delete query is not right 1

[JimC123]

I cannot get the section of my script to work right.

I have two tables (_added & _old)

I am trying to delete the records in the _added table that are NOT in the _old table but there is a problem with it.

I have been looking sooooo long my eyes are crossing.
Any help would be great, thanks in advance.

/////Added Section/////
// Get the list of records from _added

$query = "SELECT * FROM ".$prefix."_added";
$result = mysql_query($query) or die("Could not get the records from _added for comparison: ".mysql_error());

while ($row = mysql_fetch_assoc($result))
{
if ($row)
{
$id = intval($row['id']);

// Check if the record with the particular ID is in _old now as well

$cquery = "SELECT * FROM ".$prefix."_old WHERE id=".$id.";";
$cresult = mysql_query($cquery) or die("Could not search _old table: ".mysql_error());

// If the record is present in _old then record is NOT new and mysql_num_rows != 0, so delete from _added
// If the record is not present in _old then record IS new and mysql_num_rows is 0, so do nothing and leave the record in _added

if (mysql_num_rows($cresult) != 0)
{
$dquery = "DELETE FROM ".$prefix."_added WHERE id=".$id.";";
$dresult = mysql_query($dquery) or die("Could not delete the duplicate records from _added: ".mysql_error());
}
}
}

 

[TonyGroves]

You could do the whole thing in one query (if you're using MySQL 4.1 or later):
[tt]
DELETE FROM _added
WHERE
id
IN
(
SELECT a.id
FROM _added a LEFT JOIN _old o USING(id)
WHERE o.id IS NULL
)
[/tt]

 

[TonyGroves]

Or even simpler:
[tt]
DELETE FROM _added
WHERE id NOT IN (SELECT id FROM _old) s
[/tt]