CODEWITHSUNDEEP
phpmysqlarrays
Brian Barrus
Brian Barrus

Reputation: 515

MYSQL Delete Where Field is Not Part of Array

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!

Upvotes: 9

Views: 22646

Answers (7)

Ahmed Soliman
Ahmed Soliman

Reputation: 1710

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);

Upvotes: 0

Peter
Peter

Reputation: 16943

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

Upvotes: 20

tobiv
tobiv

Reputation: 842

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.

Upvotes: 1

AlanJ
AlanJ

Reputation: 11

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 . "'";

}

Upvotes: 0

Vlad Manuel Mureșan
Vlad Manuel Mureșan

Reputation: 780

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.

Upvotes: 2

swatkins
swatkins

Reputation: 13640

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 . "',";
}

Upvotes: -1

Meleneth
Meleneth

Reputation: 306

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.

Upvotes: 1

Related Questions