PHP : Create menu from SQL Server database

Question

How can I create a dynamic menu created in a database?

A sample of the menu table :

ID  NAME        URL         IDPARENT
----------------------------------
1   Accueil     #Accueil    0         
2   Parcs       #Parcs      0         
3   Allemagne   #Allemagne  2         
4   Berlin      #Berlin     3          
5   France      #France     2           
6   Contact     #Contact    0

The result should be :

<ul> 
 <li>Accueil</li>
 <li>Parcs</li>
    <ul>
     <li>Allemagne</li>
        <ul>
          <li>Berlin</li>
        </ul>  
     <li>France</li>
   </ul>
 <li>Contact</li>
</ul>

SOLVED : My code :

        <?php
        //connection to the database
        $dbhandle = mssql_connect('*****', '*****', '*****')
          or die("Couldn't connect to Server");     
        //select a database to work with
         $selected = mssql_select_db("*****", $dbhandle)
          or die("Couldn't open database"); 
          //declare the SQL statement that will query the database
        $query = "SELECT * FROM CATEGORIES ";
        //execute the SQL query and return records
        $result = mssql_query($query);
        //display the results 
        while($row = mssql_fetch_array($result))   
                {
                // Assign by reference
                $thisref = &$refs[ $row['ID'] ];
                // add the the menu parent
                $thisref['IDCategoriePere'] = $row['IDCategoriePere'];
                $thisref['NOM'] = $row['NOM'];
                $thisref['URL'] = $row['URL'];
                // if there is no parent id
                if ($row['IDCategoriePere'] == 0)
                {
                    $list[ $row['ID'] ] = &$thisref;
                }
                else
                {
                    $refs[ $row['IDCategoriePere'] ]['children'][ $row['ID'] ] = &$thisref;
                }
        } 
        function create_menu( $arr )
            {
                $html = "\n<ul>\n";
                foreach ($arr as $key=>$val) 
                {
                    $html .= '<li><a href="'.$val['URL'].'">'.$val['NOM']."</a></li>\n";
                    if (array_key_exists('children', $val))
                    {
                        $html .= create_menu($val['children']);
                    }
                }
                $html .= "</ul>\n";
                return $html;
            }
            echo create_menu( $list );
            //close the connection
         mssql_close($dbhandle);
         ?>

It works fine ! but when i tried to put css (http://cssmenumaker.com/menu/flat-jquery-responsive-menu), the dropdown doesn't show :(

Result with and witout css :

  <style>
/* CSS Document */
@import url(http://fonts.googleapis.com/css?family=Open+Sans);
@import url(http://fonts.googleapis.com/css?family=Bree+Serif);
#container {
margin: 0 auto;
}
nav {
margin: 50px 0;
background-color: #E64A19;
}
nav ul {
padding: 0;
margin: 0;
list-style: none;
position: relative;
}
nav ul li {
display:inline-block;
background-color: #E64A19;
}
nav a {
display:block;
padding:0 10px; 
color:#FFF;
font-size:20px;
line-height: 60px;
text-decoration:none;
}
nav a:hover { 
background-color: #000000; 
}
/* Hide Dropdowns by Default */
nav ul ul {
display: none;
position: absolute; 
top: 60px; /* the height of the main nav */
}   
/* Display Dropdowns on Hover */
nav ul li:hover > ul {
display:inherit;
}   
/* Fisrt Tier Dropdown */
nav ul ul li {
width:170px;
float:none;
display:list-item;
position: relative;
}
/* Second, Third and more Tiers */
nav ul ul ul li {
position: relative;
top:-60px; 
left:170px;
}
/* Change this in order to change the Dropdown symbol */
li > a:after { content:  ' +'; }
li > a:only-child:after { content: ''; }
</style>

<!-- WITH PHP -->
<div id="container">
<nav>
<?php
        //connection to the database
        $dbhandle = mssql_connect('*****', '*****', '*****')
          or die("Couldn't connect to Server");     
        //select a database to work with
         $selected = mssql_select_db("*****", $dbhandle)
          or die("Couldn't open database"); 
          //declare the SQL statement that will query the database
        $query = "SELECT * FROM CATEGORIES ";
        //execute the SQL query and return records
        $result = mssql_query($query);
        //display the results 
        while($row = mssql_fetch_array($result))   
                {
                // Assign by reference
                $thisref = &$refs[ $row['ID'] ];
                // add the the menu parent
                $thisref['IDCategoriePere'] = $row['IDCategoriePere'];
                $thisref['NOM'] = $row['NOM'];
                $thisref['URL'] = $row['URL'];
                // if there is no parent id
                if ($row['IDCategoriePere'] == 0)
                {
                    $list[ $row['ID'] ] = &$thisref;
                }
                else
                {
                    $refs[ $row['IDCategoriePere'] ]['children'][ $row['ID'] ] = &$thisref;
                }
        } 
        function create_menu( $arr )
            {
                $html = "\n<ul>\n";
                foreach ($arr as $key=>$val) 
                {
                    $html .= '<li><a href="'.$val['URL'].'">'.$val['NOM']."</a></li>\n";
                    if (array_key_exists('children', $val))
                    {
                        $html .= create_menu($val['children']);
                    }
                }
                $html .= "</ul>\n";
                return $html;
            }
            echo create_menu( $list );
            //close the connection
         mssql_close($dbhandle);
         ?> 
         </nav>
</div>

<!-- WITHOUT PHP -->
<div id="container">
<nav>
    <ul>
        <li><a href="#">ACCUEIL</a></li>

        <li><a href="#">PARCS</a>
        <!-- First Tier Drop Down -->
        <ul>
            <li><a href="#">ALLEMAGNE</a>
            <!-- Second Tier Drop Down -->
            <ul>
                <li><a href="#">BERLIN</a></li>
            </ul>
            </li>
            <li><a href="#">FRANCE</a>
            </li>
        </ul>
        </li>
        <li><a href="#">CONTACT</a></li>
    </ul>
 </nav>
</div>

please update your question and tag it with the language you're using to build the menu. — devlin carnate
– devlin carnate, Commented Mar 31, 2016 at 16:30
You should first separate countries from cities as when you curently query your table is gonna be messy with hierarchy and exclusion to properly extract the array to create your menu. — cpugourou
– cpugourou, Commented Mar 31, 2016 at 17:07
you should also show us your code so far. so, for example, we need to see your how you get your result set from sql to php, and what you've done so far in order to get those results into that menu structure. are you using an unordered list in HTML, or? — devlin carnate
– devlin carnate, Commented Mar 31, 2016 at 17:07
Yes, an unordered menu. Code updated in the question. But thats show just one list of all items — Senneville
– Senneville, Commented Mar 31, 2016 at 17:16

Pintus · Accepted Answer · 2016-04-02 09:54:01Z

1

Please give for a detailed answer all informations like your database type and also the query or all of your code. Also the column name NAME is a reserved SQL Keyword, so normally it is not a good idea but mssql is mostly not ANSI, so it is maybe not interesting :)

If you want a multidimensional menu, then you cannot only print out the table. You have first to order the data (pass all childs to parent) and then you can create your menu. For that you are using normally with recursive functions or methods like here the create_menu function.

    <?php

    $serverName = "serverName\instanceName";
    $connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
    // connect to sql server
    $conn = sqlsrv_connect( $serverName, $connectionInfo );
    if( $conn === false ) {
        die( print_r( sqlsrv_errors(), true));
    }

    // create an array to hold the references
    $refs = array();

    // create and array to hold the list
    $list = array();

    $tsql = "SELECT ID, IDPARENT, NAME, URL FROM menu_items ORDER BY NAME;"

    $stmt = sqlsrv_query( $conn, $tsql);
    if( $stmt === false) {
        die( print_r( sqlsrv_errors(), true) );
    }
    while($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC))
    {
        // Assign by reference
        $thisref = &$refs[ $row['ID'] ];

        // add the the menu parent
        $thisref['IDPARENT'] = $row['IDPARENT'];
        $thisref['NAME'] = $row['NAME'];
        $thisref['URL'] = $row['URL'];

        // if there is no parent id
        if ($row['IDPARENT'] == 0)
        {
            $list[ $row['ID'] ] = &$thisref;
        }
        else
        {
            $refs[ $row['IDPARENT'] ]['children'][ $row['ID'] ] = &$thisref;
        }
    }
    mssql_close($conn);

    /**
    *
    * Create a HTML menu from an array
    *
    * @param    array    $arr
    * @param    string    $list_type
    * @return    string 
    *
    */
    function create_menu( $arr )
    {
        $html = "\n<ul>\n";
        foreach ($arr as $key=>$val) 
        {
            $html .= '<li><a href="'.$val['URL'].'">'.$val['NAME']."</a>";
            if (array_key_exists('children', $val))
            {
                $html .= create_menu($val['children']);
            }
            $html .= "</li>\n";
        }
        $html .= "</ul>\n";
        return $html;
    }

    echo create_menu( $list );
    ?>

edited Apr 2, 2016 at 9:54

answered Mar 31, 2016 at 18:51

Pintus

675 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Senneville Over a year ago

Thank you ! It worked ! (a small final issue.. cf question)

Pintus Over a year ago

Nice to hear. That would be normally a new question and without to see the code, how you call the menu it is not easy to say why it is not functional.

Pintus Over a year ago

There was a small bug within the create_menu function. I updated now the code within the foreach. Also please close the db connection directly after you do not need anymore.

Collectives™ on Stack Overflow

PHP : Create menu from SQL Server database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related