SitePoint Sponsor |
|
User Tag List
Results 1 to 6 of 6
Thread: Php narrow search
-
Nov 12, 2009, 01:38 #1
Php narrow search
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.
-
Nov 12, 2009, 01:50 #2
- Join Date
- Oct 2009
- Posts
- 1,852
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Does product belongs to only one category?
if so, no prdcategory table needed and just one simple join will make it
-
Nov 12, 2009, 01:56 #3
one product belong to many categories.
-
Nov 12, 2009, 02:11 #4
- Join Date
- Oct 2009
- Posts
- 1,852
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
well, it will need more joins and group by and group_concat()
-
Nov 12, 2009, 03:18 #5
but how i use those?
-
Nov 12, 2009, 05:27 #6
- Join Date
- Oct 2009
- Posts
- 1,852
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
well you have to join all three tables in one query
something like
Code SQL:SELECT * FROM prod AS p JOIN prodcat AS pc ON p.id=pc.pid JOIN cat AS c ON c.id=pc.cid
Bookmarks