PHP Question: PDO Prepare() and Execute() with MYSQL IN() not working for arrays

Question

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?

maaudet · Accepted Answer · 2011-06-22 18:04:56Z

1

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 :)

answered Jun 22, 2011 at 18:04

maaudet

2,3584 gold badges20 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ripptor Over a year ago

Thanks. I was hoping to not have to do that, but it does work that way :)

Collectives™ on Stack Overflow

PHP Question: PDO Prepare() and Execute() with MYSQL IN() not working for arrays

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related