PHP问题:MYSQL IN()的PDO Prepare()和Execute()对数组不起作用 [英] PHP Question: PDO Prepare() and Execute() with MYSQL IN() not working for arrays

问题描述

我在PHP中使用PDO对象运行MYSQL查询，并且似乎在将IN()子句与PDO :: Prepare()一起使用时遇到问题.

用户输入:用逗号分隔的标签
例)篮球，足球

我编写以下代码:

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

据我了解，execute()会将每个变量都用双引号()引起来，因此我在每个输入之间手动添加双引号，以模仿sql，例如:

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

但是，这对我不起作用.在使用sql IN()子句的同时，还有什么方法可以仍然使用PDO的prepare()和execute()吗?

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?

推荐答案

您需要执行类似AND t.tag IN (:tag1, :tag2)

现在它认为您是从文字上寻找"basketball","football"

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

为此，您可以使用PHP的字符串附加和循环来创建查询生成器:)

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

这篇关于PHP问题:MYSQL IN()的PDO Prepare()和Execute()对数组不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！