Building Multi Level Menu Dynamically with PDO in PHP and MySQL

Home » PHP & MySQL » PHP » Building Multi Level Menu Dynamically with PDO in PHP and MySQL
10 Oct, 2018 Categories: PHP


Database

Create a new MySQL database named mydemo and execute the SQL code below:

-- phpMyAdmin SQL Dump
-- version 4.1.14
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Oct 23, 2015 at 08:40 AM
-- Server version: 5.6.17
-- PHP Version: 5.5.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `mydemo`
--

-- --------------------------------------------------------

--
-- Table structure for table `menu`
--

CREATE TABLE IF NOT EXISTS `menu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `parentId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parentId` (`parentId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

--
-- Dumping data for table `menu`
--

INSERT INTO `menu` (`id`, `name`, `parentId`) VALUES
(3, 'Menu 1', NULL),
(4, 'Menu 2', NULL),
(5, 'Menu 1.1', 3),
(6, 'Menu 1.2', 3),
(7, 'Menu 1.3', 3),
(8, 'Menu 2.1', 4),
(9, 'Menu 2.2', 4),
(10, 'Menu 3', NULL),
(11, 'Menu 4', NULL),
(12, 'Menu 4.1', 11);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `menu`
--
ALTER TABLE `menu`
  ADD CONSTRAINT `menu_ibfk_1` FOREIGN KEY (`parentId`) REFERENCES `menu` (`id`);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;




Database Connection

Create PHP file named connect.php. Use PDO connect to demo database with default account:


Username: root
Password:

<?php
	$conn = new PDO("mysql:host=localhost;dbname=mydemo", 'root', '');
?>

List All Menus

Create PHP file named index.php. This file will list all level 1 menus and level 2 menus

<?php require_once 'database.php'; ?>
<html lang="en">
<head>
<meta charset="utf-8">
<title>jQuery UI Menu - Default functionality</title>
<link rel="stylesheet" href="css/jquery-ui.css">
<script src="js/jquery.js"></script>
<script src="js/jquery-ui.js"></script>
<script>
  $(function() {
    $( "#menu" ).menu();
  });
  </script>
<style>
.ui-menu {
	width: 150px;
}
</style>
</head>
<body>
<?php
$stmt = $conn->prepare('select * from menu where parentId is null');
$stmt->execute();
?>

<ul id="menu">
	<?php while($menu1 = $stmt->fetch(PDO::FETCH_OBJ)) { ?>
		<li><?php echo $menu1->name; ?>
			<?php
				$stmt1 = $conn->prepare('select * from menu where parentId = :id');
				$stmt1->bindValue('id', $menu1->id);
				$stmt1->execute();
			?>
			<?php if($stmt1->rowCount() > 0) { ?>
			<ul>
				<?php while($menu2 = $stmt1->fetch(PDO::FETCH_OBJ)) { ?>
						<li><?php echo $menu2->name; ?></li>
				<?php } ?>
			</ul>
			<?php } ?>
		</li>
	<?php } ?>
</ul>
</body>
</html>




Demo

References

I recommend you refer to the books below to learn more about the knowledge in this article:


Tags

Related Posts

 PHP CRUD with MySQL

October 10, 2018

Top