3 Steps Simple Dynamic Menu With PHP MYSQL

Welcome to a quick tutorial on how to create a dynamic navigation menu with PHP and MySQL. So you are looking for ways to dynamically generate a menu, and not have to update the HTML every time?

But just how is this done? Let us walk through an example in this guide – Read on!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

TUTORIAL VIDEO

 

STEP 1) MENU DATABASE TABLE

1A) MENU ITEMS TABLE

CREATE TABLE `menu_items` (
  `item_id` bigint(20) NOT NULL,
  `parent_id` bigint(20) NOT NULL DEFAULT 0,
  `item_text` varchar(255) NOT NULL,
  `item_link` varchar(255) NOT NULL,
  `item_target` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `menu_items`
  ADD PRIMARY KEY (`item_id`),
  ADD KEY (`parent_id`);
 
ALTER TABLE `menu_items`
  MODIFY `item_id` bigint(20) NOT NULL AUTO_INCREMENT;

Field	Description
item_id	Menu item ID, the primary key.
parent_id	For nested menu items – The parent’s menu item ID. Leave this as 0 if first level menu item.
item_text	The label of the menu item.
item_link	Where the menu item links to.
item_target	Optional. This should either be _BLANK or _SELF if you want to set.

 

 

1B) DUMMY MENU ITEMS

INSERT INTO `menu_items` (`item_id`, `parent_id`, `item_text`, `item_link`, `item_target`) VALUES
  (1, 0, 'Home', '/', NULL),
  (2, 0, 'Blog', 'blog/', NULL),
  (3, 0, 'Reviews', 'reviews/', NULL),
  (4, 0, 'Shop', 'shop/', '_BLANK'),
  (5, 2, 'How To', 'blog/how/', NULL),
  (6, 2, 'Technology', 'blog/tech/', NULL),
  (7, 2, 'Hacks', 'blog/hacks/', NULL);

These should be pretty self-explanatory. The first level items are home, blog, reviews, and shop. Only “blog” has second-level items – How to, technology, and hacks. Of course, feel free to nest deeper, for example, (8, 7, 'Windows Hacks', 'blog/hacks/windows/', NULL). But I will advise against that – The design and HTML become very difficult with deeply nested items.

 

STEP 2) GET MENU ITEMS FROM DATABASE

<?php
// (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
$dbHost = "localhost";
$dbName = "test";
$dbChar = "utf8mb4";
$dbUser = "root";
$dbPass = "";
$pdo = new PDO(
  "mysql:host=$dbHost;dbname=$dbName;charset=$dbChar",
  $dbUser, $dbPass, [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]);

// (B) DRILL DOWN GET MENU ITEMS
// ARRANGE BY [PARENT ID] => [MENU ITEMS]
$menu = []; $next = [0];
while (true) {
  $stmt = $pdo->prepare(sprintf(
    "SELECT * FROM `menu_items` WHERE `parent_id` IN (%s)",
    implode(",", $next)
  ));
  $stmt->execute();
  $next = [];
  while ($r = $stmt->fetch()) {
    if (!isset($menu[$r["parent_id"]])) { $menu[$r["parent_id"]] = []; }
    $menu[$r["parent_id"]][$r["item_id"]] = $r;
    $next[] = $r["item_id"];
  }
  if (count($next) == 0) { break; }
}

// (C) CLOSE DATABASE CONNECTION
$stmt = null;
$pdo = null;

Yikes! This looks pretty confusing, but keep calm and look carefully:

1. Connect to the database. Remember to change the settings to your own.
2. Use a while loop to automatically drill down to get the menu items. The menu items are in the arrangement of $menu[PARENT ID][ITEM ID] = MENU ITEM.
3. Close the database connection.

I can hear angry trolls screaming “so stupid and dangerous to use an infinite while loop”. I agree, the more deeply nested the menu is, the slower and more complex this becomes. There is also a risk of getting into an infinite loop.

But this is also one of the better ways to satisfy everyone who wants to do deeply nested menus. There is no perfect system, I cannot please everyone – So feel free to change this part to a recursive function if you are not happy with it.

 

 

STEP 3) DRAW HTML MENU

<nav class="menu"><?php
// (A) SUPPORT FUNCTION TO DRAW AN <A>
function draw ($i) {
  printf("<a %shref='%s'>%s</a>",
    $i["item_target"]!="" ? "target='". $i["item_target"] ."' " : "" ,
    $i["item_link"], $i["item_text"]
  );
}
 
// (B) DRAW MENU ITEMS
require "2-menu.php";
foreach ($menu[0] as $id=>$i) {
  // (B1) WITH SUB-ITEMS
  if (isset($menu[$id])) { ?>
  <div class="mGrp">
    <div class="mTitle"><?=$i["item_text"]?></div>
    <div class="mItems"><?php
      foreach ($menu[$id] as $cid=>$c) { draw($c); }
    ?></div>
  </div>
 
  <?php
  // (B2) SINGLE MENU ITEM
  } else { draw($i); }
}
?></nav>

Lastly, we just build the HTML menu. Take note, this will only go 2 levels deep. I will not go into how the CSS drop-down menu works, since I literally covered it in another tutorial – Links below if you are interested.

 

 

EXTRA) A SMARTER DYNAMIC MENU – SAVE IT!

<?php
// (A) GET MENU ITEMS
require "2-menu.php";
 
// (B) SUPPORT FUNCTION TO DRAW AN <A>
function draw ($i) {
  printf("<a %shref='%s'>%s</a>",
    $i["item_target"]!="" ? "target='". $i["item_target"] ."' " : "" ,
    $i["item_link"], $i["item_text"]
  );
}
 
// (C) WRITE HTML MENU TO FILE
ob_start();
foreach ($menu[0] as $id=>$i) {
  // (C1) WITH SUB-ITEMS
  if (isset($menu[$id])) { ?>
  <div class="mGrp">
    <div class="mTitle"><?=$i["item_text"]?></div>
    <div class="mItems"><?php
      foreach ($menu[$id] as $cid=>$c) { draw($c); }
    ?></div>
  </div>
 
  <?php
  // (C2) SINGLE MENU ITEM
  } else { draw($i); }
}
 
// (C3) OB TO MENU.HTML
file_put_contents("menu.html", str_replace(["\r", "\n"], "", ob_get_contents()));
ob_end_flush();

The above example works, and it is probably how almost every tutorial on the Internet preaches too. But consider the fact that the dynamic menu loads off the database on every user visit, and it is not the best for performance. So here’s a small trick – Directly generate an HTML file of the menu, it’s way faster to load static HTML.

 

 

 

I HAVE MULTIPLE MENUS!

So what if there is more than one menu? For example, the main menu, sidebar navigation, and footer menu?

• Simply add another menu_id to the table. For example, we can set 1 for the main menu, 2 for sidebar, and 3 for footer.
• Modify the SQL – SELECT * FROM `menu_items` WHERE `menu_id`=X AND `parent_id`...
• Yep, will be a good idea to package the entire 2-menu.php into a library function.

 

 

LINKS & REFERENCES

• Simple Drop-Down Menu With CSS – Code Boxx
• How to Create Simple Collapsible Tree Menu With Pure CSS – Code Boxx
• 100 Great CSS Menu Tutorials – JotForm