i have a database name category
parent_cat cat_id title
0 1 fruit
0 2 vehicle
0 3 goods
1 4 sour
1 5 sweet
1 6 mixed
2 7 sedan
2 8 hatchback
2 9 car
and i store a object in database table name product
obj_name parent_cat sub_id
mango 1 4,5,
maruti 2 7,8,9
bmw 2 7,9
i want to join the two table to show the data so i need to pass the parameter in URL ie. ?obj=vehicle i got by doing sql query
SELECT category.cat_id,category.title,product.parent_cat,product.obj_name
FROM category, product
WHERE category.cat_id=product.parent_cat
AND category.title='$title' --is a difined get variable
if title=fruit i got "mango" if title=vehicle i got maruti and bmw i want to know if title=sedan or title=car then how can i get maruti and bmw through loop any solution
You might want to use a LEFT JOIN query, if you have comma separated values for title use IN()
SELECT a.cat_id, a.title, b.parent_cat, b.obj_name
FROM product b
LEFT JOIN category a
ON a.cat_id = b.parent_cat
WHERE a.title IN($title);
Try this:
SELECT category.cat_id, category.title, product.obj_name, product.parent_cat, product.sub_id FROM category
LEFT JOIN product ON category.cat_id = product.parent_cat OR category.cat_id LIKE '%product.sub_id%'
WHERE category.title LIKE '%$title%'
Use LIKE instead of = when you're not sure of the exact data you're comparing or looking for.
[UPDATE]
SELECT category.cat_id, category.title, product.obj_name, product.parent_cat, product.sub_id FROM product
LEFT JOIN category ON (product.parent_cat = category.cat_id OR product.sub_id LIKE '%category.cat_id%') AND category.title LIKE '%$title%'
I'm joining the table in wrong direction, sorry for that.