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?

We can create a simple dynamic navigation menu with PHP MySQL in just a few steps:

  1. Create a database table to store the menu items.
  2. Create a PHP script to fetch the menu items from the database.
  3. Finally, draw the menu in HTML.

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

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

QUICK SLIDES

 

TABLE OF CONTENTS

Download & Notes Dynamic Menu Useful Bits & Links
Tutorial Video The End

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

QUICK NOTES

  • Create a database and import the 1-menu.sql file.
  • Change the database settings in 2-menu.php to your own.
  • Launch 3a-demo.php in your browser.

If you spot a bug, please feel free to comment below. I try to answer questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

 

DYNAMIC MENU IN PHP MYSQL

All right, let us now get into the example of creating a dynamic menu with PHP and MYSQL.

 

STEP 1) MENU DATABASE TABLE

MENU ITEMS TABLE

1-database.sql
CREATE TABLE `menu_items` (
  `item_id` int(11) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `item_text` varchar(255) NOT NULL,
  `item_link` varchar(255) NOT NULL,
  `item_target` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
ALTER TABLE `menu_items`
  ADD PRIMARY KEY (`item_id`);
 
ALTER TABLE `menu_items`
  MODIFY `item_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
Field Description
item_id Menu item ID, the primary key.
parent_id For nested menu items – The parent item ID. Leave as NULL if root level.
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.

 

DUMMY MENU ITEMS

1-database.sql
INSERT INTO `menu_items` (`item_id`, `parent_id`, `item_text`, `item_link`, `item_target`) VALUES
  (1, NULL, 'Home', '/', NULL),
  (2, NULL, 'Blog', 'blog/', NULL),
  (3, NULL, 'Reviews', 'reviews/', NULL),
  (4, NULL, '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 becomes very difficult with deep nested items.

 

 

STEP 2) GET MENU ITEMS FROM DATABASE

2-menu.php
<?php
// (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
$dbHost = "localhost";
$dbName = "test";
$dbChar = "utf8";
$dbUser = "root";
$dbPass = "";
try {
  $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
    ]
  );
} catch (Exception $ex) { exit($ex->getMessage()); }

// (B) DRILL DOWN GET MENU ITEMS
// ARRANGE BY [PARENT ID] => [MENU ITEMS]
$items = [];
while (true) {
  // (B1) SQL QUERY
  $sql = "SELECT * FROM `menu_items` WHERE `parent_id` ";
  if (!isset($next)) { $sql .= "IS NULL"; }
  else { $sql .= "IN ($next)"; }

  // (B2) FETCH MENU ITEMS
  $next = "";
  $parent = "";
  $stmt = $pdo->prepare($sql);
  $stmt->execute();
  while (($i = $stmt->fetch()) !== false) {
    $parent = $i['parent_id']=="" ? 0 : $i['parent_id'] ;
    if (!isset($items[$parent])) { $items[$parent] = []; }
    $items[$parent][$i['item_id']] = $i;
    $next .= $i['item_id'] . ",";
  }
  if ($next == "") { break; }
  else { $next = substr($next, 0, -1); }
}

// (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 $items[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 can’t please everyone – So feel free to change this part if you are not happy with it.

 

 

STEP 3) DRAW HTML MENU

3a-page.php
<nav class="ddmenu"><?php
// (A) GET MENU ITEMS
require "2-menu.php";

// (B) SUPPORT FUNCTION TO DRAW AN <A>
function drawlink ($i) {
  printf("<a %shref='%s'>%s</a>",
    $i['item_target']!="" ? "target='". $i['item_target'] ."' " : "" ,
    $i['item_link'], $i['item_text']
  );
}
 
// (C) DRAW MENU ITEMS
foreach ($items[0] as $id=>$i) {
  if (isset($items[$id])) { ?>
  <div class="ddgrp">
    <div><?=$i['item_text']?></div>
    <div class="dditems"><?php
      foreach ($items[$id] as $cid=>$c) { drawlink($c); }
    ?></div>
  </div>
  <?php } else { drawlink($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!

4-save.php
<?php
// (A) GET MENU ITEMS
require "2-menu.php";

// (B) WRITE HTML MENU TO FILE
$fh = fopen("menu.html", "w");
foreach ($items[0] as $id=>$i) {
  // FORMAT YOUR OWN HTML!
  fwrite($fh, "<a href='{$i['item_link']}'>{$i['item_text']}</a>");
}
 
// (C) DONE!
fclose($fh);

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.

 

 

USEFUL BITS & LINKS

That’s it for the dynamic menu, and here are some small extras that you may find useful.

 

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

 

TUTORIAL VIDEO

 

INFOGRAPHICS CHEAT SHEET

Dynamic Menu WIth PHP MYSQL (Click To Enlarge)

 

THE END

Thank you for reading, and we have come to the end of this tutorial. I hope that it has helped you to create a better menu for your website. If you have anything to share with this guide, please feel free to comment below. Good luck and happy coding!

6 thoughts on “3 Steps Simple Dynamic Menu With PHP MYSQL”

  1. Hi I am trying to apply this in my project. But facing lot of problem. looks like parent child relationship of menu items is creating the problem.

  2. This has been insanely useful as a PDO learning tool for me, as have many of your tutorials. Any chance you’d consider updating/extending this to include an infinite multi-level navigation?

    1. Glad it helped! I don’t think I will add any more to this navigation menu. Supposed to be simple, and I intend to keep it this way. A multi-level menu can be pretty complex… Maybe in a separate future tutorial instead?

Leave a Comment

Your email address will not be published. Required fields are marked *