CODEWITHSUNDEEP
phpmysqlrecursiondrop-down-menuphpmyadmin
Rick
Rick

Reputation: 39

Speed up PHP recursive function

I am trying to create multi-level dynamic PHP menu by fetching values from database using recursion. But the page speed is getting slower and slower as the no. of records in database increases. Currently there are only around 15 records in DB.

Here is the code:

<div id="nav">
<?php
$menu_html='';
function render_menu($parent_id){
$con=mysqli_connect("localhost","root","","test");
    global $menu_html;
    $result=mysqli_query($con, "select * from menu where parent_id=$parent_id");
    if(mysqli_num_rows($result)==0) return;

    if($parent_id==0)
        $menu_html .= "<ul>";
    else
        $menu_html .= "<ul>";

    while($row=mysqli_fetch_array($result)){
        $menu_html .= "<li><a href=\"{$row['link']}\">{$row['label']}</a>";
        render_menu($row['id']);
        $menu_html .= "</li>";
    }
    $menu_html .= "</ul>";
    return $menu_html;
}
echo render_menu(0);
?>
</ul>
</div>

This is how my db looks like:

enter image description here

enter image description here

Please help and let me know how I can optimize the page speed.

Thanks

Upvotes: 1

Views: 1333

Answers (4)

Rick
Rick

Reputation: 39

Thanks to all for your guidance and showing me the correct path to proceed. I followed this link and its done.

http://wizardinternetsolutions.com/articles/web-programming/dynamic-multilevel-css-menu-php-mysql

Thanks

Upvotes: 0

Jevgenijs Vaikulis
Jevgenijs Vaikulis

Reputation: 686

I would like to recommend you to learn nested set tree: Managing Hierarchical Data in MySQL, that enables only 1 select/query for building your menu, without recursive function calls.

Upvotes: 2

bcmcfc
bcmcfc

Reputation: 26805

You're doing multiple database queries which can be avoided.

Fetch everything in one go and loop over the rows to build the menu. You'll have everything you need to build a nested array using the parent IDs.

If it's still slow after that you can look into caching the database result, or even the entire rendered menu.

You probably are better off caching it in a file or to memory and specifically removing the cached item when you edit a menu item or add a new one to the database, so that the cache can be updated. Then you need not worry too much about how long you're caching for, you can just cache it indefinitely.

Upvotes: 4

ehwas
ehwas

Reputation: 248

Try this:

    <div id="nav">
        <?php
            $menu_html='';
            function render_menu()
            {
                global $menu_html;
                $con=mysqli_connect("localhost","root","","test");
                $result=mysqli_query($con, "SELECT * FROM menu ORDER BY parent_id ASC;");
                $menu = array();
                while($row=mysqli_fetch_array($result))
                {
                    if($row['parent_id'] == 0)
                    {
                        $menu[$row['id']] = array
                        (
                            'label' => $row['label'], 
                            'link' => $row['link'], 
                            'children' => array()
                        );
                    }
                    else
                    {
                        $menu[$row['parent_id']]['children'][] = array
                        (
                            'label' => $row['label'], 
                            'link' => $row['link']
                        );
                    }
                }
                $menu_html = '<ul>';
                foreach($menu as $menu_item)
                {
                    $menu_html .= '<li>';
                    $menu_html .= '<a href="'.$menu_item['link'].'">'.$menu_item['label'].'</a>';
                    if((bool)$menu_item['children'])
                    {
                        $menu_html .= '<ul>';
                            foreach($menu_item['children'] as $child)
                            {
                                $menu_html .= '<li>';
                                    $menu_html .= '<a href="'.$child['link'].'">'.$child['label'].'</a>';
                                $menu_html .= '</li>';
                            }
                        $menu_html .= '</ul>';
                    }
                    $menu_html .= '</li>';
                }
                $menu_html .= '</ul>';
                return $menu_html;
            }
            echo render_menu();
        ?>
    </div>

Upvotes: 0

Related Questions