Creating an IE-Only Database Driven Menu System With PHP, MySQL and DHTML
Web pages are very flexible, and are used in more places than just the Internet. In this article, Annette will teach us how to create a PHP/DHTML driven menu system that uses MySQL to store its menu headings and items.
Creating an IE-Only Database Driven Menu System With PHP, MySQL and DHTML - Creating the database (Page 2 of 5 )
Our menu will be presented in a hierarchical format, one layer deep. This means that can we have several menu titles, and each of those titles can have as many "nodes" as they like. Our menus structure will look like this:
Menu Item 1
- Sub Item 1
- Sub Item 2
- Sub Item 3
Menu Item 2
- Sub Item 1
- Sub Item 2
Each "sub item" will contain a hyperlink to another web page. This makes sense, because we are creating a menu to navigate around an intranet, which is in essence, just a bunch of web pages tied together by a common theme.
To create our database, load the MySQL console application by typing "mysql" at the command prompt. On Windows systems, this resides in c:\mysql\bin by default. On Linux/Unix systems, it resides in the /usr/local directory.
We will create a new database named "menu". Enter the following command at the MySQL console application:
create database menu;
Press Enter. MySQL will respond with "Query OK, 1 row affected (0.06 sec)". Next, we need to connect to our new database so that we can create the table to hold our menu items. Use the "connect" command to do so:
Now, to create our table. It will be named "nodes" and will contain four fields. Use the following command to create the "nodes" table:
create table nodes
nodeId int auto_increment not null,
title varchar(100) not null,
url varchar(250) null,
parentId int not null default 0,
MySQL should respond with "Query OK, 1 row affected (0.06 sec)". Each field in our "nodes" table is described below:
nodeId: Each item in our menu will have a nodeId field that uniquely identifies it from the other menu items in the "nodes " table. It is auto incrementing, meaning its value starts from 1 and each time a new row is added, it is incremented by 1.
title: The title field will hold the actual text that will be displayed for the menu item.
url: The "url" field will contain the hyperlink for each sub-menu item. For menu items that are parents (ie: Can be expanded/collapsed), this field should be left empty.
parentId: Used by sub-menu items only. It contains the ID of the parent item under which this menu item will be created.
Our database and table have been created, but they are still empty. Before we create the PHP script to display our menu, we might actually want some menu items to our database first!
Type the following commands at the MySQL prompt (If you’re using MySQL under Windows, you can copy each one to the clip-board and then right click on the MySQL command window to paste them there). They will create three new menu headings: "ASP.NET Books", "C# Books" and "PHP Books":
insert into nodes values(0, 'ASP.NET Books', '', 0);
insert into nodes values(0, 'C# Books', '', 0);
insert into nodes values(0, 'PHP Books', '', 0);
Next, type the following commands at the MySQL console app to add some different books to our "nodes" table:
insert into nodes values(0, 'ASP.NET for Developers', 'http://www.amazon.com/exec/obidos/ASIN/067232038X/devarticlesco-20', 1);
insert into nodes values(0, 'Professional ASP.NET Web Services', 'http://www.amazon.com/exec/obidos/ASIN/1861005458/devarticlesco-20', 1);
insert into nodes values(0, 'Sams Teach Yourself ASP.NET in 21 Days', 'http://www.amazon.com/exec/obidos/ASIN/0672321688/devarticlesco-20', 1);
insert into nodes values(0, 'Data-Centric .NET Programming with C#', 'http://www.amazon.com/exec/obidos/ASIN/186100592X/devarticlesco-20', 2);
insert into nodes values(0, 'Application Development Using C# and .NET', 'http://www.amazon.com/exec/obidos/ASIN/013093383X/devarticlesco-20', 2);
insert into nodes values(0, 'Introduction to C# Using .NET', 'http://www.amazon.com/exec/obidos/ASIN/0130418013/devarticlesco-20', 2);
insert into nodes values(0, 'PHP and MySQL Web Development', 'http://www.amazon.com/exec/obidos/ASIN/0672317842/devarticlesco-20', 3);
insert into nodes values(0, 'Beginning PHP4', 'http://www.amazon.com/exec/obidos/ASIN/1861003730/devarticlesco-20', 3);
Now that our "nodes" table is full of data, we can create the PHP script to display the menu items as part of a simple DHTML-based menu system.