PHP Prepared Statement MySQL in-function with integers not string

Question

I am trying to perform a query, in which i use the MySQL in-function, to do something like:

SELECT * FROM table WHERE something IN(1,2,3)

So i would select everything that had "something" set to 1, 2 or 3 respectively.

The query i have made works perfectly when executed on the DB directly, but my problem is that whenever i pass it to PHP as a prepared statement, it converts my IN-values to strings, not int, so it becomes:

SELECT * FROM table WHERE something IN("1,2,3")

Notice the " " inside IN. This returns nothing, as the column "something" is stored as int values, and not as strings. Besides, i think it searches for 1 big string.

Here is a simple example that resembles my use-case:

$values = "1,2,3"; // These needs to be converted to ints, so it reads 1,2,3
if ($stmt = $connection->prepare('SELECT * FROM table WHERE something IN(?)')) {
   $stmt->bind_param("s", $values);
}

score 1 · Accepted Answer · answered Jun 08 '16 at 10:21

You'll have to use FIND_IN_SET instead of IN, like this:

$values = "1,2,3"; // These needs to be converted to ints, so it reads 1,2,3
if ($stmt = $connection->prepare('SELECT * FROM `table` WHERE FIND_IN_SET(`something`, ?)) {
   $stmt->bind_param("s", $values);
}

PHP Prepared Statement MySQL in-function with integers not string

1 Answers1