MYSQL Delete where the field is not part of the array

Question

I have a groupdentlink table where I want to delete all rows that were not marked on the form.

In essence, I want to execute a query such as:

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

I think that I could set a variable with a foreach loop, and then continue to add array values ​​to it, so I get:

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

... etc.

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 details here http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_not-in

PHP syntax:

 $array = Array('D1','D5','D8'); $sql = " DELETE FROM groupdentlink WHERE group_id='a' AND dentist_id NOT IN ('".join("','", $array)."')"; mysql_query($sql); 

Answer 2

If you want to execute this request from an application managed by Zend Framework, please consider the following:

 $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, a query will result in a fatal error due to quotation marks. So from my experience using htmlspecialchars or htmlencode along with. The operator will consume your time and patience. Using sprintf is elegant, helping you keep your code clean.

And I think that these observations apply to any application that uses php objects.

Answer 3

You just need to join the semicolon identifiers:

 $myarray = new array('D1', 'D5', 'D8'); $str = ""; foreach ($myarray as $item) { $str .= $item . ","; } $str = rtrim($str, ","); $query = "DELETE * FROM groupdentlink WHERE group_id = 'a' AND dentist_id NOT IN ($str)"; 

This will give you this request:

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

If you need quotes around identifiers, modify the loop as follows:

 foreach ($myarray as $item) { $str .= "'".$item . "',"; } 

Answer 4

New Stack Exchange user, please forgive and instruct if I am performing faux pas.

The previous answer is incredibly dangerous, as it opens you with SQL injection attacks.

Always use binding options.

Always use binding options.

Always use binding options.

Hint: if your request is not like "DELETE * FROM groupdentlink WHERE group_id = 'a' AND dentist_id DOES NOT INCLUDE (?,?,?);" you are doing it wrong.

Answer 5

I found the instruction $str = rtrim($str, ",");
did not remove the trailing comma giving rise to error
I came up with this work:

// string without quotes

 $str = array_shift($array); 

// quoted string

 $str = "'" . array_shift($array) . "'"; 

 foreach ($array as $item) { $str .= ", '" . $item . "'"; } 

Answer 6

Elegant, fully parameterized solution (using PDO):

 $dentistIds = ['D1', 'D5', 'D8']; $query = sprintf( "DELETE FROM online_order_shipping WHERE group_id = 'a' AND dentist_id NOT IN (%s)", implode(',', array_fill(0, count($dentistIds), '?')) ); $stmtDelete = $pdo->prepare($query); $stmtDelete->execute($dentistIds); 

Strings implode ? along with , without adding a comma at the end ( source ). You can turn this into a function to make it more readable; otherwise, sprintf keeps it beautiful and neat without ugly string concatenation.