Php narrow search

[asrasrasr]

Hello friends,

i wany narrow search like this url

http://www.cymaxstores.com/Common/Ne...ch/Search.aspx

i have 3 tables in database.
first category - which have category name and category id

second product - which have product name, product id and brand name

third prdcategory - which have product id with corressponding category id


now when i search product with brand name i execute 3 mysql queries-
$prdSQL = "SELECT prd_id from product where prd_brand LIKE '%$search%'";
$rsPrdSet = mysql_query($prdSQL, $conn) or die(mysql_error());
while($rowPrdSet=mysql_fetch_assoc($rsPrdSet)){

$catSQL = "SELECT cat_id from prdcategory where prd_id ='$rowPrdSet[prdid]'";
$rsCatSet = mysql_query($catSQL, $conn) or die(mysql_error());
while($rowCatSet=mysql_fetch_assoc($rsCatSet)){

$catTitleSQL = "SELECT cat_title from category where cat_id ='$rowCatSet[cat_id]'";
$rsCatTitleSet = mysql_query($catTitleSQL, $conn) or die(mysql_error());
while($rowCatTitleSet=mysql_fetch_assoc($rsCatTitleSet)){
echo "<li><a href='products.php?cat={$_GET['cat_id']}}'>{$rowCatTitleSet['cat_title']} ()</a></li>";
}
}
}

but this repeat me category name many time like

Categories

* Office Desks ()
* Nightstands ()
* Nightstands ()
* Nightstands ()
* CD/DVD Storage ()
* CD/DVD Storage ()
* Cubby and Storage Benches ()
* Cubby and Storage Benches ()
* CD/DVD Storage ()
* Nightstands ()
* Nightstands ()
* Cubby and Storage Benches ()
* Cubby and Storage Benches ()
* Cubby and Storage Benches ()
* Cubby and Storage Benches ()
* CD/DVD Storage ()

now how i accees this search like above url. with item number in each category.

[Shrapnel_N5]

Does product belongs to only one category?
if so, no prdcategory table needed and just one simple join will make it

[asrasrasr]

one product belong to many categories.

[Shrapnel_N5]

well, it will need more joins and group by and group_concat()

[asrasrasr]

but how i use those?

[Shrapnel_N5]

well you have to join all three tables in one query
something like

SELECT * 
FROM prod AS p 
JOIN prodcat AS pc ON p.id=pc.pid 
JOIN cat AS c ON c.id=pc.cid

there is an SQL guy hangs around here. he can build queries like this with one eyewink!