Home arrow PHP arrow Page 4 - Setting up the Foundation for an Extensible Website Engine with PHP 5

Defining the database schema with MySQL - PHP

Building a dynamic website with a database backend is something that nearly every PHP programmer will need to do, usually sooner rather than later. If you haven't done it yet (or even if you have), this two-part article series will show you how to develop a website engine that can generate pages on the fly from a simple database structure.

TABLE OF CONTENTS:
  1. Setting up the Foundation for an Extensible Website Engine with PHP 5
  2. Defining the core structure of a simple yet dynamic website
  3. Creating a simple template file
  4. Defining the database schema with MySQL
By: Alejandro Gervasio
Rating: starstarstarstarstar / 14
October 24, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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!



 
 
>>> More PHP Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: