MYSQL Delete Where Field is Not Part of Array

Question

I have a table "groupdentlink" where I want to delete all the rows that weren't checked in a form.

In essence I want to perform a query like:

DELETE * FROM groupdentlink
WHERE group_id = 'a'
AND dentist_id IS NOT IN ARRAY 'b'

I think I could set a variable with a foreach loop and then keep adding the array values to it so I end up with:

DELETE * FROM groupdentlink
WHERE group_id = 'a'
AND dentist_id != 'D1'
AND dentist_id != 'D5'
AND dentist_id != 'D8'

...and so on.

But is this really the right/best way to do this?

Thanks in advance!

Answer 1

DELETE FROM groupdentlink
WHERE group_id = 'a'
AND dentist_id NOT IN ('D1','D5','D8')

More info here http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_not-in

Answer 2

If you want to execute this query from a Zend Framework driven application please take in consideration the followings :

$where = sprintf('dentist_id NOT IN ("%s")', implode('", "',array_map('mysql_escape_string', $array))); 
$this->sqlMapper->delete($where);

If you try . operator for concatenation purposes the query will result in a fatal error because of the quotes. So from my experience using htmlspecialchars or htmlencode along with . operator will only consume your time and patience. The use of sprintf is elegant, helps you keep your code clean.

And I think these observations apply to any application that makes use of php objects.