如果不选择整个数据库并与PHP进行比较，如何获得数据库中尚未存在的ID

Question

I have an array of ID's that correlate to a unique column in a MySQL table, is there a way to get the ID's from the array that don't appear in the database? I know I could do it by selecting the entire database and doing comparisons with PHP, but the table could get pretty big, so this doesn't seem like a very good idea to me.

Answer 1

Do it in two stages:

$ids = implode(',', $your_array);

SELECT id FROM thetable WHERE id IN ($ids); 

$found_ids = array();
while($row = fetch($result)) {
    $found_ids[] = $row['id'];
}

$missing = array_diff($your_array, $found_ids);

Basically: use your array of ids to select any matching records from the DB. Any ids in the array which don't have matching records will obviously not be returned. Take that result set, stuff it into another array. Then do a diff between the two arrays. The missing values will pop out as they'll only be in the original array, not the "found" one.