Tutorial Create Dynamic Sidebar Menu on MySQL, Node.js, Express, AdminLTE For Beginner

How to easily create dynamic sidebar menu AdminLTE using node, express and Mysql. I have made several articles about how to create dynamic sidebar menu on adminLTE with the permissions of each user on PHP and MYSQL. For the implementation of the node.js, express.js and jade template engine is slightly different. The menu that can be accessed by each user will be stored in a session.

So that you can follow this tutorial very well, you should read my tutorial how to create a CRUD using node.js express.js, MySQL, and adminLTE from the previous article. Please follow this link

In addition, if you want to read the article how to create dynamic sidebar menu in adminLTE and PHP, please follow this link

Okay, let’s start the following tutorials:

1. Make sure you have created a MySQL database as node.js (read my previous articles). and sure to make 4 of the table named customer, r_menu, r_menu_sub and user as shown below:

Create Database Node Js Tutorial Create Dynamic Sidebar Menu On MySQL, Node.js, Express, AdminLTE For Beginner

2. Copy the following code to add new data to the table r_menu, r_menu_sub, and user table

r_menu table

DROP TABLE IF EXISTS 'r_menu';

CREATE TABLE 'r_menu' (
  'id_menu' int(2) NOT NULL AUTO_INCREMENT,
  'nama_menu' varchar(100) DEFAULT NULL,
  'urutan' int(2) DEFAULT NULL,
  'icon' varchar(30) DEFAULT NULL,
  PRIMARY KEY ('id_menu')
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

insert  into 'r_menu'('id_menu','nama_menu','urutan','icon') values (1,'Master',1,'<i class=\"fa fa-bell\"></i> ');
insert  into 'r_menu'('id_menu','nama_menu','urutan','icon') values (2,'Customer',2,'<i class=\"fa fa-bomb\"></i> ');
insert  into 'r_menu'('id_menu','nama_menu','urutan','icon') values (3,'Utility',3,'<i class=\"fa fa-cubes\"></i> ');

r_menu_sub table

DROP TABLE IF EXISTS 'r_menu_sub';

CREATE TABLE 'r_menu_sub' (
  'id_sub_menu' int(3) NOT NULL AUTO_INCREMENT,
  'nama_sub_menu' varchar(100) DEFAULT NULL,
  'id_menu' int(2) DEFAULT NULL,
  'urutan_sub_menu' int(2) DEFAULT NULL,
  'hak_akses' varchar(30) DEFAULT '4',
  'url' varchar(150) DEFAULT '#',
  'content_before' varchar(200) DEFAULT NULL,
  'content_after' varchar(200) DEFAULT NULL,
  'icon' varchar(150) DEFAULT NULL,
  'title' varchar(200) DEFAULT NULL,
  'target' enum('_self','_blank') DEFAULT '_self',
  PRIMARY KEY ('id_sub_menu'),
  KEY 'urutan_sub_menu_index' ('id_menu','urutan_sub_menu')
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;


insert  into 'r_menu_sub'('id_sub_menu','nama_sub_menu','id_menu','urutan_sub_menu','hak_akses','url','content_before','content_after','icon','title','target') values (1,'Product',1,3,'2','#','','','<i class=\"fa fa-arrow-right\"></i>','Title please','_self');
insert  into 'r_menu_sub'('id_sub_menu','nama_sub_menu','id_menu','urutan_sub_menu','hak_akses','url','content_before','content_after','icon','title','target') values (2,'Brand',1,4,'2','#','','','<i class=\"fa fa-arrow-right\"></i>','Title please','_self');
insert  into 'r_menu_sub'('id_sub_menu','nama_sub_menu','id_menu','urutan_sub_menu','hak_akses','url','content_before','content_after','icon','title','target') values (3,'Customer',2,1,'2','#','','','<i class=\"fa fa-arrow-right\"></i>','Title please','_self');
insert  into 'r_menu_sub'('id_sub_menu','nama_sub_menu','id_menu','urutan_sub_menu','hak_akses','url','content_before','content_after','icon','title','target') values (4,'Sales',2,2,'2','#','','','<i class=\"fa fa-arrow-right\"></i>','Title please','_self');
insert  into 'r_menu_sub'('id_sub_menu','nama_sub_menu','id_menu','urutan_sub_menu','hak_akses','url','content_before','content_after','icon','title','target') values (5,'Backup',3,1,'4','#','','','<i class=\"fa fa-arrow-right\"></i>','Title please','_self');
insert  into 'r_menu_sub'('id_sub_menu','nama_sub_menu','id_menu','urutan_sub_menu','hak_akses','url','content_before','content_after','icon','title','target') values (6,'Reference',3,2,'4','#','','','<i class=\"fa fa-arrow-right\"></i>','Title please','_self');
insert  into 'r_menu_sub'('id_sub_menu','nama_sub_menu','id_menu','urutan_sub_menu','hak_akses','url','content_before','content_after','icon','title','target') values (7,'bla bla bla',3,3,'4','#','','','<i class=\"fa fa-arrow-right\"></i>','Title please','_self');

user table

DROP TABLE IF EXISTS 'user';

CREATE TABLE 'user' (
  'the_email' varchar(200) DEFAULT NULL,
  'the_password' varchar(100) DEFAULT NULL,
  'user_menu' varchar(254) DEFAULT NULL
) 

insert  into 'user'('the_email','the_password','user_menu') values ('test@test.com','e10adc3949ba59abbe56e057f20f883e','1,4,6');

3. Please download my previous project here, so you no longer need to create from scratch (Since this article is a continuation of the previous article).

4. Make sure your package.json as mine in the picture below so that the application can run properly.

Package.json Tutorial Create Dynamic Sidebar Menu On MySQL, Node.js, Express, AdminLTE For Beginner

5. Create a new module to check the value of the array (such as in_array function in PHP)

module.exports = {
	in_array : function(needle) {
		var findNaN = needle !== needle;
		var indexOf;

		if(!findNaN && typeof Array.prototype.indexOf === 'function') {
			indexOf = Array.prototype.indexOf;
		} else {
			indexOf = function(needle) {
				var i = -1, index = -1;

				for(i = 0; i < this.length; i++) { var item = this[i]; if((findNaN && item !== item) || item === needle) { index = i; break; } } return index; }; } return indexOf.call(this, needle) > -1;
	}
};

6. Edit routes index.js on folder routes/index.js and copy the following code

var express = require('express');
var router = express.Router();
var modul = require('../modul/modul');

var session_store;
/* GET home page. */
router.get('/', function(req, res, next) {
	res.render('index', { title: 'Express' });
});
router.get('/login',function(req,res,next){
	res.render('main/login',{title:"Login Page"});
});
router.post('/login',function(req,res,next){
	session_store=req.session;
	req.assert('txtEmail', 'Please fill the Username').notEmpty();
	req.assert('txtEmail', 'Email not valid').isEmail();
	req.assert('txtPassword', 'Please fill the Password').notEmpty();
	var errors = req.validationErrors();
	if (!errors) {
		req.getConnection(function(err,connection){
			v_pass = req.sanitize( 'txtPassword' ).escape().trim(); 
			v_email = req.sanitize( 'txtEmail' ).escape().trim();
			
			var query = connection.query('select * from user where the_email="'+v_email+'" and the_password=md5("'+v_pass+'")',function(err,rows)
			{
				var users=rows;
				var user_menu = users[0]['user_menu'];
				var split_user_menu = user_menu.split(',');
				if(err)
				{
					
					var errornya  = ("Error Selecting : %s ",err.code );  
					console.log(err.code);
					req.flash('msg_error', errornya); 
					res.redirect('/login'); 
				}else
				{
					if(rows.length <=0)
					{

						req.flash('msg_error', "Wrong email address or password. Try again."); 
						res.redirect('/login');
					}
					else
					{	

						var querys = connection.query('SELECT * FROM r_menu',function(err,rows)
						{
							if(err)
								console.log(("Error Selecting : %s ",err ));   
							var result = [];
							var nil = 0;
							for(var x in rows) 
							{

								var id_mn=rows[x].id_menu;
								var sub_query = connection.query('SELECT * FROM r_menu_sub where id_menu = '+id_mn+" order by urutan_sub_menu asc",function(err,xy)
								{
									if(err)
										console.log(("Error Selecting : %s ",err ));
									var data_submenu = new Array();
									var pjg = xy.length -1;
									for (var i = 0 ; i <= pjg; i++)
									{
										var id_mnus= xy[i]['id_sub_menu'];
										if(modul.in_array.call(split_user_menu,id_mnus.toString()))
										{
											data_submenu.push(xy[i]);
										}
										if(i == pjg)
										{
											result.push({"name":rows[nil].nama_menu,"icon":rows[nil].icon,"id_menu":rows[nil].id_menu,"sub_menu":data_submenu});
										}
									}
									if(nil+1 == rows.length)
									{
										session_store.sidebar = result;
										session_store.is_login = true;
										res.redirect('/customers');
									}
									nil++;
								});

							}


						});

}
}

});
});
}else{
	errors_detail = "

Sory there are error
<ul>";
	for (i in errors) 
	{ 
		error = errors[i]; 
		errors_detail += '
<li>'+error.msg+'</li>

'; 
	} 
	errors_detail += "</ul>

"; 
	console.log(errors_detail);
	req.flash('msg_error', errors_detail); 
	res.render('main/login', 
	{ 

	});
}
});


module.exports = router;

7. Edit sidebar.jade (views/layout/sidebar.jade) and copy the following code:

aside.main-sidebar
	section.sidebar
		.user-panel
			.pull-left.image
				img(src='/adminlte/dist/img/user2-160x160.jpg',alt='User Image').img-circle
			.pull-left.info
				p Alexander Pierces
				a(href='#')
					i.fa.fa-circle.text-success 
					| Online
		form(action='#',method='get').sidebar-form
			.input-group
				input(type='text', name='q',placeholder='Search...').form-control
				span.input-group-btn
						button(type='submit', name='search')#search-btn.btn.btn-flat
							i.fa-fa-search
		ul.sidebar-menu
			li.header
				| Main Navigation
			for row, index in session_store.sidebar
				li.treeview
					a(href='#')
						!= row.icon
						span
							|#{row.name}
						span.pull-right-container
							i.fa.fa-angle-left.pull-right
					ul.treeview-menu
						for y,i in row.sub_menu
							li
								a(href='#{y.url}')
									i.fa.fa-circle-o
									| #{y.nama_sub_menu}

8. Add a session variable at each route. For example, I added a session variable on routes customers.js

var express = require('express');
var router = express.Router();
var authentication_mdl = require('../middlewares/authentication');
var session_store;
/* GET Customer page. */

router.get('/',authentication_mdl.is_login, function(req, res, next) {
	req.getConnection(function(err,connection){
		var query = connection.query('SELECT * FROM customer',function(err,rows)
		{
			if(err)
				var errornya  = ("Error Selecting : %s ",err );   
			req.flash('msg_error', errornya);   
			res.render('customer/list',{title:"Customers",data:rows,session_store:req.session});
		});
         //console.log(query.sql);
     });
});

router.delete('/delete/(:id)',authentication_mdl.is_login, function(req, res, next) {
	req.getConnection(function(err,connection){
		var customer = {
			id: req.params.id,
		}
		
		var delete_sql = 'delete from customer where ?';
		req.getConnection(function(err,connection){
			var query = connection.query(delete_sql, customer, function(err, result){
				if(err)
				{
					var errors_detail  = ("Error Delete : %s ",err);
					req.flash('msg_error', errors_detail); 
					res.redirect('/customers');
				}
				else{
					req.flash('msg_info', 'Delete Customer Success'); 
					res.redirect('/customers');
				}
			});
		});
	});
});
router.get('/edit/(:id)',authentication_mdl.is_login, function(req,res,next){
	req.getConnection(function(err,connection){
		var query = connection.query('SELECT * FROM customer where id='+req.params.id,function(err,rows)
		{
			if(err)
			{
				var errornya  = ("Error Selecting : %s ",err );  
				req.flash('msg_error', errors_detail); 
				res.redirect('/customers'); 
			}else
			{
				if(rows.length <=0)
				{
					req.flash('msg_error', "Customer can't be find!"); 
					res.redirect('/customers');
				}
				else
				{	
					console.log(rows);
					res.render('customer/edit',{title:"Edit ",data:rows[0],session_store:req.session});

				}
			}

		});
	});
});
router.put('/edit/(:id)',authentication_mdl.is_login, function(req,res,next){
	req.assert('name', 'Please fill the name').notEmpty();
	var errors = req.validationErrors();
	if (!errors) {
		v_name = req.sanitize( 'name' ).escape().trim(); 
		v_email = req.sanitize( 'email' ).escape().trim();
		v_address = req.sanitize( 'address' ).escape().trim();
		v_phone = req.sanitize( 'phone' ).escape();

		var customer = {
			name: v_name,
			address: v_address,
			email: v_email,
			phone : v_phone
		}

		var update_sql = 'update customer SET ? where id = '+req.params.id;
		req.getConnection(function(err,connection){
			var query = connection.query(update_sql, customer, function(err, result){
				if(err)
				{
					var errors_detail  = ("Error Update : %s ",err );   
					req.flash('msg_error', errors_detail); 
					res.render('customer/edit', 
					{ 
						name: req.param('name'), 
						address: req.param('address'),
						email: req.param('email'),
						phone: req.param('phone'),
					});
				}else{
					req.flash('msg_info', 'Update customer success'); 
					res.redirect('/customers/edit/'+req.params.id);
				}		
			});
		});
	}else{

		console.log(errors);
		errors_detail = "

Sory there are error
<ul>";
		for (i in errors) 
		{ 
			error = errors[i]; 
			errors_detail += '
<li>'+error.msg+'</li>

'; 
		} 
		errors_detail += "</ul>

"; 
		req.flash('msg_error', errors_detail); 
		res.redirect('/customers/edit/'+req.params.id);
	}
});

router.post('/add',authentication_mdl.is_login, function(req, res, next) {
	req.assert('name', 'Please fill the name').notEmpty();
	var errors = req.validationErrors();
	if (!errors) {

		v_name = req.sanitize( 'name' ).escape().trim(); 
		v_email = req.sanitize( 'email' ).escape().trim();
		v_address = req.sanitize( 'address' ).escape().trim();
		v_phone = req.sanitize( 'phone' ).escape();

		var customer = {
			name: v_name,
			address: v_address,
			email: v_email,
			phone : v_phone
		}

		var insert_sql = 'INSERT INTO customer SET ?';
		req.getConnection(function(err,connection){
			var query = connection.query(insert_sql, customer, function(err, result){
				if(err)
				{
					var errors_detail  = ("Error Insert : %s ",err );   
					req.flash('msg_error', errors_detail); 
					res.render('customer/add-customer', 
					{ 
						name: req.param('name'), 
						address: req.param('address'),
						email: req.param('email'),
						phone: req.param('phone'),
						session_store:req.session,
					});
				}else{
					req.flash('msg_info', 'Create customer success'); 
					res.redirect('/customers');
				}		
			});
		});
	}else{

		console.log(errors);
		errors_detail = "

Sory there are error
<ul>";
		for (i in errors) 
		{ 
			error = errors[i]; 
			errors_detail += '
<li>'+error.msg+'</li>

'; 
		} 
		errors_detail += "</ul>

"; 
		req.flash('msg_error', errors_detail); 
		res.render('customer/add-customer', 
		{ 
			name: req.param('name'), 
			address: req.param('address'),
			session_store:req.session
		});
	}

});

router.get('/add',authentication_mdl.is_login, function(req, res, next) {
	res.render(	'customer/add-customer', 
	{ 
		title: 'Add New Customer',
		name: '',
		email: '',
		phone:'',
		address:'',
		session_store:req.session
	});
});

module.exports = router;

Save and run the node server with following script

on Linux

DEBUG=expressproject:* npm start

and on windows

set DEBUG=myapp:* & npm start

Open your browser and run http://localhost:3000/login

User: test@test.com

Password: 123456

If successful, your main page will display as shown below:

Main Menu Tutorial Create Dynamic Sidebar Menu On MySQL, Node.js, Express, AdminLTE For Beginner

To try permissions menu, please change user_menu value in user table. The latest value is 1,4,6 change with 1,2,3 and check the result.

To download the entire project, please share the link below to get the download link

Thus an article about Tutorial Create Dynamic Sidebar Menu on MySQL, Node.js, Express, AdminLTE For Beginner, hope useful 🙂

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.

Leave a Comment

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.

%d bloggers like this: