Delete multiple lines in MySQL from a string

advertisements

I know this has been asked a few times, but I've looked through quite a few SO posts and could not find anything to help me out.

I have a javascript function that will add values to an element to appear as such:

value="1,3,5,16"

However the values could be completely different depending on what the user chooses. This value is then sent to a php file to perform a MySQL DELETE function from the database.

Each value is actually an ID number of a record in the database that will need to be deleted.

I cannot get the MySQL statement to recognize multiple ID numbers. It only recognizes one (the first).

HTML:

<form method='post' action='delete_ids.php'>
   <input type='hidden' value="1,2,4,8,12,16" id='myvalues' name='myvalues'/>
   <button type='submit' id='submitBtn'>Submit</button>
</form>

PHP:

<?php

   require("config.php");  // THIS IS MY DATABASE CONNECTION
   $x = $_POST['myvalues'];

   $result = "DELETE FROM `image_upload2` WHERE id IN ('$x')";
   $statement = $db->prepare($result);
   $count = $statement->execute();

?>

It works just fine if there is just ONE value, but when there are multiple values (commas in between) it will only delete the first record and not the rest. In the example above, only 'id' = 1 will be deleted.


The single quotes around $x turn that into a string literal (in the context of the SQL statement sent to the database). For example, consider:

DELETE FROM foo WHERE bar IN ('fee,fi,fo,fum');

The database doesn't care about all those commas within the string literal; the database sees that as a single string, one element inside the parens. Those comma characters are just part of the string.

I think what you want is for the comma to be interpreted as part of the SQL syntax. You likely want a query of the form:

DELETE FROM `image_upload2` WHERE id IN (1,2,4,8,12,16);

--or--

DELETE FROM `image_upload2` WHERE id IN ('1','2','4','8','12','16');

In those examples, the comma characters is seen as part of the SQL syntax, separating the literals.


(And I make just a brief mention here that your current code is vulnerable to SQL Injection, but that's an answer to different question.)

XKCD Exploits of a Mom