Creating dynamic sidebar menu with Mysql and AdminLTE Bootstrap Template

To create dynamic and exciting sidebar menu, you need a database for the storage and website templates that facilitate in making the layout (In this article, I use adminLTE bootstrap, read my other tutorial here or check adminLTE official website for more information).

how to create dynamic sidebar menu with mysql and adminLTE bootstrap

In this tutorial you will find information about creating and managing dynamic sidebar menu with mysql and adminLTE Bootstrap.

 Creating Table menu with myqsl for dynamic sidebar menu 

Add new table in your database with name dynamic_menu , copy this table structure below to you

Column Name Data type Length Primary Key Not null auto increment
id tinyint 3 yes yes yes
parent_id tinyint 3 yes
title varchar 100 yes
url varchar 100 yes
menu_order tinyint 3 yes
status tinyint 1
level tinyint 1
icon varchar 100
description varchar 150

Or run this mysql script with include example menu


CREATE TABLE `dynamic_menu` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `title` varchar(100) NOT NULL DEFAULT '',
  `url` varchar(100) NOT NULL DEFAULT '',
  `menu_order` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `status` tinyint(1) DEFAULT '1',
  `level` tinyint(1) DEFAULT NULL,
  `icon` varchar(100) DEFAULT NULL,
  `description` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
)
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (1,0,'Home','',1,1,0,'fa fa-dashboard',NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (3,0,'Setup','#',5,1,2,'fa fa-sitemap',NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (5,0,'Administrator','#',6,1,1,'fa fa-shield',NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (6,5,'User','/form/fdatauser.php',1,1,0,NULL,NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (2,3,'Setup Kategori','/form/fkategori.php',2,1,0,NULL,NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (4,0,'Invent','/form/fdataasset.php',2,1,0,'fa fa-plus-square',NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (35,5,'Office Setup','/form/fperusahaan.php',3,1,2,'',NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (33,0,'Report','/form/flaporan.php',4,1,0,'fa fa-bar-chart-o',NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (36,31,'Transaction History','/form/fdatatransaksi.php',7,1,0,NULL,NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (34,3,'Brand Setup','/form/fmerk.php',4,0,0,NULL,NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (41,31,'Refund Transaction','/form/fpengembalian.php',5,1,0,NULL,NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (38,3,'Category Setup','/form/fgolongan.php',1,1,0,NULL,NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (39,31,'Sales Transaction','/form/fpenjualan.php',3,1,0,NULL,NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (40,31,'Purchase Transaction','/form/fpeminjaman.php',4,1,0,NULL,NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (42,3,'Type Setup','/form/fjenis.php',3,0,0,NULL,NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (43,31,'Others Transaction','/form/flainlain.php',6,1,0,NULL,NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (44,0,'Process','/form/fproses.php',3,1,1,'fa fa-spinner',NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (45,4,'Invent Setup','/form/fdataasset.php',1,1,1,NULL,NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (46,4,'Bopi Setup','/form/fbopi.php',2,1,0,NULL,NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (47,5,'Log user','/form/floguser.php',2,1,1,NULL,NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (48,5,'Admin tool','/form/adminform.php',4,1,2,NULL,NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (49,33,'Lastmonth Report','/form/flaporanlama.php',1,1,0,NULL,NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (50,4,'Taka Setup','/form/ftaka.php',3,1,1,NULL,NULL);
insert  into `dynamic_menu`(`id`,`parent_id`,`title`,`url`,`menu_order`,`status`,`level`,`icon`,`description`) values (51,4,'Invent Data','/application/data-invent/',1,1,0,NULL,NULL);

Explanation

Column Name Description
id Id menu , auto increment
parent_id Every menu has parent_id
title Menu title
url link url to your page
menu_order order menu
status 1 or 0 value for active or deactive
level User level
icon bootstrap menu icon
description Title description

 Creating PDO Class to connect php and mysql 

Create php file as pdo_db.php

<?php
class core_db extends PDO{
    private $engine;
    private $host;
    private $database;
    private $user;
    private $pass;

    private $result;    

    public function __construct()
        {
        $this->engine   = 'mysql';
        $this->host     = 'localhost';
        $this->database = 'mydatabase';
        $this->user     = 'root';
        $this->pass     = '';

        $dns = $this->engine.':dbname='.$this->database.";host=".$this->host;
        parent::__construct( $dns, $this->user, $this->pass );
    }   

        public function getResult()
        {
       	 return $this->result;
   		 }
}

?>

 Creating dynamic sidebar menu in adminLTE Bootstrap Template 

In adminLTE code, we will find code below to create sidebar menu

<aside class="left-side sidebar-offcanvas">
..............
</aside>

Change these code with my code below to creating dynamic sidebar menu

<aside class="left-side sidebar-offcanvas">
                <section class="sidebar">
                    <div class="user-panel">
                        <div class="pull-left info">
	                        <p>Your company name</p>
                            <p>Welcome , </p>
                            <p>[Usename]</p>
                            <a href="#"><i class="fa fa-circle text-success"></i> Online</a>
                        </div>
                    </div>
                    <form action="#" method="get" class="sidebar-form">
                       <div class="footer">
                          <button type="submit" class="btn bg-olive btn-block">Logout</button>
                        </div>
                    </form>

<?php
	require_once('pdo_db.php');
	$database= new core_db;
	$hasil=$database->query('SELECT id as menu_item_id, parent_id as menu_parent_id, title as menu_item_name,concat("/foldername",url)as url,menu_order,icon FROM menu ORDER BY menu_order');
    $refs = array();
    $list = array();
    while($data = $hasil->fetch(PDO::FETCH_ASSOC))
    {
        $thisref = &$refs[ $data['menu_item_id'] ];
        $thisref['menu_parent_id'] = $data['menu_parent_id'];
        $thisref['menu_item_name'] = $data['menu_item_name'];
		$thisref['url'] = $data['url'];
		$thisref['icon'] = $data['icon'];
        if ($data['menu_parent_id'] == 0)
        {
            $list[ $data['menu_item_id'] ] = &$thisref;
        }
        else
        {
            $refs[ $data['menu_parent_id'] ]['children'][ $data['menu_item_id'] ] = &$thisref;
        }
    }
    function create_list( $arr ,$urutan)
    {
		if($urutan==0){
      		 $html = "\n<ul class='sidebar-menu'>\n";
		}else
		{
			 $html = "\n<ul class='treeview-menu'>\n";
		}
        foreach ($arr as $key=>$v)
        {
            if (array_key_exists('children', $v))
            {
                $html .= "<li class='treeview'>\n";
				$html .= '<a href="#">
                                <i class="'.$v['icon'].'"></i>
                                <span>'.$v['menu_item_name'].'</span>
                                <i class="fa fa-angle-left pull-right"></i>
                            </a>';

                $html .= create_list($v['children'],1);
                $html .= "</li>\n";
            }
            else{
					$html .= '<li><a href="'.$v['url'].'">';
					if($urutan==0)
					{
						$html .=	'<i class="'.$v['icon'].'"></i>';
					}
					if($urutan==1)
					{
						$html .=	'<i class="fa fa-angle-double-right"></i>';
					}
					$html .= $v['menu_item_name']."</a></li>\n";}
        }
        $html .= "</ul>\n";
        return $html;
    }
    echo create_list( $list,0 );
?>
                </section>
            </aside>

You can modify my code as you wish, if any question about how to create dynamic sidebar menu in adminLTE bootstrap, please leave comment 🙂

The following two tabs change content below.
This site is a personal Blog of Sigit Prasetya Nugroho, a Desktop developer and freelance web developer working in PHP, MySQL, WordPress.

15 Comments

 Add your comment
  1. Eneko Anasagasti

    Thank you for sharing this code!
    There is a small typo in the query ….FROM menu ORDER BY menu_order’);
    should be ….FROM dynamic_menu ORDER BY menu_order’); instead.

  2. i want to know how to set user permission view for certain module

  3. Terima kasih tutorialnya, bermanfaat buat saya.

    mungkin dikoreksi kali querynya kurang tepat ya harusnya diganti tabelnya jangan menu, khawatir bagi yang baru belajar kebingungan kenapa tidak bisa running.

  4. many thanks for your work
    I found that it doesn’t add active class to the submenu after it was clicked.
    thanks again

  5. Mohon petunjuknya gan.
    Itu kan sub menu nya punya class ‘treeview-menu’ yah
    Kalo di template yang sub menu class nya beda-beda seperti class ‘second-level’ , ‘third-level’, ‘fourth-level’ , gimana yah caranya. saya coba utak-utik kok stuck. yang harusnya di level 1, masuk ke level 2, karena dia punya sub menu lagi. mohon arahannya, thank you

  6. Mohon bantuannya, bagaimana membuat menu dinamis seperti diatas bila menggunakan mysqli?

    Menurut agan, untuk menu bertingkat/multilevel baiknya menggunakan 1 tabel saja atau pakai 2 tabel (tbl menu dan tbl submenu).. Terima kasih sebelumnya.

Leave a Comment

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.

%d bloggers like this: