php - 数组到字符串转换错误/PDO 在数组中执行数组

Question

所以我在使用 PDO 执行执行查询时遇到问题。我不断从服务器返回的错误是我有一个数组到字符串的转换。然后它告诉我在 execute(array()) 代码行中输入的参数数量无效。我还在错误代码中得到一个 1 缓冲区，这意味着可能存在一些错误的串联。

该功能是一个搜索页面，用户可以在其中输入最多 6 个参数来搜索书籍。有 6 个 if 语句用于检查是否为空，如果不为空，则该变量将添加到数组 params 中，并且是连接到开始语句末尾的 SQL 查询的一部分。这是相关代码:

$title = $_POST['title'];
$author = $_POST['author'];
$publisher = $_POST['publisher'];
$isbn = $_POST['isbn'];
$condition = $_POST['condition'];
$status = $_POST['status'];

//array to hold variables
$params = array();
$concat_stmt_orig = "SELECT * FROM Book WHERE ";
$concat_stmt = "SELECT * FROM Book WHERE ";

//Check for Title
if(!empty($title)){
    //add title to the end
    $concat_stmt = $concat_stmt." `Title` = ?";
    //add variable to print list
    array_push($params, $title);

}
//Check for Author
if(!empty($author)){
    if(strlen($concat_stmt) !== strlen($concat_stmt_orig)){
        //add author to the end
        $concat_stmt = $concat_stmt." AND `Author1` = ?";
        //add variable to print list
        array_push($params, $author);
    }
    else{
        //add author to the end
        $concat_stmt = $concat_stmt." `Author1` = ?";
        //add variable to print list
        array_push($params, $author);
    }
}

//Check for publisher
if(!empty($publisher)){
    if(strlen($concat_stmt) !== strlen($concat_stmt_orig)){
        //add publisher to the end
        $concat_stmt = $concat_stmt." AND `Publisher` = ?";
        //add variable to print list
        array_push($params, $publisher);
    }
    else{
        //add pubisher to the end
        $concat_stmt = $concat_stmt." `Publisher` = ?";
        //add publisher to print list
        array_push($params, $publisher);
    }
}
//check for ISBN
if(!empty($isbn)){
    if(strlen($concat_stmt) !== strlen($concat_stmt_orig)){
        //add isbn to the end
        $concat_stmt = $concat_stmt." AND `ISBN` = ?";
        //add variable to print list
        array_push($params, $isbn);
    }
    else{
        //add pubisher to the end
        $concat_stmt = $concat_stmt." `ISBN` = ?";
        //add publisher to print list
        array_push($params, $isbn);
    }
}
//check for condition
if(!empty($condition)){
    if(strlen($concat_stmt) !== strlen($concat_stmt_orig)){
        //add condition to the end
        $concat_stmt = $concat_stmt." AND `BookCondition` = ?";
        //add condition to print list
        array_push($params, $condition);
    }
    else{
        //add condition to the end
        $concat_stmt = $concat_stmt." `BookCondition` = ?";
        //add condition to print list
        array_push($params, $condition);
    }
}

//check for status
if(!empty($status)){
    if(strlen($concat_stmt) !== strlen($concat_stmt_orig)){
        //add status to the end
        $concat_stmt = $concat_stmt." AND `Status` = ?";
        //add variable to print list
        array_push($params, $status);
    }
    else{
        //add condition to the end
        $concat_stmt = $concat_stmt." `Status` = ?";
        //add condition to print list
        array_push($params, $status);
    }
}

 //prepare statement for querying
 $stmt_retrieve = $db->prepare($concat_stmt);

 echo print_r($stmt_retrieve);
 var_dump($params);

 //exectue query
 $stmt_retrieve->execute(array($params));
}

echo 和 var dump 用于测试目的，以检查 sql 语句和数组值的正确数量

这里是错误

PDOStatement Object ( [queryString] => SELECT * FROM Book WHERE Title = ? AND Author1 = ? ) 1array(2) { [0]=> string(13) "Example Title" [1]=> string(14) "Example Author" } Notice: Array to string conversion in...."yadayadayada"

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens'...."yadayadayada"

我尝试输入帖子值作为“$title”，“$author”...等，但仍然产生错误，我也尝试过 printf 和其他。

只有当我尝试输入多个变量进行搜索时，才会出现这些错误。当我只按一个变量搜索时，程序没有错误，但仍然不会返回任何内容。

感谢任何帮助或指导:)

Answer 1

使用$stmt_retrieve->execute($params);

目前，您拥有相当于:

$stmt_retrieve->execute(array(array('title','author1',....)));

看到那里的双数组了吗？

@tadman 是对的，您当前的代码有大量代码重复，很快就会变得无法维护。

这样的事情会有所帮助:

$fields = array(
   'Title'   => $_POST['title'],
   'Author1' => $_POST['author'],
   ....);

$params = array();
$wheres = array();

foreach($fields as $fieldname => $value){
    if(!empty($value)){
        $wheres[] = "`$fieldname` = ?";
        $params[] = $value;
    }
}
$stmt = 'SELECT * FROM Book';
if(!empty($wheres)) $stmt .= ' WHERE '.implode(' AND ',$wheres);
$stmt_retrieve = $db->prepare($stmt);
$stmt_retrieve->execute($params);