有 3 种不同的过滤器:books、authors 和 stores(select 列表)，我可能一次将它们全部一起使用，或者只使用其中的一两个，所以我使用 UNION 将所有查询放在一起

require('database.php');

if(isset($_POST['books'])){
    $books_ids = $_POST["books"];
}
if(isset($_POST['authors'])){
    $authors_ids = $_POST["authors"];
}
if(isset($_POST['stores'])){
    $stores_ids = $_POST["stores"];
}

$query = "";

if( !empty( $books_ids ))
{
    $books_ids_in = implode(',', array_fill(0, count($books_ids), '?'));

    $query .= "SELECT
        b.id,
        b.`name`,
        b.`year`,
        GROUP_CONCAT(DISTINCT a.`name`) AS author_names,
        GROUP_CONCAT(DISTINCT s.`name`) AS store_names,
        'book' as param
    FROM
        books AS b
        LEFT JOIN books_authors AS b_a ON b.id = b_a.book_id
        LEFT JOIN authors AS a ON a.id = b_a.author_id
        LEFT JOIN books_stores AS b_s ON b.id = b_s.book_id
        LEFT JOIN stores AS s ON s.id = b_s.store_id
    WHERE
        b.id IN (". $books_ids_in .")
    GROUP BY b.id
    ORDER BY b.id";
}

if( !empty( $authors_ids ) )
{
    $authors_ids_in = implode(',', array_fill(0, count($authors_ids), '?'));

    if (!empty($query)) {
         $query .= " UNION ";
    }

    $query .= "SELECT
        b.id,
        b.`name`,
        b.`year`,
        GROUP_CONCAT(DISTINCT a.`name`) AS author_names,
        GROUP_CONCAT(DISTINCT s.`name`) AS store_names,
        'author' as param
    FROM
        books AS b
        LEFT JOIN books_authors AS b_a ON b.id = b_a.book_id
        LEFT JOIN authors AS a ON a.id = b_a.author_id
        LEFT JOIN books_stores AS b_s ON b.id = b_s.book_id
        LEFT JOIN stores AS s ON s.id = b_s.store_id
    WHERE
        b.id IN (
            SELECT DISTINCT book_id FROM books_authors WHERE author_id IN (". $authors_ids_in .")
            )
    GROUP BY b.id
    ORDER BY b.id";
}

if( !empty( $stores_ids ) )
{
    $stores_ids_in = implode(',', array_fill(0, count($stores_ids), '?'));

    if (!empty($query)) {
         $query .= " UNION ";
    }

    $query .= "SELECT
        b.id,
        b.`name`,
        b.`year`,
        GROUP_CONCAT(DISTINCT a.`name`) AS author_names,
        GROUP_CONCAT(DISTINCT s.`name`) AS store_names,
        'store' as param
    FROM
        books AS b
        LEFT JOIN books_authors AS b_a ON b.id = b_a.book_id
        LEFT JOIN authors AS a ON a.id = b_a.author_id
        LEFT JOIN books_stores AS b_s ON b.id = b_s.book_id
        LEFT JOIN stores AS s ON s.id = b_s.store_id
    WHERE
        b.id IN (
            SELECT DISTINCT book_id FROM books_stores WHERE store_id IN (". $stores_ids_in .")
            )
    GROUP BY b.id
    ORDER BY b.id";
}


if( !empty( $query )) {

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

    if( !empty( $books_ids ))
    {
        foreach ($books_ids as $k => $id) {
            $stmt->bindValue(($k+1), $id);
        }
    }

    if( !empty( $authors_ids ))
    {
        foreach ($authors_ids as $k => $id) {
            $stmt->bindValue(($k+1), $id);
        }
    }

    if( !empty( $stores_ids ))
    {
        foreach ($stores_ids as $k => $id) {
            $stmt->bindValue(($k+1), $id);
        }
    }

    $stmt->execute();
    $results = $stmt->fetchAll();
    echo json_encode($results);
}

$conn = null;

当我只使用一个过滤器时代码工作得很好，但是当我尝试使用 2 个或更多时，我得到错误

fatal error :C:\xampp\htdocs\bookstore\filter.php 中的未捕获异常“PDOException”和消息“SQLSTATE[HY093]:无效参数编号:绑定(bind)变量的数量与 token 数量不匹配” :123 堆栈跟踪:#0 C:\xampp\htdocs\bookstore\filter.php(123): PDOStatement->execute() #1 {main} 在线抛出 C:\xampp\htdocs\bookstore\filter.php 123

我想，使用 bindValue 有问题，但我不知道如何解决？

UPD var_dump($query)(选择了 3 本书和 2 位作者)
string(1097) "SELECT b.id, b.name, b.year, GROUP_CONCAT(DISTINCT a.name) AS author_names, GROUP_CONCAT(DISTINCT s.name) AS store_names, 'book' 作为参数 FROM books AS b LEFT JOIN books_authors AS b_a ON b.id = b_a.book_id LEFT JOIN authors AS a ON a.id = b_a.author_id LEFT JOIN books_stores AS b_s ON b.id = b_s.book_id LEFT JOIN stores AS s ON s.id = b_s.store_id WHERE b.id IN (?,?,?) 按b.id ORDER BY b .id UNION SELECT b.id, b.name, b.year, GROUP_CONCAT(DISTINCT a.name) AS author_names, GROUP_CONCAT(DISTINCT s.name) AS store_names, 'author' 作为参数 FROM books AS b LEFT JOIN books_authors AS b_a ON b.id = b_a.book_id LEFT JOIN authors AS a.id = b_a.author_id LEFT JOIN books_stores AS b_s ON b.id = b_s.book_id LEFT JOIN stores AS s ON s.id = b_s.store_id WHERE b.id IN ( SELECT DISTINCT book_id FROM books_authors WHERE author_id IN (?,?) ) GROUP BY b.id按 b.id 订购"0 1201

最佳答案

构建动态查询的代码存在问题。 构建动态查询时，您需要将查询的静态部分与动态部分分开。

可以看到下面的代码是静态的。

$query = "SELECT
        b.id,   
        b.`name`,
        b.`year`,
        GROUP_CONCAT(DISTINCT a.`name`) AS author_names,
        GROUP_CONCAT(DISTINCT s.`name`) AS store_names,
        'book' as param
    FROM
        books AS b
        LEFT JOIN books_authors AS b_a ON b.id = b_a.book_id
        LEFT JOIN authors AS a ON a.id = b_a.author_id
        LEFT JOIN books_stores AS b_s ON b.id = b_s.book_id
        LEFT JOIN stores AS s ON s.id = b_s.store_id ";

还有

" GROUP BY b.id
  ORDER BY b.id";   

其余代码是动态的。 过滤记录时使用 WHERE 子句，AND & OR 运算符用于根据多个条件过滤记录。 如果第一个条件和第二个条件都为真，则 AND 运算符显示一条记录。 如果第一个条件或第二个条件为真，则 OR 运算符显示一条记录。 所以对于第一个条件，使用 WHERE 但之后必须使用 AND 或 OR(在您的示例中使用 OR)

// Static code
sql = "SELECT * FROM `table`"
// Set initial condition to WHERE       
clause = "WHERE";       
if( !empty( filter )){
    Add clause to sql 
    Add condition to sql
    change clause to OR or AND as required
}
Repeat for each filter
Note the filter is not changed until a filter is  not empty and remains changed once changed.
The remaining static code is added after all the filters have been handled

要允许应用不同的过滤器，您可以使用 flag .

$flag = 0;
if(isset($_POST['books'])){
    $books_ids = $_POST["books"];
    $flag += 1;

}
if(isset($_POST['authors'])){
    $authors_ids = $_POST["authors"];
    $flag += 10;
}
if(isset($_POST['stores'])){
    $stores_ids = $_POST["stores"];
    $flag += 100;
}

尽可能使用“惰性”绑定(bind) - 将数据传递给 execute 将大大缩短您的代码。 参见 PDO info 您需要合并数组才能执行此操作。使用带有标志的 switch 语句合并所需的数组。

switch ($flag) {
    case 1:
        $param_array = $books_ids;
        break;
    case 10:
        $param_array = $authors_ids;
        break;
    case 100:
        $param_array = $stores_ids;
        break;
    case 11://books & authors
        $param_array = array_merge($books_ids, $authors_ids); 
        break;
    case 101://books & stores
        $param_array = array_merge($books_ids, $stores_ids); 
        break;
    case 110://authors & stores
        $param_array = array_merge($authors_ids, $stores_ids); 
        break;
     case 111://books & authors & stores
        $param_array = array_merge(array_merge($books_ids,$authors_ids),$stores_ids);
        break;  

}

if( !empty( $query )) {
    $stmt = $conn->prepare($query); 
    $stmt->execute($param_array);
    $results = $stmt->fetchAll();
    echo json_encode($results);
}

下面的代码使用了以上几点。我已经回显了一些行以指示一旦测试完成就可以删除的结果。此外，一些代码已被注释掉以进行测试。

//Set flag
$flag = 0;
if(isset($_POST['books'])){
    $books_ids = $_POST["books"];
    $flag += 1;
}
if(isset($_POST['authors'])){
    $authors_ids = $_POST["authors"];
    $flag += 10;
}
if(isset($_POST['stores'])){
    $stores_ids = $_POST["stores"];
    $flag += 100;
}
echo $flag. " <BR>";//Remove after testing

//Basic SQL statement 
$query = "SELECT
        b.id,
        b.`name`,
        b.`year`,
        GROUP_CONCAT(DISTINCT a.`name`) AS author_names,
        GROUP_CONCAT(DISTINCT s.`name`) AS store_names,
        'book' as param
    FROM
        books AS b
        LEFT JOIN books_authors AS b_a ON b.id = b_a.book_id
        LEFT JOIN authors AS a ON a.id = b_a.author_id
        LEFT JOIN books_stores AS b_s ON b.id = b_s.book_id
        LEFT JOIN stores AS s ON s.id = b_s.store_id ";
// Set initial condition to WHERE       
$clause = "WHERE";      

if( !empty( $books_ids ))
{
    $books_ids_in = implode(',', array_fill(0, count($books_ids), '?'));
    $query .= $clause;
    $query .= " b.id IN (". $books_ids_in .")";
    // Set condition to OR for additional condition
    $clause = " OR ";   
}
if( !empty( $authors_ids ) )
{
    $authors_ids_in = implode(',', array_fill(0, count($authors_ids), '?'));

    /*  This part commented out as I don't see relevance
        if (!empty($query)) {
         $query .= " UNION ";
    }
    */
    $query .= $clause;
    $query .= "    b.id IN (
            SELECT DISTINCT book_id FROM books_authors WHERE author_id IN (". $authors_ids_in .")
            )";
    // Set condition to OR for additional condition     
    $clause = " OR ";       

}


if( !empty( $stores_ids ) )
{
    $stores_ids_in = implode(',', array_fill(0, count($stores_ids), '?'));

    /* if (!empty($query)) {
         $query .= " UNION ";
    }
    */
    $query .= $clause;
    $query .= " b.id IN (
            SELECT DISTINCT book_id FROM books_stores WHERE store_id IN (". $stores_ids_in .")
            )";
    $clause = " OR ";
}

//Add GROUP & ORDER
    $query .= " GROUP BY b.id
    ORDER BY b.id";
echo $query; //Remove after testing
//building $param_array
switch ($flag) {
    case 1:
        $param_array = $books_ids;
        break;
    case 10:
        $param_array = $authors_ids;
        break;
    case 100:
        $param_array = $stores_ids;
        break;
    case 11://books & authors
        $param_array = array_merge($books_ids, $authors_ids); 
        break;
    case 101://books & stores
        $param_array = array_merge($books_ids, $stores_ids); 
        break;
    case 110://authors & stores
        $param_array = array_merge($authors_ids, $stores_ids); 
        break;
     case 111://books & authors & stores
        $param_array = array_merge(array_merge($books_ids,$authors_ids),$stores_ids);
        break;  

}
echo "<br>";
print_r($param_array);// remove after testing
/*
if( !empty( $query )) {
    $stmt = $conn->prepare($query); 
    $stmt->execute($param_array);
    $results = $stmt->fetchAll();
    echo json_encode($results);
}

$conn = null;

关于php - 在 pdo 中绑定(bind)多个数组，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/35961854/