SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot asrasrasr's Avatar
    Join Date
    Apr 2009
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

  2. #2
    Non-Member
    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

  3. #3
    SitePoint Zealot asrasrasr's Avatar
    Join Date
    Apr 2009
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    one product belong to many categories.

  4. #4
    Non-Member
    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()

  5. #5
    SitePoint Zealot asrasrasr's Avatar
    Join Date
    Apr 2009
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but how i use those?

  6. #6
    Non-Member
    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
    there is an SQL guy hangs around here. he can build queries like this with one eyewink!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •