[SOLVED] Mysql search by category

[chrischen]

So I have a merchant table with merchant details. I want to be able to search for merchants by category. I have a category table that ties merchant IDs to category IDs so that merchants can have more than one category. But the problem I have is how do I make it search by a category if the category is in another table.

 

[search Box] [Drop down list of categories] [go]

 

Results page should display merchant details pulled from the merchant table based on the query but only on in a specific category.

 

In the category table there is merchantId and categoryId. And specific merchants might have more than one entry in that database if it falls into another category. I have everything else working except this search by category.

 

Thanks.

[Barand]

SELECT m.merchant_name

FROM merchant m

INNER JOIN category c ON c.merchantID = m.id

WHERE c.categoryID = '$selectedCategory'

[chrischen]

Great thanks for the fast reply!

 

I have one more question and it is about the way I store category data. Is it more efficient to store categories in the merchants table by comma delimiting the category ids (for example under the category column: 12,25,1,7) or by doing it the way I am right now with a separate table that matches category ID to merchant ID?

 

Thanks.

[chrischen]

SELECT m.merchant_name

FROM merchant m

INNER JOIN category c ON c.merchantID = m.id

WHERE c.categoryID = '$selectedCategory'

 

What's the m in "merchant m" and c in "category c"

In m.merchant_name m is the table name right?

[pocobueno1388]

That is called aliasing. It basically just shortens the table name for the query so you don't have to type it out all the time.

[Barand]

Great thanks for the fast reply!

 

I have one more question and it is about the way I store category data. Is it more efficient to store categories in the merchants table by comma delimiting the category ids (for example under the category column: 12,25,1,7) or by doing it the way I am right now with a separate table that matches category ID to merchant ID?

 

Thanks.

 

As you have it now, normalised. Don't even consider 12,25,1,7

[Barand]

That is called aliasing. It basically just shortens the table name for the query so you don't have to type it out all the time.

 

That's part of the story. Sometimes it's essential if you meed to reference the same table twice in a query

 

eg

http://www.phpfreaks.com/forums/index.php/topic,180282.msg804214.html#msg804214

[chrischen]

Ok great thanks!

 

By the way it worked perfectly!