mysql pdo in_PHP问题：MYSQL IN（）的PDO Prepare（）和Execute（）无法用于数组

I am using a PDO object in PHP to run MYSQL queries, and I seem to be having a problem with using the IN() clause with PDO::Prepare().

User Input: tags separated by a comma

ex) basketball,football

I code the following:

$query =

"SELECT s.item_id, s.item_type, s.title

FROM search_all s

WHERE EXISTS (

SELECT t.item_id

FROM tags t

WHERE t.item_id = s.item_id AND t.item_type = s.item_type

AND t.tag IN (:tags)

)";

$mysql_vars[':tags'] = implode("','",explode(',',$tags));

$stmt = $connection->prepare($query);

$stmt->execute($vars);

$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

From what I understand, execute() will wrap each variable in double quotes ("), so I manually add double quotes between each input, to mimic the sql like:

SELECT s.item_id, s.item_type, s.title

FROM search_all s

WHERE EXISTS (

SELECT t.item_id

FROM tags t

WHERE t.item_id = s.item_id AND t.item_type = s.item_type

AND t.tag IN ("basketball","football")

)

This is not working for me, however. Is there any way to still use PDO's prepare() and execute() while using the sql IN() clause?

解决方案

You would need to do something like AND t.tag IN (:tag1, :tag2)

Right now it thinks that you are textually looking for "basketball","football"

To do this you can do a query generator using PHP's string appends and loops :)