Search query in two tables and ID

Question

I have a system that stores author name in one table:

author_id | author_name
        1 | J.K.Rowling
        2 | Ryan Quinn
        3 | Stephen King

and another table that stores books and uses author_id from above:

book_id | book_name | book_desc | book_author
      1 | Harry Potter | Boy who lived | 1
      2 | End of secrets | Some desc   | 2
      3 | Harry Potter2 | Flying wands | 1

And so on.. So I need to make a search query so if I enter word Harry it would show all books that has "Harry" in it's title OR description or author's name has harry.

It's my first time making search in PHP. Thanks in advance

Answer 1

You might want to consider full text search if you tables are large. Using standard SQL, you would do:

select b.book_id, b.book_name, b.book_desc, a.author_name
from books b join
     authors a
     on b.book_author = a.author_id
where b.book_name like '%Harry%' or
      b.book_desc like '%Harry%' or
      a.author_name like '%Harry%;