0

I have a situation where I'd like to add a list of names in an array and use it in an SQL query.

How I do it:

$names = implode(',', $names);
$sql = "DELETE FROM product WHERE name NOT IN ($names)";

This works ok if I use id (but I can't do that here). Problem is - name can have a comma in it (i.e. - benchpress rack, blue) and that breaks this query. Is there a way to bypass this issue?

Xeen
  • 6,955
  • 16
  • 60
  • 111
  • It probably is breaking because your names aren't quoted. Whats the error message? If `benchpress rack, blue` really is directly in your query that is invalid, strings need to be quoted. – chris85 Oct 10 '16 at 23:32
  • If your using the PDO extension you can send an array as the parameter for $names. If not, implode the with single apostophys.. make your string look like this: 'benchpress rack, blue','benchpress rack,red' etc – Layton Everson Oct 10 '16 at 23:34
  • @LaytonEverson Placeholders will need to be appended to the query though for the array to bind. – chris85 Oct 10 '16 at 23:35
  • May-be solution, `= implode("','", $names);` and `...NOT IN ('$names')";` – Rajdeep Paul Oct 10 '16 at 23:36

1 Answers1

1

Try this:

$names = implode("','", $names);
$sql = "DELETE FROM product WHERE name NOT IN ('$names')";

If this is your actual code I would suggest switching to PDO, use a library, or escape your values with mysqli_real_escape_string.

Here is a link: http://php.net/manual/en/mysqli.real-escape-string.php

Here is a w3schools link for prepared statements, a decent high level view of how they work: http://www.w3schools.com/php/php_mysql_prepared_statements.asp

Here is how your statement query will run if your using PDO:

$query= $conn->prepare("DELETE FROM product WHERE name NOT IN (:names)");
$query->bindParam(':names', $names);
$query->execute();

And for mysqli it will be similar, refer to the w3schools link above for the differences.

I highly recommend you move away from the old mysql driver and at the very least switch to mysqli.

Layton Everson
  • 1,148
  • 9
  • 18