search inner join with multiple conditions

Question

i am trying to create a search feature that searches through the the book title, book author, book ISBN or via the keywords(which are connected to the books via inner join composite table). im not getting the results i am looking for;

depending on what i search i get either of these results:

• it sometimes returns duplicate results if the search term e.g appears on the book title and keyword name column. say if i'm searching for "hulk" and "hulk" appears on the title of the book and that book's joined keyword, it will return that same book twice etc
• it only searches through the keywords table and ignores the other "OR WHERE" clauses for it to search in the other specified columns.

i have tried the "SELECT DISTINCT.." sql statement but still doesn't work

Tables in database:

1. book table //book information is store

   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 ;
   

2. Book_keyword table //this table is where a book is assigned a keyword

   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;
   

3. keyword table

   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 code/////

$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

        }

Answer 1

The reason that it's ignoring the extra WHERE condition is that all of your joins are INNER joins. Using left joins will show results if anything in the where clause matches rather than enforcing the JOIN. The problem of duplicate results can be solved by using 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;

Answer 2

I would format the SQL somewhat better. That makes it easier to spot errors.

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

The trick is to group by books. Notice that you might have several keyword hits. I therefore used the grouping function GROUP_CONCAT.

Sorry, I cannot really test this, because I have no database to work with.