php - Mysql SELECT WHERE $VAR IN $VAR,$VAR,$VAR

Question

这个问题已经有答案了:

Can I bind an array to an IN() condition in a PDO query? (23 个回答)

已关闭 5 年前。

任何人都可以看到我如何在该语句中使用字符串而不是数字，

我试图在 $delivery="ALL"时使用 $delivery 拉取所有行，但我只能拉取 1 个单个变量，如果我想拉取一个数组，我不能，因为我将数组转换为字符串，

$delivery_con = [];
if ($delivery==="collection") { $delivery_con[]="no";}
if ($delivery==="delivery") {$delivery_con[]="yes";}
if ($delivery==="local") {$delivery_con[]="yes local";}

if ($delivery==="either") {$delivery_con=["no","yes","yes local"];}


$query="SELECT * 
        FROM testdata 
        WHERE title LIKE ? 
        AND location LIKE ? 
        AND postcode LIKE ? 
        AND price >=? 
        AND price <=? 
        AND cond=? 
        AND catagory LIKE ? 
        AND delivery IN ? 
        ORDER BY $order $dir";

$stat=$db->prepare($query);

$stat->execute(array("%$searchfor%",
                    "%$location%",
                    "%$postcode%",
                    "$pricefrom",
                    "$priceto",
                    "$cond",
                    "%$catagory%",
                    "$delivery_con"));

所以我的问题是，如何解决这个问题，让 select 函数与 $variables 一起使用，

我真的被困住了。如果有人可以帮忙

谢谢。

Answer 1

使用 IN() SQL 运算符时，您需要手动创建一组 ? 并将它们放入查询中:

<?php

$delivery_con = [];

if ($delivery === "collection") {
    $delivery_con[] = "no";
}
if ($delivery === "delivery") {
    $delivery_con[] = "yes";
}
if ($delivery === "local") {
    $delivery_con[] = "yes local";
}

if ($delivery==="either") {$delivery_con=["no","yes","yes local"];}


$in = str_repeat('?,', count($delivery_con) - 1) . '?';


$searchfor = "%$searchfor%";
$location  = "%$location%";
$postcode  = "%$postcode%";
$catagory  = "%$catagory%";

$array1 = array($searchfor,$location,$postcode,$pricefrom,$priceto,$cond,$catagory);

$params = array_merge($array1, $delivery_con);

$query = "SELECT * 
        FROM testdata 
        WHERE title LIKE ? 
        AND location LIKE ? 
        AND postcode LIKE ? 
        AND price >=? 
        AND price <=? 
        AND cond=? 
        AND catagory LIKE ? 
        AND delivery IN ($in) 
        ORDER BY $order $dir";

$stat = $db->prepare($query);

$stat->execute([$params]);