有 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: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens' in C:'xampp'htdocs'bookstore'filter.php:123 Stack trace: #0 C:'xampp'htdocs'bookstore'filter.php(123): PDOStatement->execute() #1 {main} thrown in C:'xampp'htdocs'bookstore'filter.php on line 123
我想,bindValue
使用有问题,但我不知道如何解决?
UPD var_dump($query)
(选择3本书和2位作者)
string(1097) "SELECT b.id, b.
名称, b.
年份, GROUP_CONCAT(DISTINCT a.
名称) AS author_names, GROUP_CONCAT(DISTINCT s.
名称) 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 (?,?,?) GROUP BY b.id ORDER BY b.id UNION SELECT b.id, b.
名称, b.
年份, GROUP_CONCAT(DISTINCT a.
名称) AS author_names, GROUP_CONCAT(DISTINCT s.
名称) 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 (?,?) ) GROUP BY b.id ORDER BY b.id" 01201
用于生成动态查询的代码存在问题。构建动态查询时,需要将查询的静态部分与动态部分分开。
您可以看到以下代码是静态的。
$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 = 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;
}
尽可能使用"惰性"绑定 - 将数据传递到执行将大大缩短代码。查看 PDO 信息您需要合并数组才能执行此操作。使用带有标志的 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;
不要使用相同的$k; 使用变量并随着每个绑定而递增;见下文
$bindingIndex = 0;
if( !empty( $books_ids ))
{
foreach ($books_ids as $k => $id) {
$stmt->bindValue((++$bindingIndex), $id);
}
}
if( !empty( $authors_ids ))
{
foreach ($authors_ids as $k => $id) {
$stmt->bindValue((++$bindingIndex), $id);
}
}
if( !empty( $stores_ids ))
{
foreach ($stores_ids as $k => $id) {
$stmt->bindValue((++$bindingIndex), $id);
}
}