MYSQL Delete Where Field is Not Part of Array

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.

New user to Stack Exchange, please forgive and instruct if I'm committing a faux pas.

The preceeding answer is incredibly dangerous, because it opens you up to SQL injection attacks.

Always use bind params.

Always use bind params.

Always use bind params.

Hint: if your query does not resemble "DELETE * FROM groupdentlink WHERE group_id = 'a' AND dentist_id IS NOT IN (?, ?, ?);" you are doing it wrong.

An elegant, fully parametrized 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);

The implode function strings ? together with , without adding a comma in the end (source). You could turn that into a function to make it more readable, otherwise the sprintf keeps it nice and tidy without ugly string concatenation.

I found the statement $str = rtrim($str, ",");
didnt remove the trailing comma giving rise to an error
I came up with this work around:

// string without quotes

$str = array_shift($array);

// string with quotes

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

foreach ($array as $item)
{

    $str .= ", '" . $item . "'";

}

Here How I do it :

assuming you have an array called $csvstocknumbers = ['0','1']; $stocknumbers = implode(",",$csvstocknumbers) ; // make the content of the array in one string seprated by , $deleteoldvehicles = "DELETE FROM table_name WHERE column_name NOT IN
($stocknumbers ) ;"; mysqli_query($con, $deleteoldvehicles);

You just need to join the ids with a comma:

$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 a query like this:

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

If you need the quotes around the ids, then change the loop like this:

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