How to get search result by a keyword from different table in MySQL for php?

Question

In my project(online shopping website), I have a search box like flipkart website. After enter a search keyword the result of 4 table will show in a page.

Table : `tbl_product`
Field : `id`,`date`,`title`,`model_no`,`quantity`,`category_id`,`brand_id`,`color`,`size`

Table : `tbl_brand`
Field : `id`,`date`,`brand_name`,`category_id`,`description`

Table : `tbl_category`
Field : `id`,`date`,`category_title`, `description`

Table : `tbl_product_specification`
Field : `id`, `date`, `product_id`,`specification_title`

Now, I want to get search result of $keyword variable as:

`title`,`model_no` from `tbl_product`
`brand_name` from `tbl_brand`
`category_title` from `tbl_category`
`specification_title` from `tbl_product_specification`

I'm not good in MySQL SQL query. So, what SQL will complete this?

Answer 1

Maybe something like this?

SELECT p.title, p.model_no,
    b.brand_name,
    c.category_title,
    s.specification_title
FROM tbl_product AS p
    LEFT JOIN tbl_brand AS b ON b.id = p.brand_id
    LEFT JOIN tbl_category AS c ON c.id = p.category_id
    LEFT JOIN tbl_product_specification AS s ON s.product_id = p.id
WHERE p.title LIKE 'keyword'
    OR p.model_no LIKE 'keyword'
    OR b.brand_name LIKE 'keyword'
    OR c.category_title LIKE 'keyword'
    OR s.specification_title LIKE 'keyword'

Answer 2

you should use union to join queries from different tables. something like:

select title, model_no 
from tbl_product
where title like 'mykeyword'
union
select brand_name, ''
from tbl_brand
where brand_name like 'mykeyword'
select category_title, ''
from tbl_category
where category_title like 'mykeyword'
union 
select specification_title, ''
from tbl_product_specification
where specification_title like 'mykeyword'
order by 1

note that: each separate query must return the same number of columns as well as the same data types in the same sequence for more info about Union.