我有一个图书搜索，正在搜索的书籍，我正在寻找有它搜索的书籍，共享作者类别或出版商。我已经设置了它，它适用于or子句，例如有一个类别的儿童或与他们相关的历史的书籍，这是正确的工作，但当我搜索属于两个类别（和）的书籍时，例如儿童和魔法（哈利波特），它不显示这些书籍，即使它们在数据库中链接。
以上是我使用的搜索，或者，当我搜索属于儿童和历史的书籍时
上面你可以看到，当哈利波特的书确实属于这两种类型时，我没有得到任何关于分享儿童和魔法的结果。
上面是数据库中的链接，该链接为每本书提供了它们的类别，magic是类别1，childrens是类别2，您可以看到它们都共享这些类别。
下面是查询的php代码
<?php
include 'header.php';
include 'searchscriptTEST.php';
$sql = "SELECT DISTINCT bk.title AS Title, bk.bookid AS BookID, bk.year AS Year, bk.publisher AS Publisher, aut.authorname AS Author
FROM book bk
JOIN book_category bk_cat
ON bk_cat.book_id = bk.bookid
JOIN categories cat
ON cat.id = bk_cat.category_id
JOIN books_authors bk_aut
ON bk_aut.book_id = bk.bookid
JOIN authors aut
ON aut.id = bk_aut.author_id";
if(isset($_GET['searchInput'])){
$input = $_GET['searchInput'];
$input = preg_replace('/[^A-Za-z0-9]/', '', $input);
}
if (isset($input)){
$getters = array();
$queries = array();
foreach ($_GET as $key => $value) {
$temp = is_array($value) ? $value : trim($value);
if (!empty($temp)){
if (!in_array($key, $getters)){
$getters[$key] = $value;
}
}
}
if (!empty($getters)) {
foreach($getters as $key => $value){
//${$key} = $value;
switch ($key) {
case 'searchInput':
array_push($queries,"(bk.title LIKE '%{$getters['searchInput']}%'
|| bk.description LIKE '%{$getters['searchInput']}%' || bk.isbn LIKE '%{$getters['searchInput']}%'
|| bk.keywords LIKE '%{$getters['searchInput']}%' || aut.authorname LIKE '%{$getters['searchInput']}%')");
break;
case 'srch_publisher':
array_push($queries, "(bk.publisher = '{$getters["srch_publisher"]}')");
break;
case 'Year':
if(isset($getters['Year1']) ==""){
array_push($queries, "(bk.year = '{$getters['Year']}')");
} else {
array_push($queries, "(bk.year BETWEEN '$value' AND '{$getters['Year1']}')");
}
break;
case 'srch_author':
if(isset($getters['authorOperator']) ==""){
array_push($queries, "(bk_aut.author_id = '{$getters["srch_author"]}')");
} else {
$operator = $getters['authorOperator'];
array_push($queries, "(bk_aut.author_id = '$value' $operator bk_aut.author_id = '{$getters['srch_author1']}')");
}
break;
case 'srch_category':
if(isset($getters['catOperator']) ==""){
array_push($queries, "(bk_cat.category_id = '{$getters["srch_category"]}')");
} else {
$operator1 = $getters['catOperator'];
array_push($queries, "(bk_cat.category_id = '$value' $operator1 bk_cat.category_id = '{$getters['srch_category1']}')");
}
break;
}
}
}
if(!empty($queries)){
$sql .= " WHERE ";
$i = 1;
foreach ($queries as $query) {
if($i < count($queries)){
$sql .= $query." AND ";
} else {
$sql .= $query;
}
$i++;
}
}
$sql .= " GROUP BY bk.title ORDER BY bk.title ASC";
var_dump($sql);
}else{
$sql .= " GROUP BY bk.title ORDER BY bk.title ASC";
}
$rs = mysql_query($sql) or die(mysql_error());
$rows = mysql_fetch_assoc($rs);
$tot_rows = mysql_num_rows($rs);
?>
这是发送到数据库的sql转储，
SELECT
DISTINCT bk.title AS Title,
bk.bookid AS BookID,
bk.year AS Year,
bk.publisher AS Publisher,
aut.authorname AS Author
FROM book bk
JOIN book_category bk_cat ON bk_cat.book_id = bk.bookid
JOIN categories cat ON cat.id = bk_cat.category_id
JOIN books_authors bk_aut ON bk_aut.book_id = bk.bookid
JOIN authors aut ON aut.id = bk_aut.author_id
WHERE
(bk_cat.category_id = '2' AND bk_cat.category_id = '1')
GROUP BY bk.title
ORDER BY bk.title ASC
最佳答案
有多种方法可以做到这一点，但最简单的方法是使用类似这样的WHERE子句来SELECT属于多个类别的书籍：
WHERE
`bk`.`book_id` IN (SELECT `book_id` FROM `book_category` WHERE `category_id` = '2')
AND `bk`.`book_id` IN (SELECT `book_id` FROM `book_category` WHERE `category_id` = '1')
关于php - PHP AND子句未显示结果，但OR为，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22843943/