SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jul 2011
    Location
    Port Pirie, South Australia
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database driven navigation question.

    Hi, I have created a drop down navigation that looks like this,

    All stock
    Leather recliner suites
    Black
    Brown
    Cream
    Non leather recliner suites
    Black
    Brown
    Cream
    Specialized leather suites
    Chesterfield
    Chaise

    Here is the code.

    Code HTML4Strict:
     <li class="top"><a href=" " class="top_link"><span class="down">All Stock</span></a>
                <ul class="sub">
    Code PHP:
    <?php
              $query ="SELECT cat_name, item_name FROM categories c, cat_items j, items i
                    where c.cat_id = j.cat_id and i.item_id = j.item_id and cat_menu_indicator >0 
    					 order by cat_menu_indicator";
    	    $result =&mysql_query($query);
    		 $prev_cat =" ";
    		 while($row =mysql_fetch_assoc($result)) {
    			 if ($row['cat_name'] != $prev_cat)
    			 {
    				 if ($prev_cat != ' ')
    				 {
    					 echo '</ul></li>'."\n";
    				 }
    				 echo '<li><a href=" "  class="fly">'.$row['cat_name'].'</a>'."\n";
    				 echo  '<ul>';
    				 $prev_cat = $row['cat_name'];
    			 }
    			 echo '<li><a href=" ">'.$row['item_name'].'</a></li>'."\n";
     
    		 }
    		 echo '</ul></li></ul></li>'."\n";
    ?>

    My Question is about the href on each link. I'm not sure how to get the right items from the database, based on what the user clicks on.
    If the user clicks on leather recliner suites i want all leather recliner suites to be displayed, or if the user clicks on black recliner suites, just the black ones will be displayed, ect
    I was thinking of a separate query for each link but that does not feel right. Something like a parameter with the one query, maybe.

    Thanks for any help.

  2. #2
    Non-Member
    Join Date
    Jun 2012
    Posts
    88
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    All your categories and subcategories should have their own ID in the database. So just attach the appropriate parameter name and ID for each menu item as a query string to the url of the php script that will run the query to get the info for the clicked menu item.

  3. #3
    Resident OCD goofball! bronze trophy Serenarules's Avatar
    Join Date
    Dec 2002
    Posts
    1,911
    Mentioned
    26 Post(s)
    Tagged
    0 Thread(s)
    From your sql, I can gether that cat_items is a pivot table linking categories and items? It also appears you are building your category selector from the same sql statement you are building the display from. This probably isn't the best approach as it is returning more records than required with every request. Also, some of the fields names in your query aren't qualified with the table aliases you choose, so it's hard to tell which table they coming from, therefore, this might be inaccurate...

    PHP Code:
    // grab just the data needed to build your menu
    $navigationalQuery "
        select
            c.cat_id,
            c.cat_name
        from
            categories c
        order by
            c.cat_menu_indicator
    "
    ;

    // get the results
    $results mysql_query($navigationalQuery);

    // now build the menu display

    // other stuff here

    // display all items (initially)...
    $itemQuery "
        select
            i.item_id,
            i.item_name
        from
            items i
        join
            cat_items ci on ci.item_id = i.item_id
        join
            categories c on c.cat_id = ci.cat_id
    "
    ;

    // but if this is a postback and we have a specific cat id
    if (isset($_GET['cat_id']))
        
    $itemQuery $itemQuery " where c.cat_id = '$cat_id'";

    // get the results
    $results mysql_query($itemQuery);
    // now build the item display 
    Category link...as an example. You should be able to infer the rest.
    PHP Code:
    echo(sprintf(
        
    '<li><a href="items.php?cat_id=%d">%s</a></li>',
        
    $results[cat_id],
        
    $results[cat_name]
    )); 
    Hope that helps. My SQL is a bit rusty as I don't write it very much. You'll need to double check it.

    NOTE: Inserting variables into sql is very risky. Please look at mysqli prepared statement or the PDO extensions.


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
  •