Defining the database schema with MySQL - PHP

Since the previous section was dedicated to demonstrating the complete process for creating the pair of files that put the presentation layer to work, the current one will be focused on defining the simple database schema which will be used for storing the contents of each dynamic web document.

With reference to the above concept, first I'll create a single database table called "pages" where each row will store web page data. It will be structured as follows: page id, page title, header contents, left column contents, center column contents, right column contents, and finally footer contents.

As you'll realize, the previous schema is extremely simple to implement, and certainly can be translated to real SQL code by the following statement:

CREATE TABLE 'pages' (
  'id' INTEGER(4) NOT NULL AUTO_INCREMENT,
  'title' VARCHAR(45) NOT NULL DEFAULT '',
  'header' TEXT NOT NULL DEFAULT '',
  'leftcol` LONGTEXT NOT NULL DEFAULT '',
  'centercol' LONGTEXT NOT NULL DEFAULT '',
  'rightcol' LONGTEXT NOT NULL DEFAULT '',
  'footer' TEXT NOT NULL DEFAULT '',
  PRIMARY KEY(`id`)
)
ENGINE = InnoDB;

Now that the above "pages" database table has been properly created, I'll do something more useful with it. I'm going to fill it up with some basic data to create the contents of all the web site sections that I showed you previously. How will this be done? That's as simple as running the following SQL INSERT statements:

INSERT INTO pages (id,title,header,leftcol,centercol,rightcol,footer) VALUES
(NULL,'HomePage','Content of header section goes here','Content
of left column goes here','Content of main section goes
here','Content of right column goes here','Content of footer
section goes here');

INSERT INTO pages (id,title,header,leftcol,centercol,rightcol,footer) VALUES(NULL,'
Profile Page','Content of header section goes here','Content of
left column goes here',' Content of main section for profile page
goes here','Content of right column goes here','Content of footer
section goes here');

INSERT INTO pages (id,title,header,leftcol,centercol,rightcol,footer) VALUES(NULL,'
Products Page','Content of header section goes here','Content of
left column goes here',' Content of main section for products
page goes here','Content of right column goes here','Content of
footer section goes here');

INSERT INTO pages (id,title,header,leftcol,centercol,rightcol,footer) VALUES(NULL,'
Services Page','Content of header section goes here','Content of
left column goes here',' Content of main section for services
page goes here','Content of right column goes here','Content of
footer section goes here');

INSERT INTO pages (id,title,header,leftcol,centercol,rightcol,footer) VALUES(NULL,'
Customers Page','Content of header section goes here','Content of
left column goes here','Content of main section for products page
goes here','Content of right column goes here'','Content of
footer section goes here');

INSERT INTO pages (id,title,header,leftcol,centercol,rightcol,footer) VALUES(NULL,'
Contact Page','Content of header section goes here','Content of
left column goes here',' Content of main section for contact page
goes here','Content of right column goes here','Content of footer
section goes here');

Okay, as shown above, the previous six INSERT statements populate the corresponding "pages" database table with some basic data that belong to each one of the website's sections. Following this schema, you can see that every web page now contains a concrete title, and some descriptive texts for the respective header and footer areas, as well as for the pertinent columns. Wasn't that easy?

Now that the "pages" database table has been filled with information related to each particular section of the website in question, it should be clear to you how to construct a simple application with PHP that fetches data from the mentioned table and injects it straight into the previous template file.

And best of all, this process can be performed with a unique "pageid" parameter! Definitely, we're on the right way to constructing a  simple yet efficient website engine with PHP. But, to see how this will be done, you'll have to wait till the next tutorial.

Wrapping up

In this first article of the series, I introduced the key points for how to build an extensible website engine with PHP 5. More specifically, I developed a comprehensive presentation layer, in conjunction with the respective data layer. However, as I mentioned before, there's a missing piece in this puzzle: the PHP-based application, which will be developed in the next part. Thus, you won't want to miss it!