Silverterra 3d blog

Goal: pass value list to IN statement

The PDO variable binding is easy until you want to use an IN statement like

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

with PDO

$sql = <<<EOT
  SELECT *
    FROM table
   WHERE id IN (?)
EOT;

after the binding this statement is not working because in the background the select statement will be like this:

   SELECT *
     FROM table
    WHERE id IN ('1,2,3,4')

Luckily the mysql knows the following tricky form

   SELECT *
     FROM table
    WHERE find_in_set(cast(ID as char), '1,2,3,4')

and the bindable form is the following

$sql = <<<EOT
  SELECT *
    FROM table
   WHERE find_in_set(cast(ID as char), ? )
EOT;

Job done. Enjoy the result…

Saturday, December 16th, 2017 at 04:14.
Filed under mysql, tricky.