WHERE 子句的动态 PDO 参数绑定问题 [英] Dynamic PDO parameter binding issue with WHERE clause

问题描述

我有一个搜索表单，用户可以在其中输入几条信息来搜索数据库中的记录.由于某些字段可以留空，因此我正在动态创建查询的 WHERE 子句以及动态绑定 PDO 参数.如果用户只填写搜索表单中的 1 个字段，则一切正常，但如果使用了 1 个以上的字段，则返回一个空数组.这是我的代码.

I have a search form where users can enter a few pieces of information to search for records in the database. Due to the fact that some of the fields can be left blank, I am dynamically creating the WHERE clause of the query as well as dynamically binding the PDO parameters. Everything works great if the user only fills out 1 field in the search form but if more than 1 field is used then an empty array is returned. Here is my code.

if(count($_POST)>0)
{   
    //Remove any key that has no value  
    $data = array_filter($_POST);

    //Define array to hold the pieces of the where clause
    $where = array();

    //loop each of the variable to build the query
    foreach($data as $key=>$value)
    {
        $key = mysql_real_escape_string($key);

        //Push values to array
        array_push($where, "$key=:$key");
    }

    //Create the select query
        $query = "SELECT application_ID, 
                     student_last_name, 
                     student_first_name,
                     s.school_name,
                     DATE_FORMAT(submission_datetime, '%m/%d/%Y %h:%i:%s %p') AS  submission_datetime, 
                     aps.name  
                     FROM application a
                     LEFT JOIN application_status aps ON(aps.status_ID = a.application_status_ID)
                     LEFT JOIN schools s ON(s.school_ID = a.school_choice)";
    //As long as criteria was selected in the search form then add the where clause to the query with user's search criteria
    if(!empty($where))
    {       
        $query .= "WHERE ".implode(" AND ", $where);
    }

    //Add ORDER BY clause to the query
    $query .= " ORDER BY application_ID";

    $stmt = $conn->prepare($query);
    //loop each of the variables to bind parameters
    foreach($data as $key=>$value)
        {
            $value = mysql_real_escape_string($value);
            $stmt->bindparam(':'.$key, $value);
        }
    $stmt->execute();
    $result = $stmt->fetchall(PDO::FETCH_ASSOC);


}

当我回显查询时，一切看起来都很好，甚至在从 PHPMyAdmin 运行时返回结果.这是查询.

When I echo the query everything looks fine and even returns results when run from PHPMyAdmin. Here is the query.

SELECT application_ID, 
       student_last_name, 
       student_first_name, 
       s.school_name, 
       DATE_FORMAT(submission_datetime, '%m/%d/%Y %h:%i:%s %p') AS submission_datetime,
       aps.name 
       FROM application a 
       LEFT JOIN application_status aps ON(aps.status_ID = a.application_status_ID)
       LEFT JOIN schools s ON(s.school_ID = a.school_choice)
       WHERE school_choice=:school_choice AND status_ID=:status_ID 
       ORDER BY application_ID ASC

当我 print_r 时，我得到一个空数组.感谢您提供的任何帮助.

When I print_r I get an empty array. Thanks for any help you can provide.

推荐答案

当您遍历数组以将值绑定到 PDO 语句时，您应该使用 bindValue 而不是 bindParam.

When you iterate through an array to bind values to the PDO statement you should use bindValue instead of bindParam.

当你说 $stmt->bindparam(':'.$key, $value) 时，查询将使用变量 $value 的值作为它是在查询执行时.$value 的值将是数组的最后一个元素.

When you say $stmt->bindparam(':'.$key, $value), the query will use the value of the variable $value as it is at the time of the query execution. Value of $value will be the last element of the array.

http://php.net/manual/en/pdostatement.bindvalue.php

我希望这会有所帮助.

这篇关于WHERE 子句的动态 PDO 参数绑定问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！