I have an issue with PDO that I'd really like to get an answer for after being plagued by it for quite some time.
Take this example:
I am binding an array of ID's to a PDO statement for use in a MySQL IN statement.
The array would be say: $values = array(1,2,3,4,5,6,7,8);
The database-safe variable would be $products = implode(',' $values);
So, $products would then be a STRING with a value of: '1,2,3,4,5,6,7,8'
The statement would look like:
SELECT users.id
FROM users
JOIN products
ON products.user_id = users.id
WHERE products IN (:products)
Of course, $products would be bound to the statement as :products.
However, when the statement is compiled and values bound, it would actually look like this:
SELECT users.id
FROM users
JOIN products
ON products.user_id = users.id
WHERE products IN ('1,2,3,4,5,6,7,8')
The problem is it is executing everything inside of the IN statement as a single string, given that I've prepared it as comma-separated values to bind to the statement.
What I actually need is:
SELECT users.id
FROM users
JOIN products
ON products.user_id = users.id
WHERE products IN (1,2,3,4,5,6,7,8)
The only way I can actually do this is by placing the values within the string itself without binding them, however I know for certain there has to be an easier way to do this.