Generate One Level Menu From MySQL Table

Question

I want to generate a menu with the values from the database.

+------+------+------------+--------------------+--------------+----------+
| ID   |Parent| Name       | Link               | Type         | Order    |
+------+------+------------+--------------------+--------------+----------+
|    1 | 0    | Some Name 2| /path1             | Top          | 1        |
|    2 | 1    | Some Name A| /path2             | Custom       | 1        |
|    3 | 0    | Some Name x| /path3             | Top          | 2        |
|    3 | 1    | Some Name e| /path4             | Custom       | 2        |
|    4 | 1    | Some Name 3| /path5             | Custom       | 3        |
|    5 | 6    | Some Name t| /path6             | Custom       | 1        |
|    6 | 0    | Some Name i| /path7             | Top          | 3        |
+------+------+------------+--------------------+--------------+----------+

The Menu should look like:

<a href="/path1">Some Name 2</a>
- <a href="/path4">Some Name e</a>
- <a href="/path5">Some Name 3</a>

<a href="/path3">Some Name x</a>

<a href="/path7">Some Name i</a>
- <a href="/path6">Some Name t</a>

ID - is unique ID.

Parent - 0 means no parent, any other value represents the ID (above) of the parent item in the menu.

Name - The text for the respective item in the menu.

Link - The link of the respective item in the menu.

Type - Top means top level item in the menu, the rest are labeled as custom.

Order - For top items, sets the order how they appear in the menu 1 being first > bigger numbers. For sub-items sets the order how they appear in the respective list of sub items.

The menu has only one level: Top > Sub

What I need to do is find a way to generate a menu from these values in php & mysqli.

The problem is I have no idea how I should proceed.. I tried select concat for ID and Parent but I lack some basic php skills (total noob).

How should I proceed? Any idea is welcomed, thanks!

It might be worth reading [this question](http://stackoverflow.com/questions/2871861/how-to-build-unlimited-level-of-menu-through-php-and-mysql) as the answers give the solution to multi-level menus and discuss the topic in some detail. — nickhar, Jun 27 '13 at 23:18

score 2 · Answer 1 · answered Jun 27 '13 at 23:24

Some quick solution that comes in mind right now (not tested) :

function getCategories($parentID = 0, $level = 0) {
    $categories = array();

    $query = 'SELECT * FROM `table` WHERE `Parent` = '.(int)$parentID.' ORDER BY `Order` ASC';
    $dbCats = runQuery($query); // you have to implement this

    foreach ($dbCats as $cat) {
        $categories[] = str_repeat('-', $level) . '<a href="'.$cat->Link.'">'.$cat->Name.'</a>' . "\n";
        $categories += getCategories($cat->ID, ++$level);
    }

    return $categories;
}
$categories = getCategories();
echo implode('', $categories);

However, you should look into Open Cart for example and see how it extracts categories.

score 1 · Accepted Answer · answered Jun 28 '13 at 01:17

First select all top menu in the first query and then create a loop the will retrieve it all..Inside the first loop create another query that will retrieve all the child menu of the selected menu, if there is any..That's why we also have the if statement..Inside the if statement create the loop to retrieve all the child menu...Here's the code:

    $sql = "SELECT * FROM `table` WHERE `Parent` = '0' ORDER BY `Order` ASC";
    $result = mysql_query($sql) or die ("Error: Query Failed! " .mysql_error());
    $output = "<ul>";
    while ($rs = mysql_fetch_array($result)) {
       $output .= "<li><a href='".$rs['Link']."'>".$rs['Name']."</a></li>";
       $sql2 = "SELECT * FROM `table` WHERE `Parent` = '".$rs['ID']."' ORDER BY `Order` ASC";
       $result2 = mysql_query($sql2) or die ("Error: Query Failed! " .mysql_error());
       if (mysql_num_rows($result2) != 0) {
          $output .= "<ul>";
          while ($rs2 = mysql_fetch_array($result2)){
             $output .= "<li><a href='".$rs2['Link']."'>".$rs2['Name']."</a></li>"
          }
          $output .= "</ul>";
       }
    }
    $output .="</ul>";
    echo $output;

Haven't tested it but this should do it.

Thank you everyone, I could have never do this alone, I need to learn from examples to understand.. Thank you again.. — Adrian M., Jun 28 '13 at 07:44

Generate One Level Menu From MySQL Table

2 Answers2