Printer Friendly Version

Have a question about this article, object-oriented programming, or PHP? Visit Intranet Journal's Discussion Forum

In this series we've been working through the construction of a Content Management System, for use with an Intranet or Web site. The foundation has been laid in the form of a PHP class for accessing the database ('DbConnector'), and to kick off this month we'll set up the database itself, and create the first working part of the system.

Creating the Database

The first table we're going to add to our database will store articles, for display on the Intranet. The ability to share information is the most important function of an Intranet, and the job of the Content Management System is to make doing this as easy as possible. Consider your own data requirements, a few important ones spring to mind for most articles tables:

Before we can create the system itself, we need to create the database to store our information. The code below will set this up if you're using the MySQL database system - uses of other systems should modify the commands appropriately. Copy and paste the following into the MySQL admin tool, or use one of the many free 'client' programs available:

CREATE TABLE `databasename`.`cmsarticles` (
`ID` int(6) unsigned NOT NULL auto_increment COMMENT 'The unique ID of the article',
`title` varchar(200) NULL COMMENT 'The article title',
`tagline` varchar(255) NULL COMMENT 'Short summary of the article',
`section` int(4) NULL DEFAULT 0 COMMENT 'The section of the article',
`thearticle` text NULL COMMENT 'The article itself',
PRIMARY KEY (`ID`)
);

If all has gone to plan, you should now have a working table in the database. We're now going to create a page to allow you or your staff to enter articles into the system.

Creating the editor

Firstly, design a form using the HTML editor of your choice. Create text fields for each database field (excluding ID). An example is below:

Set the action of the form to be newArticle.php (with the method 'post'), and save this page in a folder called cmsadmin (described in the previous article). If any of this is unclear, just browse through the attached file at the end of the article. Note that the 'section' field is currently a text box, by the time we've finished it'll be a drop-down list, allowing you to choose a section of the site in which to place the article.

Next, we'll create the PHP code to deal with whatever is typed into this form, and save it to the database for later retrieval. The code is below, with explanation beneath:

<?php
// Get the PHP file containing the DbConnector class
require_once('../includes/DbConnector.php');

// Check whether a form has been submitted. If so, carry on
if ($HTTP_POST_VARS){

// Create an instance of DbConnector
$connector = new DbConnector();

// IMPORTANT!! ADD FORM VALIDATION CODE HERE - SEE THE NEXT ARTICLE

// Create an SQL query (MySQL version)
$insertQuery = "INSERT INTO cmsarticles (title,tagline,section,thearticle) VALUES (".
"'".$HTTP_POST_VARS['title']."', ".
"'".$HTTP_POST_VARS['tagline']."', ".
$HTTP_POST_VARS['section'].", ".
"'".$HTTP_POST_VARS['thearticle']."')";

// Save the form data into the database
if ($result = $connector->query($insertQuery)){

// It worked, give confirmation
echo '<center><b>Article added to the database</b></center><br>';

}else{

// It hasn't worked so stop. Better error handling code would be good here!
exit('<center>Sorry, there was an error saving to the database</center>');

}

}
?>

We start off by requiring the 'dbConnector' class that we created in the previous article. If it can't be found, an error will be displayed. We then check whether a form has been submitted, by seeing if $HTTP_POST_VARS exists (this variable contains all the submitted form data). Next we assemble the database query, and store it in $insertQuery, before actually running it using the query command we created last time. Finally, a message is shown to the user confirming success, or showing failure.

Try adding an article. For the time being you'll have to type an integer into the 'section' box, as we haven't yet created a drop-down menu to display the section names. We now have a way of adding articles to the database, but for this to be of any use we must allow people to retrieve them again. Let's make a page to do that.