Populating a drop-down menu with MySQL data

PHP
1

Hi all,

I’m quite new to PHP and I’m having problems populating a two-level menu with data from MySQL. I need the top level of the menu to display my main categories: Clothing, Shoes, Accessories, Jewellery, Lingerie & Swimwear. The sub-level needs to display sub categories related to each of the main categories. For example, under Clothing there should be the following sub-menu: Dresses, Tops, Knitwear, Jeans, Shorts etc.

In MySQL I have 1 table called categories with columns cat_id, cat_name and parent. I have all my categories (main and sub) in this table, the main categories have their parent set to 0, and the sub categories have a relevant parent’s id (for example, Dresses has a parent of 1 for Clothing; Heels have a parent of 12 for Shoes and so on).

I need to use this structure to populate a drop-down menu. I have the following code so far, it fetches the main categories correctly and only 1 related sub-category (as there is no loop to fetch other related sub-categories, and if I add that loop, it all breaks completely…)


$result1 = mysql_query("SELECT DISTINCT L1.cat_name AS cat,
L2.cat_name AS sub_cat FROM categories L1
LEFT OUTER JOIN categories L2 ON L1.cat_id = L2.parent     
WHERE L1.parent = '0' GROUP BY L1.cat_id");?>

<div id="nav">

<ul class="dropdown">

<?while ($row = mysql_fetch_array($result1))
{
$cat_name=$row['cat'];
$_GET['cat']=$cat_name; 
$sub_cat_name=$row['sub_cat'];
$_GET['sub_cat']=$sub_cat_name;?> 

<li><a href="<?echo "browse.php?cat_name=$cat_name"?>"><?echo $row['cat'];?></a>


  <ul class="sub_menu">

<li><a href="<?echo "browse.php?cat_name=$sub_cat_name"?>"><?echo $row['sub_cat'];?></a></li>


  </ul>

   
</li>
 <?}?> 

</ul> 

</div>

Am I on the right track? If so, what is this code missing? Thanks very much in advance.

2

the result set returned by the query is exactly what you want

sorry, i don’t do php, or i’d give you some code

basically you loop over the rows in the result set and print the new category whenever it changes

3

Thanks again. Almost but not quite. Fetches each main and sub item individually, so I have as many ‘Clothing’ instances as there are related subcategories. My menu now has 8 instances of Clothing (as many as there are related sub categories) with each sub-category fetched one per item. So Clothing - Dresses, Clothing - Jeans, Clothing - Tops etc., Accessories - Belts, Accessories - Scarves etc. I need just one Clothing instance and all the related subcategories appearing when you roll over this instance. So Clothing - Dresses, Jeans, Tops etc.

Any idea how to achieve this? do I need to change my html?

4

hmmmmmm :scratch:

I don’t see why it should once you understand it. The lft and rgt values are used to store the multilevel hierachy and imho make it so much easier to move categories wherever you like.

If you definitely are only going to have 2 levels in the hierachy then the adjacency list model is fine but imho if you’re going to have more than 2 levels then the nested set model is the only way I would recommend to go.

5

Thanks r937,

What about the second query? How do I fetch the sub categories related to the main categories? Do I need another loop? Help is much appreciated.

6

there is no need for a second query, the first one will retrieve all categories and their subcategories

oops, i forgot the ORDER BY clause …

SELECT L1.cat_name AS cat
     , L2.cat_name AS sub_cat 
  FROM categories L1
LEFT OUTER 
  JOIN categories L2 
    ON L2.parent = L1.cat_id
 WHERE L1.parent = 0
ORDER
    BY cat
     , sub_cat
7

in my opinion your adjacency model is perfectly adequate for this application – the nested set model will only tear your hair out and cause you grief

your query contains a GROUP BY which is wrong

SELECT L1.cat_name AS cat
     , L2.cat_name AS sub_cat 
  FROM categories L1
LEFT OUTER 
  JOIN categories L2 
    ON L2.parent = L1.cat_id
 WHERE L1.parent = 0
8

imho you will be better off using The Nested Set Model instead of the Adjacency List Model you are currently using to store your categories hierachy in your database.

9

Thank you. Haven’t managed to apply the code to my menu though - here’s the set up I currently have:


<ul class="dropdown">

<?while ($row = mysql_fetch_array($result)){$cat_name=$row['cat'];$_GET['cat']=$cat_name; $sub_cat_name=$row['sub_cat'];$_GET['sub_cat']=$sub_cat_name;?> 
<li><a href="<?echo "browse.php?cat_name=$cat_name"?>"><?echo $row['cat'];?></a>


  <ul class="sub_menu">

<li><a href="<?echo "browse.php?cat_name=$sub_cat_name"?>"><?echo $row['sub_cat'];?></a></li>


  </ul>

   
</li>
 <?}?> 

</ul>

How do I need to adjust this?

10

Figured out how to adjust your code to my markup, thanks very much, everything works now!

11 

//I assume here you've already got the select stuff done, and the result is in a $result array

while($row = mysql_fetch_array($result)) {
  if(!isset($curcat)) {
    echo "<div class='menu'>".$row['cat']."</div><div class='submenu'>"; // The 'start' code goes here. I'm making something up cause i dont know what your code is.
    $curcat = $row['cat'];
  }
  if($row['cat'] != $curcat) { 
   echo "</div><br><div class='menu'>".$row['cat']."</div><div class='submenu'>"; //I'm guessing at your structure. Fill in here with 'end last entry, begin next'
  $curcat = $row['cat'];
  }
  echo $row['subcat']."<br />";
}
echo "</div>";
12

I’m only using 2 levels and not planning to have any more so I’ll stick with the adjacency model.

I’m not sure how to loop through the rows and print the new category when it changes. Does anyone know what the code for this would look like? Any suggestions?