为什么pdo准备的select语句中的多个where条件会导致查询中断

hsvhsicv  于 2021-06-21  发布在  Mysql
关注(0)|答案(2)|浏览(345)

当执行这个查询时,我得到了预期的响应(通过ajax)。它起作用了。

$stmt = $pdo->prepare("SELECT item_location AS Location,  users_description AS Description, price AS Price  FROM sale_items WHERE item_type IN ($cs_vals)”);

但当我向where子句添加另一个表列名时,如下所示:

$stmt = $pdo->prepare("SELECT item_location AS Location,  users_description AS Description, price AS Price  FROM sale_items WHERE (item_type, map_region) IN ($cs_vals)”);

..我在控制台中收到一条“json.parse:unexpected character..”消息,但我的网页中没有显示任何内容。为什么会这样?我做错什么了。提前谢谢你的帮助。顺便说一句,$cs\u vals是逗号分隔的值列表,格式为('a'、'b'、'c'…)。这个逗号分隔的列表是由

$cs_vals = str_repeat('?,', count($arr) - 1) '?';

$arr变量是从用户生成的表单中提取的非空值数组。

7dl7o3gd

7dl7o3gd1#

我想你应该知道
您必须一次检查一个:

WHERE ((item_type) IN ($cs_vals) 
OR (map_region) IN ($cs_vals))

除非你想在数组中找到这个组合。

qltillow

qltillow2#

你用 EXISTS 而不是 $cs_vals 您可以使用 UNION 这样就可以验证元组是否与列表匹配。
sql演示

SELECT item_location AS Location,  users_description AS Description, price AS Price  
FROM sale_items 
WHERE EXISTS (SELECT 1
              FROM (SELECT 'A' as item_type, 1 as map_region
                    UNION 
                    SELECT 'B' as item_type, 2 as map_region
                    ) as T
              WHERE sale_items.item_type = T.item_type
                AND sale_items.map_region = T.map_region
              )

您也可以在中使用,但需要与元组进行比较,如下所示:

SELECT item_location AS Location,  users_description AS Description, price AS Price  
FROM sale_items 
WHERE  (item_type, map_region) IN (('A',1),('B',2));

相关问题