我正在尝试创建一个搜索功能,该功能通过书名、图书作者、图书ISBN或关键字(通过内部联接组合表连接到图书)进行搜索。我没有得到我想要的结果;
根据我搜索的内容,我会得到以下结果之一:
- 如果搜索词出现在书名和关键字名称列上,它有时会返回重复的结果。比如说,如果我在搜索"hulk","hurk"出现在书名和该书的joind关键字上,它会两次返回同一本书等
- 它只搜索关键字表,而忽略其他"OR WHERE"子句,以便在其他指定列中进行搜索
我尝试过"SELECT DISTINCT…"sql语句,但仍然无法使用
数据库中的表:
-
图书表//图书信息是存储
CREATE TABLE IF NOT EXISTS books ( book_id` int(11) NOT NULL AUTO_INCREMENT, book_title` varchar(100) NOT NULL, book_author` varchar(100) NOT NULL, book_ISBN` varchar(100) NOT NULL, book_description` varchar(1000) NOT NULL, book_price` double NOT NULL, book_image` varchar(100) NOT NULL, book_stock_quantity` int(11) NOT NULL, PRIMARY KEY (`book_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=61 ;
-
Book_关键字表//该表是为一本书分配关键字的地方
CREATE TABLE IF NOT EXISTS books_keyword ( books_keyword_book_id` int(10) NOT NULL, books_keyword_keyword_id` int(10) NOT NULL, KEY `BOOK_FK` (`books_keyword_book_id`), KEY `KEYWORD_FK` (`books_keyword_keyword_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-
关键字表
CREATE TABLE keyword ( keyword_id` int(10) NOT NULL AUTO_INCREMENT, keyword_name` varchar(50) NOT NULL, PRIMARY KEY (`keyword_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
///php代码////
$search = $_POST["Search"]; // get text from textbox and insert into variable
$mysqli = new mysqli('localhost', 'root','','bookstore'); //connect to database
$searchSQL = "SELECT books.*, books_keyword.books_keyword_keyword_id AS keyword_id, keyword.keyword_name FROM books JOIN books_keyword ON (books.book_id = books_keyword_book_id) JOIN keyword ON(books_keyword.books_keyword_keyword_id = keyword_id) WHERE keyword_name LIKE '%" . $search . "%' OR book_title LIKE '%" . $search . "%' OR book_ISBN LIKE '" . $search ."' OR book_author LIKE '%" . $search ."%'";
$Results = $mysqli->query($SearchSql); //run sql
while($SearchRow = $Results->fetch_assoc()) //print out each result
{
echo '<P>'.$SearchRow["book_title"].'</P>'; //echo book title in the search results
}
我会更好地格式化SQL。这样可以更容易地发现错误。
SELECT
books.*,
GROUP_CONCAT(DISTINCT books_keyword.books_keyword_keyword_id SEPARATOR ',') AS keyword_ids,
GROUP_CONCAT(DISTINCT keyword.keyword_name SEPARATOR ',') AD keyword_names
FROM
books
JOIN books_keyword ON (books.book_id = books_keyword.books_keyword_book_id)
JOIN keyword ON (books_keyword.books_keyword_keyword_id = keyword.keyword_id)
WHERE
keyword.keyword_name LIKE '%".$search."%' OR
books.book_title LIKE '%".$search."%' OR
books.book_ISBN LIKE '".$search."' OR
books.book_author LIKE '%".$search ."%'";
GROUP BY
books.book_id
诀窍是按书本分组。请注意,您可能有多个关键字命中。因此,我使用了分组函数CCD_ 1。
很抱歉,我真的无法测试这个,因为我没有可以使用的数据库。
它忽略额外WHERE条件的原因是所有联接都是INNER联接。如果where子句中有任何内容匹配,则使用左联接将显示结果,而不是强制执行JOIN。重复结果的问题可以通过使用GROUP by:来解决
SELECT books.*, books_keyword.books_keyword_keyword_id AS keyword_id, keyword.keyword_name
FROM books
LEFT JOIN books_keyword ON (books.book_id = books_keyword_book_id)
LEFT JOIN keyword ON(books_keyword.books_keyword_keyword_id = keyword_id)
WHERE keyword_name LIKE '%" . $search . "%' OR book_title LIKE '%" . $search . "%' OR book_ISBN LIKE '" . $search ."' OR book_author LIKE '%" . $search ."%'
GROUP BY books.book_id;