Learning MySQL / PostgreSQL and other stuff: Improving the menus

It would be great if one single program were to be flexible enough to handle all the menus. After all, they are in a single database table.

The idea is to pass the program a parameter telling it which menu to show. It will test, and if no menu has been requested, it will show the main menu.

I'll also move tings around to put the database open and closing in to functions, and the head and tail printing as well.

One thing to bear in mind is that the menu header is currently hard code, and this will need to be pulled from the database.

So, create a table to store the menu details!

We'll need a primary index on the menus table:


CREATE TABLE menus (
   id           INT NOT NULL AUTO_INCREMENT,
   name         VARCHAR(20),
   title        VARCHAR(40),
   description  VARCHAR(80),
   last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP (),
   PRIMARY KEY (id)
)  ENGINE=INNODB;

CREATE TABLE cddb.menuitems (
   menuid    INT NOT NULL,
   itemno    INT NOT NULL,
   itemtext  VARCHAR(50) NOT NULL,
   itemlink  VARCHAR(150) NOT NULL,
   last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP (),
   FOREIGN KEY (menuid) REFERENCES menus(id) ON DELETE CASCADE
)  ENGINE=INNODB;

And repopulate:


INSERT INTO cddb.menus (name, title, description) values ('mainmenu','Main Menu', 'Welcome to my CD Collection');
INSERT INTO cddb.menus (name, title, description) values ('listmenu','List Menu', 'List Details of my CD Collection');

INSERT INTO cddb.menuitems (menuid, itemno, itemtext, itemlink)
  values (1,1,'Data Entry','mainmenu.php?menuname=dataentrymenu');

INSERT INTO cddb.menuitems (menuid, itemno, itemtext, itemlink)
  values (1,2,'Data Lists','mainmenu.php?menuname=listmenu');

INSERT INTO cddb.menuitems (menuid, itemno, itemtext, itemlink)
  values (1,3,'Reports','mainmenu.php?menuname=reportsmenu');

INSERT INTO cddb.menuitems (menuid, itemno, itemtext, itemlink)
  values (2,1,'List Publisher','publishers.php');
INSERT INTO cddb.menuitems (menuid, itemno, itemtext, itemlink)
  values (2,2,'List Artists','artists.php');
INSERT INTO cddb.menuitems (menuid, itemno, itemtext, itemlink)
  values (2,3,'CDs by Artists','lcdsbyartist.php');
INSERT INTO cddb.menuitems (menuid, itemno, itemtext, itemlink)
  values (2,4,'Tracks by CD','track.php');

Finally - rewrite the menu PHP


<?php
function openMyDB()
{
include("DBInfo.inc");
$con = mysql_connect($mydbhost,$mydbuser,$mydbpassword);
if (!$con) {
 die('Could not connect: ' . mysql_error());
}
mysql_select_db("cddb", $con);
return $con;
}

function closeMyDB($con)
{
mysql_close($con);
}

function writeHead($MenuName)
{
$MyQuery =            "SELECT title, description FROM menus m ";
$MyQuery = $MyQuery . "WHERE m.name = '" . $MenuName . "'";

$result = mysql_query($MyQuery);
$row = mysql_fetch_array($result);

echo "<html>\n<head>\n<title>" . $row['title'] . "</title>\n</head>\n";
echo "<body>\n";
echo "<h1>" . $row['description'] . "</h1>\n";
mysql_free_result($result);
}

function writeTail()
{
echo "</body>\n</html>";
}

// Main Program starts here

$mycon=openMyDB();

if ( empty($_GET['menuname'])) 
 $MyMenuName='mainmenu';
else
 $MyMenuName=$_GET['menuname'];


writehead($MyMenuName);
echo "<ol>\n";

$MyQuery =            "SELECT itemtext, itemlink, itemno FROM menus m, menuitems mi ";
$MyQuery = $MyQuery . "WHERE m.name = '" . $MyMenuName . "' ";
$MyQuery = $MyQuery . "and m.id=mi.menuid order by itemno";

$result = mysql_query($MyQuery);
while ($row = mysql_fetch_array($result)) {
 echo "<li>\n";
 echo "<a href=\"" . $row['itemlink'] . "\">";
 echo $row['itemtext'] . "</a>";
 echo "\n</li>\n";
}
mysql_free_result($result);
echo "</ol>";
writetail();
closeMyDB($mycon);

?>

Learning MySQL / PostgreSQL and other stuff

Tuesday, June 16, 2009

Improving the menus

No comments: