Learning MySQL / PostgreSQL and other stuff: PHP

I have decided that I want all the menus on my web site to be dynamic, driven from the database rather than hard-coded in HTML. This has the advantage that it you want to add a menu item, you just add it to the database, you don't need to recode your menus. Of course it requires a little more work up front, but that's what we are here for!

Step 1:
Create a database table to hold your menus:


CREATE TABLE cddb.menus (
  name      VARCHAR(20) NOT NULL,
  itemno    INT NOT NULL,
  itemtext  VARCHAR(50) NOT NULL,
  itemlink  VARCHAR(150) NOT NULL
);

Step 2:
Create a text file with your menus defined in it:


mainmenu,1,Data Centre,dataentry.php
mainmenu,2,Data Lists,datalists.php
mainmenu,3,Reports,reports.php
listmenu,1,List Publishers,publishers.php
listmenu,2,List Artists,artists.php
listmenu,3,CDs by Artist,lcdsbyartist.php
listmenu,4,Tracks by CD,track.php

I am working on a Windows laptop, and I found that the load command didn't handle the windows EOL characters well, it corrupts the final field. I'm sure that there is a way around this, but I ended up using a dos2unix command in cygwin.

Step 3:
Import your menu data into your database:


mysql> LOAD DATA LOCAL INFILE 'menus.txt' INTO TABLE cddb.menus FIELDS TERMINATED BY ',';
Query OK, 7 rows affected, 7 warnings (0.00 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 7

mysql> select * from menus;
+----------+--------+-----------------+------------------+
| name     | itemno | itemtext        | itemlink         |
+----------+--------+-----------------+------------------+
| mainmenu |      1 | Data Centre     | dataentry.php    |
| mainmenu |      2 | Data Lists      | datalists.php    |
| mainmenu |      3 | Reports         | reports.php      |
| listmenu |      1 | List Publishers | publishers.php   |
| listmenu |      2 | List Artists    | artists.php      |
| listmenu |      3 | CDs by Artist   | lcdsbyartist.php |
| listmenu |      4 | Tracks by CD    | track.php        |
+----------+--------+-----------------+------------------+
7 rows in set (0.00 sec)

mysql>

Step 4:

Final Step, to get write the PHP to create the index page.


<?php
echo "<html><head><title>Main Menu</title></head>";
echo "<body>";
echo "<h1>Welcome to my CD Collection</h1>";
echo "<ol>\n";
$con = mysql_connect("localhost","timj","xxx");
if (!$con) {
    die('Could not connect: ' . mysql_error());
}

mysql_select_db("cddb", $con);

$result = mysql_query("SELECT itemtext, itemlink, itemno FROM menus WHERE name = 'mainmenu' order by itemno");
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);
mysql_close($con);
echo "</ol>";
echo "</body>";
echo "</html>";
?>

And that's all there is to it!

Learning MySQL / PostgreSQL and other stuff

Friday, May 8, 2009

PHP - My website's menus

No comments: