J.B.J. J.B.J. - 6 months ago 20
MySQL Question

PHP Prepared Statement MySQL in-function with integers not string

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

Answer

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