Populate a dropdown menu with mysql data?

[GlenRogers]

Hi,

Is the any way I can populate a dropdown navigation menu with categories and subcategories i have in mysql??

This is what I have so far, with the categories and subcategories just hardcoded in the html.

<div class="menu">
                   <ul id="verticalmenu" class="glossymenu">
                      <li><a href="#" >Ribbon</a>
                         <ul>
                            <li><a href="#">7/8</a></li>
                            <li><a href="#">5/8</a></li>
                            <li><a href="#">3/8</a></li>
                         </ul>
                      </li>
                      <li><a href="#" >Flatbacks</a></li>
                      <li><a href="#" >Hair Bands/Clips</a></li>
                      <li><a href="#" >Baby Accessories</a></li>
                   </ul>
                </div>

But I want to allow the site owner to add more categories and subcategories as and when they need to.
Can anyone help?

Thanks for looking.

Glen.......

[abhi10kumar]

Its quite simple; U have to use join tables first; and then use foreach or while loops to print sub and main categories.

[dinhunzvi]

post your table structures.

[GlenRogers]

Thanks for the replies guys!

Ok my tables are set up like this

categories
id: int(10) ai primary
category: varchar(100)

subcategories
id: int(10) ai primary
subcat: varchar(100)
cat_id: int(10)

This is my form to add Categories to the categories table

<form class='adminform' action='catadd.php' method='post' enctype='multipart/form-data' name='cat_add_form' id='cat_add_form'>
                   <span class='formheading'>Add Category To The Site</span><br /><br />
                   <b>Category</b><input type=text name='acat' />
                   <br />
                   <br />
                   <input type='submit' id='submit' name='submit' value='Add Category' />
                   <input type='hidden' value='new' /><br />
                   <?php
                     include '../inc/connect.php';
                     // validate form fields
                     if (!empty($_REQUEST['acat'])){
                        $acat = $_REQUEST['acat'];
                     }
                     else{
                        $acat=NULL;
                        echo "Please enter a category to add";
                      }
                     //if evetrything is ok continue with form post inserting data to table
                     if ($acat){
                        if (isset($_POST['submit'])){ 
                           $r = mysql_query("INSERT INTO categories  VALUES ('', '$acat')");
                            echo "Category has been added to the website";
                        }
                     }
                 ?>
                </form>

And thiss is my form to add Subcategories to the subcategories table.

<form class='adminform' action='subcatadd.php' method='post' enctype='multipart/form-data' name='cat_add_form' id='cat_add_form'>
                <?php
                   include '../inc/connect.php';
                   //populate form dropdown box
                   $op = '';
                   $r = mysql_query("SELECT id, category FROM categories ORDER BY id");
                   if (mysql_num_rows($r)){
                      while ($d = mysql_fetch_assoc($r)){
                         $op .= "\n\t<option value='{$d['id']}'>{$d['category']}</option>";
                      }
                    }
                    ?>
                   <span class='formheading'>Add Subcategory</span><br /><br />
                   <b>Subcat</b><input type=text name='asubcat' /><br />
                   <b>Category</b>
                   <select name="subcat">
                      <?php echo $op;?>
                   </select>
                   <br />
                   <br />
                   <input type='submit' id='submit' name='submit' value='Add Category' />
                   <input type='hidden' value='new' /><br />
                   <?php 
                      include '../inc/connect.php';
                     // validate form fields
                     if (!empty($_REQUEST['asubcat'])){
                        $asubcat = $_REQUEST['asubcat'];
                     }
                     else{
                        $asubcat=NULL;
                        echo "Please enter a subcategory to add";
                      }
                      if($asubcat){
                      if (isset($_POST['submit'])){  
                         $subcat = intval($_POST['subcat']);
                         $r = mysql_query("INSERT INTO subcategories  VALUES ('', '$asubcat', '$subcat')");
                      }
                      }
                   ?>

                </form>

Thanks for looking

[dinhunzvi]

if it's for displaying the categories and sub categories this is what yur code is supposed to be.

$sql = "SELECT id, category FROM categories ORDER by category";
$result = mysql_query( $sql ) or die( 'Could not execute query: ' . mysql_error() );
while( $row = mysql_fetch_array( $result ) ) {
    ?>
        <li><a href="category.php?cat_id=<?php echo $row['id']; ?>"><?php echo $row['category']; ?></a>
    <?php
    $sql = "SELECT id, subcat FROM subcategories WHERE cat_id = {$row['id']}";
    $result = mysql_query( $sql ) or die( 'Could not execute query: ' . mysql_error() );
    while( $row = mysql_fetch_array( $result ) ) {
        ?>
            <li><a href="subcategory.php?cat_id=<?php echo $row['id']; ?>"><?php echo $row['subcat']; ?></a>
        <?php
    } 
}

hope that answers you

[GlenRogers]

Hey thanks for the reply.
I tried your code, it only give me one category and the subcategories for it.

How do I get it to show me all the categories? (I have 3 in my table).
Thanks...................

[diafol]

I made a blog entry for a similar problem some time ago:

http://diafol.blogspot.co.uk/2013/02/creating-linked-select-dropdowns-from.html

[IIM]

$sql = "SELECT id, category FROM categories ORDER by category";

this query will return all the categories and corresponding category id.How are you getting only one id?

  <li><a href="category.php?cat_id=<?php echo $row['id']; ?>"><?php echo $row['category']; ?></a></li> 

Close of li tag is missing

This line is used only to create anchor reference page,i.e.,opening the page correponding to that category.

 $sql = "SELECT id, subcat FROM subcategories WHERE cat_id = {$row['id']}";

this line will return the subcategories for a corresponding id.As it is in loop,so for each id ,all corresponding sub categories will be generated.

<li><a href="subcategory.php?cat_id=<?php echo $row['id']; ?>"><?php echo $row['subcat']; ?></a></li>

Close of li tag is missing.
This line is used only to create anchor reference page,i.e.,opening the page correponding to that sub-category.

Hope it will clear everything.

[GlenRogers]

Hey guys.
I cant get this working!
My menu is contains: Ribbons, Flatbacks, Hair bands, Baby Accessories.
When you hover on Ribbons a submenu appears with 3 links: 7/8, 5/8, 3/8.

Just like this when its hardcoded in html

<li><a href="#" >Ribbon</a> //category
                         <ul>
                            <li><a href="#">7/8</a></li>//subcatery
                            <li><a href="#">5/8</a></li>
                            <li><a href="#">3/8</a></li>
                         </ul>
                      </li>
                      <li><a href="#" >Flatbacks</a></li>
                      <li><a href="#" >Hair Bands/Clips</a></li>
                      <li><a href="#" >Baby Accessories</a></li>

My tables are like this:

categories:
id, int(10), ai, primary
category: varchar(100)

subcategories:
id: int(10), ai, primary
subcat: varchar(100)

Is this a hard task to perform? Should I just stick with the hardcoded html?
cat_id: int(10

[diafol]

You don't have to hard-code!
But you do need a category_id field in your subcategories table in order to link them.
Once you've done that, quite easy:

SELECT c.category, GROUP_CONCAT(s.subcat) AS sublist FROM category AS c INNER JOIN subcategory AS s ON c.id = s.category_id GROUP BY c.id ORDER BY c.category

That gives each record a comma delimited list of subcategories. You then use an explode() in your while loop to produce the inner <ul>:

$output = '<ul id="verticalmenu" class="glossymenu">';
while($data = ...){
    $output .= "<li><a href='#'>{$data['category']}</a></li>";
    $subcats = explode(",", $data['sublist']);
    $output .="<ul>";
    foreach($subcat as $s){
        $output .= "<li><a href='#'>$s</a></li>";
    }
    $output .= "</ul>";
}
$output .= '</ul>';

Then in the appropriate place:

echo $output;

Of course, this will output horrible html, so you can add \n and \t in relevant places to prettify it.

[GlenRogers]

Thanks for that.

I have used your code as is, but i get this error
Parse error: syntax error, unexpected '.' in C:\wamp\www\acraftyaffair\pages\home.php on line 32

Its on this line

while($data = ...){

Should I be replacing ... with something?

[GlenRogers]

Ok, strange things are happening now!

Now I only get one main menu item (category) and on page load all of it subcategories are visible underneath. When I hover over the main category button then come of it the subcategories dissapear! Then it works like it should, I hover over the main categorie and the subs appear.

This is what my code looks like

                <?php
                include '../inc/connect.php';
                $q="SELECT c.category, GROUP_CONCAT(s.subcategory) AS sublist FROM categories AS c INNER JOIN subcategories AS s ON c.id = s.cat_id GROUP BY c.id ORDER BY c.category";
                $result = mysql_query( $q ) or die( 'Could not execute query: ' . mysql_error() );
                $output = '<ul id="verticalmenu" class="glossymenu">';
               while($data = mysql_fetch_array( $result )){
               $output .= "<li><a href='#'>{$data['category']}</a></li>";
               $subcats = explode(",", $data['sublist']);
               $output .="<ul>";
               foreach($subcats as $s){
                  $output .= "<li><a href='#'>$s</a></li>";
               }
               $output .= "</ul>";
               }
               $output .= '</ul>';
               echo $output;
               ?>

[GlenRogers]

Ive just noticed something else, its only showing 1 category but when I hover on it it shows me every subcategory in my table, not just the ones for that category.

Can anyone see why?

Cheers

[diafol]

I'm just wondering about the closing </li> for the main category, shouldn't that be after the included subcategory list?

like this:

<ul id="verticalmenu" class="glossymenu">
    <li><a href='#'>cat1</a>
        <ul>
            <li><a href='#'>subcat1</a></li>
            <li><a href='#'>subcat2</a></li>
            <li><a href='#'>subcat3</a></li>
        </ul>
    </li>
    <li><a href='#'>cat2</a>
        <ul>
            <li><a href='#'>subcat4</a></li>
            <li><a href='#'>subcat5</a></li>
        </ul>
    </li>
</ul>

Been a while since I did nested navs.

If so, then :

$output = '<ul id="verticalmenu" class="glossymenu">';
while($data = ...){
    $output .= "<li><a href='#'>{$data['category']}</a>";
    $subcats = explode(",", $data['sublist']);
    $output .="<ul>";
    foreach($subcat as $s){
        $output .= "<li><a href='#'>$s</a></li>";
    }
    $output .= "</ul></li>";
}
$output .= '</ul>';

Glad you saw you had to replace ... with your result resource :)
Notice that the close </li> for the main category now appears on line 7.

[GlenRogers]

Thanks for the reply Al, but its still showing just one category and all the subcategories!

[diafol]

Strange . The MySQL works for me. The php is off the top of my head though. Here's my mysql output:

[GlenRogers]

I have 4 categories in my categories table but only 2 of them have correspong subcategories in the subcategories table, the other 2 dont need subcategories.

I ran that query in phpmyadmin. It showed me this table

category : sublist

cat1         subcat1,subcat2
cat2         subcat3,subcat4

My categories and subcategories tale are setup like this:

categories
id: int(10) ai primary nulll(no)
categories: varchar(100) null(no)

categories
id: int(10) ai primary null(no)
subcategories: varchar(100) null(no)
cat_id: int(10) null(no)

I need the webpage to display all the categories, the when you hover on one with subcategories it shows them. This is proving to be quite difficult for me to do!
Any ideas?

Cheers

[diafol]

I thought you needed cats with subcats. My SQL does an INNER JOIN, which only returns cats which have subcats. For what you need, change INNER JOIN to LEFT JOIN.

[GlenRogers]

This didnt work either! Its just the same, showing only 1 category with all the subcategories!

Its starting to look like I will just have to hardcode this and update it manually if the owner needs to add more cats or subcats!

[diafol]

So in phpMyAdmin with this:

SELECT c.category, GROUP_CONCAT(s.subcat) AS sublist FROM category AS c LEFT JOIN subcategory AS s ON c.id = s.category_id GROUP BY c.id ORDER BY c.category

you just get this:

category | sublist
cat1     | subcat1, subcat2, subcat3, subcat4, subcat5, ....

A single record with all the subcategories in the second field?

Can you please go to View Source in the browser and copy/paste the whole <ul> here? SO then we can see why you're getting strange output.

[GlenRogers]

No in phpmyadmi I'm gettin this

category  : sublist
cat1      :subcat1, subcat2
cat2      :subcat3, subcat4

But in the webpage, its only showing cat1 and all 4 subcats!

Copy/paste from view source is this

                <ul id='verticalmenu' class='glossymenu'><li><a href='#'>cat1</a><ul><li><a href='#'>subcat1</a></li><li><a href='#'>subcat2</a></li><li><a href='#'>subcat3</a></li><li><a href='#'>subcat4</a></li></ul></li></ul>        

[diafol]

THis code:

$q = "SELECT c.category, GROUP_CONCAT(s.subcat) AS sublist FROM category AS c LEFT JOIN subcategory AS s ON c.cat_id = s.cat_id GROUP BY c.cat_id ORDER BY c.category";
$r = mysql_query($q);
$output = '<ul id="verticalmenu" class="glossymenu">';
while($data = mysql_fetch_array($r)){
    $output .= "<li><a href='#'>{$data['category']}</a>";
    $subcats = explode(",", $data['sublist']);
    $output .="<ul>";
    foreach($subcats as $s){
        $output .= "<li><a href='#'>$s</a></li>";
    }
    $output .= "</ul></li>";
}
$output .= '</ul>';
echo $output;

Gives me the following (screenshot):

Note, that I have slightly different field names to you in the SQL.

//EDIT

However, I noticed that this flow gives empty categories an inner ul too, so modify to this:

while($data = mysql_fetch_array($r)){
    $output .= "<li><a href='#'>{$data['category']}</a>";
    if(!empty($data['sublist'])){
        $subcats = explode(",", $data['sublist']);
        $output .="<ul>";
        foreach($subcats as $s){
            $output .= "<li><a href='#'>$s</a></li>";
        }
        $output .= "</ul>";
    }
    $output .="</li>";
}
$output .= '</ul>';

[GlenRogers]

Thats it sorted now! I'm well chuffed!
Thanks so much Al. I really do appreciate it mate.................