php - combine search from multiple tables -
the current table structure have is
product_description [ (int)product_id, (varchar)name, (text)description, ... ] vendors [ (int)vendor_id, (varchar)vendor_name, (text)vendor_description, ... ] vendor [ (int)vendor, (int)product_id, ... ] currently have search option fetch matching values both tables. work independently, i.e. keyword search gets matching value both tables' name description fields.
now requirement has been changed client. client wants search dependent, i.e. suppose 1 search product associated vendors should fetched , if vendor searched associated products should visible. issue there no option determine whether user searching product or vendor.
is there way make search dependent?
after search there other filter work out location, price range, etc. result displayed on tab based page separate tab vendor , product , each tab has own pagination not affect other tab.
we suggested client give drop down elance near search bar user can specify want search for, changing design not possible now. how proceed searching?
after convincing able modify client's requirement solve both implementation problem provide appropriate value upon search. per new search guidelines provided, conditions are:
if < keyword > == product , show product on product tab, , related vendors selling products along < keyword > match of vendors.
if < keyword > == vendor , show vendor on vendor tab, , < keyword > match of products.
so basically, vendors dependent while, products independent.
query used product -
select p.product_id, pd.product_type, (select avg(rating) total review r1 r1.product_id = p.product_id , r1.status = '1' group r1.product_id) rating, (select price product_discount pd2 pd2.product_id = p.product_id , pd2.customer_group_id = '1' , pd2.quantity = '1' , ((pd2.date_start = '0000-00-00' or pd2.date_start < now()) , (pd2.date_end = '0000-00-00' or pd2.date_end > now())) order pd2.priority asc, pd2.price asc limit 1) discount, (select price product_special ps ps.product_id = p.product_id , ps.customer_group_id = '1' , ((ps.date_start = '0000-00-00' or ps.date_start < now()) , (ps.date_end = '0000-00-00' or ps.date_end > now())) order ps.priority asc, ps.price asc limit 1) special product p left join product_description pd on (p.product_id = pd.product_id) left join product_to_store p2s on (p.product_id = p2s.product_id) pd.language_id = '1' , p.status = '1' , p.date_available <= now() , p2s.store_id = '0' , ( pd.name '%<keyword>%' or pd.description '%<keyword>%' or pd.tag '%<keyword>%' or lcase(p.model) = '<keyword>' ) group p.product_id order p.image desc, lcase(pd.name) asc limit 0,15 query used vendor-
select distinct (vds.vendor_id), vds . * vendors vds left join user u on ( vds.user_id = u.user_id ) left join vendor v on vds.vendor_id = v.vendor left join product_description pd on v.vproduct_id = pd.product_id left join product p on v.vproduct_id = p.product_id u.status = '1' , p.status = '1' , vds.display_vendor = '1' , ( lower( vds.vendor_name ) lower( "%<keyword>%" ) or lower( vds.vendor_description ) lower( "%<keyword>%" ) or lower( pd.name ) lower( "%<keyword>%" ) or lower( pd.description ) lower( "%<keyword>%" ) ) order vds.vendor_name asc limit 0 , 30