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.

 

 

TLDR – QUICK SLIDES

 

TABLE OF CONTENTS

 

DOWNLOAD & NOTES

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

 

QUICK NOTES

  • Create a database and import 1-menu.sql.
  • Change the database settings in 2-menu.php to your own.
  • Launch 3a-demo.php in your browser.
If you spot a bug, feel free to comment below. I try to answer short 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.

 

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.

 

 

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

1A) MENU ITEMS TABLE

1-database.sql
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

1-database.sql
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

2-menu.php
<?php
// (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
$dbHost = "localhost";
$dbName = "test";
$dbChar = "utf8";
$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

3a-page.php
<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!

4-save.php
<?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.

 

 

EXTRA 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!

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

  1. Amazing that I have been studying this code for several days and when I went back to your explanation this morning, the download file was updated.
    I created an empty file named menu.html and the save function works but it only saves the top-level menu links, without the surrounding nav tags – but no child items. For a one-level menu this is perfect, you could put the nav tags in the container document itself. I might even try and create a form to help with creating and editing the database rows – a sort of menu-builder for my app.
    Thank you for sharing your knowledge and code!

  2. Hi, this seems to be a very promising solution. Thanks so much. I downloaded your files and tried it. Unfortunately I get this:

    Undefined offset: 0 in php-mysql-menu/3a-demo.php on line 19
    Invalid argument supplied for foreach() in 3a-demo.php on line 19

    I also tried a var_dump on $menu which returns array(0) {}
    Any idea, what is wrong here?

    Thanks
    Michael

    1. Tutorial updated, download the new version if you like. I will assume you mean “CSS class”. 3a-page.php – B1 already differentiates menu items with dropdown with class="mGrp".

  3. 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.

  4. 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 *