Database driven navigation question

PHP
1

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.


 <li class="top"><a href=" " class="top_link"><span class="down">All Stock</span></a>
            <ul class="sub">

<?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>'."\
";
				 }
				 echo '<li><a href=" "  class="fly">'.$row['cat_name'].'</a>'."\
";
				 echo  '<ul>';
				 $prev_cat = $row['cat_name'];
			 }
			 echo '<li><a href=" ">'.$row['item_name'].'</a></li>'."\
";
			
		 }
		 echo '</ul></li></ul></li>'."\
";
?>

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

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

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…


// 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.


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.