Using MySQL IN() with PHP and PDO

Question

I'm developing a web service and one of the parameters it takes is a list of device IDs (energy_mon_id). The list is comma separated e.g. 0,1,2 (the other two parameters it takes are dateFrom and dateTo).

My prepared statement looks like this:

$this->sql = "SELECT $vars FROM $table "

                . "WHERE energy_mon_id IN(:energy_mon_id) AND CONCAT(date, ' ', time) "
                . "BETWEEN :dateFrom AND :dateTo "
                . "$group ORDER BY $orderBy $limit";

The problem is that instead of recognising these as individual comma separated characters it's being interpreted as a complete string (I think). I tried to follow this post

I have searched around and found the exact same solution being provided (it seems to work for everyone else but me). I'm quite puzzled, never have I had to directly ask a question myself, so this is my first post!

Thanks for the help in advance.

it's because you're using a prepared statement, you need to do some extra work to convert the string to a usable list. See this link http://stackoverflow.com/questions/1586587/pdo-binding-values-for-mysql-in-statement — David Parlevliet, Aug 01 '15 at 10:14
Did you do this `$energy_mon_id = "'" . implode("', '", $users ) . "'";` from post in your link? It should work. — Styx, Aug 01 '15 at 10:17
I think in vanilla prepared statements you need a placeholder for every passed value, e.g. : `IN(:energy_mon_id1, :energy_mon_id2, ...)`. Take a look here: http://stackoverflow.com/questions/1586587/pdo-binding-values-for-mysql-in-statement — dmitry, Aug 01 '15 at 10:21
Tried using implode but that didn't work... David, that post is exactly what I needed! thanks. — Abdul Maye, Aug 01 '15 at 15:50

score 1 · Accepted Answer · answered Aug 01 '15 at 15:54

Thanks for the comments they pointed me to a suitable post with the answer. However, just to simplify for anyone that may be having a similar issue, this simple fix worked...

instead of using:

WHERE energy_mon_id IN(:energy_mon_id)

I used:

WHERE find_in_set(energy_mon_id, :energy_mon_id)

Using MySQL IN() with PHP and PDO

1 Answers1