|
|
Table used for the site index and menus
We found that whenever we wanted to add a section to our website, or take a piece off-line while we updated it, it took us ages to update all the various link lists and menu bars on the pages around the rest of the site. We've used databases before for generating static content, but this time we wanted to make our site very much more dynamic, and use the database on-line. Our site doesn't get a large amount of traffic, so it was felt that having all these database activity, especially with a database as fast as mySQL, wouldn't give us too many difficulties.
We just have one table in our mySQL database to store all the information needed for our menu bar at the top of the page, the link list at the bottom of the page, and the site index. We also use this table to store the descriptions of pages which get inserted into the page header has META descriptions and can also be used for the site search which we'll be adding soon. For the moment, we illustrate the point by allowing visitors to show descriptions of the pages in the Site Index, and also use the same scripts and table to create the Contents lists for all the sections of the website.
Below is SQL CREATE statement for creating the site_index table which stores everything we need
|
|
<![CDATA[
CREATE TABLE
site_index
(
id INTEGER UNSIGNED NOT NULL
AUTO_INCREMENT PRIMARY KEY,
item_text VARCHAR(80),
graphic_url TEXT,
link_url VARCHAR(255),
parent_item_id INTEGER UNSIGNED NOT NULL,
sort_order INTEGER UNSIGNED NOT NULL,
page_active TINYINT UNSIGNED,
visits INTEGER UNSIGNED,
page_active TINYINT UNSIGNED,
meta_description TEXT,
INDEX parent_item_id (parent_item_id),
INDEX visits (visits),
INDEX link_url (link_url)
);]]>
|
Description of the Fields
|
|
|
