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?

Would you mind giving it a go first? It really is the best way to learn. You'll need to do `SELECT (columns) FROM table1 INNER JOIN ON (table1.column = table2.column)`. You can add as many joins as you need to get all tables included. For example, joining between `tbl_product` and `tbl_brand`, the join could be `tbl_product AS p INNER JOIN tbl_brand AS b ON (p.brand_id = b.id)`. — halfer, Aug 12 '14 at 21:53

score 0 · Answer 1 · answered Aug 12 '14 at 22:03

0

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.

answered Aug 12 '14 at 22:03

Dany Balian

608
7
16

if i add GROUP BY product_id? – Daniel Smith Aug 12 '14 at 22:13
you only have product_id in one table.. you can't group by it! – Dany Balian Aug 12 '14 at 22:27

score 0 · Accepted Answer · answered Aug 12 '14 at 22:15

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'

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

2 Answers2