Create a list from database

[Meridius]

Hello,

 

I am working on getting a CSS menu from a database. The CSS menu i want to use makes use of <ul> and <li> tags. The basic setup for the menu is as followed:

<div id="cssmenu">
  <ul>
    
    <li>CustomerX
      <ul>
        <li id="li1">Main
          <ul>
            <li></li>
            <li></li>
          </ul>
        </li>
      </ul>
    </li>

   more menu items

  </ul>
</div>

 

Most things speak for themselves i think. The <li id=""> can be duplicated to get multiple levels that can expand and collapse. The id can also be opened through a js script.

 

Static this menu works fine, but i want to have the menu in a MySQL database for easy management since the menu content is subject to change.

I made 3 relational tables in the database for this:

- Customers (ID, Custname)

- Menuoptions (ID, Option)

- Cust_options (cust_ID, option_id)

 

After running the SELECT query i have a sorted list with customername/menuoption. Each option has a customer name attached.

 

My PHP/MySQL is quite a bit rusty so I am struggling to get it sorted. Here is what i have so far:

<?php
include 'dbconnect.php';

/* query */
$query = "SELECT c.Custname, mo.Option
		FROM customers c, menuoption mo, cust_option co
		WHERE co.cust_ID = c.ID
		AND co.option_ID = mo.ID
		ORDER BY Custname";
$result = $mysqli->query($query);

/* define variable */
$custname = "";
$i = 1;

print '<div id="cssmenu">';
print '<ul>';	

/* loop through array */
while ($array = mysqli_fetch_array($result)){
	if ($array['custname'] <> $custname){
		print "<li>" . $array['custname'];
		print "<ul>";
		print "<li id=li". $i .">Main";
		print "<ul>";
		$i++;
		}
	
	print "<li>" . $array['Option'] . "</li>";
	
	if ($array['custname'] <> $custname){
		print "</ul></li></ul></li>";
		}
			
	$custname = $array['custname'];
}

/* free result set */
$result->free();

/* close connection */
$mysqli->close();

print "</ul>";
print "</div>";
?>

(could be that i missed a closing tag somewhere in the above code, i have been experimenting)

 

The result from this is that i get the following:

Customer X
  Main
    Menuitem1
Menuitem2
Menuitem3

While it should look like:

Customer X
  Main
    Menuitem1
    Menuitem2
    Menuitem3

As soon as there is a 2nd, 3rd menu, etc option they are seen as a new menu item.

 

Does anyone have an idea what i am doing wrong? Any thoughts would be greatly appreciated.

 

Kind regards,

 

Erik

 

[Jessica]

What's the actual output of the query? Do menuitem2 and 3 have the right customer name?

[Meridius]

Thank you for the reply.

 

The query results are as you would expect. 1 row with the customer name and a 2nd row with the correct option.

 

Customer X  |  Menuitem 1

Customer X  |  Menuitem 2

Customer Y  |  Menuitem 1

Customer Y  |  Menuitem 2

Customer Y  |  Menuitem 3

etc.

Edited March 21, 2013 by Meridius

[Meridius]

After a bit of experimenting and writing stuff on paper, I think i solved the problem by moving a block of code up and adding another check.

while ($array = mysqli_fetch_array($result)){
        if ($custname != ""){
            if ($array['custname'] <> $custname){
		print "</ul></li></ul></li>";
		}
            }

	if ($array['custname'] <> $custname){
		print "<li>" . $array['custname'];
		print "<ul>";
		print "<li id=li". $i .">Main";
		print "<ul>";
		$i++;
		}
	
	print "<li>" . $array['Option'] . "</li>";
			
	$custname = $array['custname'];
}

 

This seems to do the trick now but I don't know if it is the most elegant solution.

 

If anyone has any idea's how to make it more "professional" please do not hesitate to give suggestions.

 

Jessica, I also took some things from the tutorial you wrote about the lists for this and for the rest of my code. In short: you are awesome!