Data Management

news item addition code

In part four of Vincent Danen's Daily Drill Down series, he explains how to design the database that will maintain the site news for your dynamic Web site.


Welcome to part four of the Building a dynamic Web site series! Give me a moment to recap what I’ve accomplished so far. Previously, I took a comparative look at a few of the options available to any new or experienced Web developer or maintainer in terms of software. I decided to go with a few de facto standards: Apache, MySQL, PHP4, and Perl. I then went through the steps to install and configure these tools, and in the third part of this series, I spent some time fine-tuning PHP and implementing a few small debug pages to help you maintain your dynamic site.

In this part of the series, I’ll take things one step further and begin to create one portion of our dynamic Web site, namely a news service, so that you can maintain all of your site news in a database and present it on the Web site. Let’s begin with designing the database.

Plan the database
The first thing you need to do is plan your database. This step is perhaps the most important when adding a new component to your dynamic Web site. When you plan your database, you should spend some serious time thinking about how you are going to design it. Plan for the future! It’s much better to have too many unused fields in your database than to have too few. Think of all the possibilities. If you’re halfway through implementing the PHP code to handle your database and find that you need something else, you’ll have to modify the database and your code. This creates extra work, which wouldn’t be necessary if you had spent the time to design it properly in the first place.

Before we go any further, we need to make a distinction here. MySQL and other similar database servers have a special way of relating to each other. Within the server you can have multiple databases, or groups. Within each database you can have multiple tables. This is how relational databases are designed, and it makes sense if you think about it. Within each database, you can have one or more tables that relate to one another. These tables can connect to each other via special fields called keys, or they can be completely independent of each other. The design is up to you.

Using a piece of paper and a pen is probably the best way to design a table in your database. Create a flow chart to show what components are needed and how they relate to one another. This will help you visualize what you want to accomplish and generally make things much easier.

Once you’ve decided on what your database requires, you must give it a name. Since your site will not be overly complex, you can use one database for the entire site and split up everything into component tables instead of using multiple databases, each with one table inside it. So, for this example, let’s call your database Website. The next thing you need to do is decide on the tables the Website database will hold. Right now, you need just one table to hold your site announcements or news items, so call it news.

Now you need to determine what fields your table will hold. These fields will contain the information you want for each news item. Initially, you’ll want five fields: the name of the person who entered the news item, the title of the news item, the date the item was posted, the body (or text) of the news item, and finally a unique identifier. The unique identifier should exist in every table you create. This makes it easier to track any item in your database, and MySQL makes creating the unique identifier very easy with an auto-incrementing function. Now that you know what fields you need, give each of them a unique name. Let’s call them author, title, date, body, and id, respectively.

Use MySQL to create the database
Now, you need to actually create the database, table, and fields you’ve decided upon. To do so, you need to connect to the MySQL server. For this example, I’ll assume that you have root access to the MySQL server and will be connecting to it via the command line. Keep in mind, however, that just like Linux, MySQL allows you to create users with certain permissions. Therefore, you may opt to use a more secure method of dealing with MySQL, especially if you host many sites and need to give other people access to your MySQL server. Since you really don’t want to give just anyone root access to the server, you’ll need to set up appropriate permissions for each person to whom you’re granting access. Since explaining this process could very well be an article unto itself, I’ll take the easy route and assume you are root.

Also, if you’re creating the database on a local machine, keep in mind that you can also find and use many GUI clients to manipulate the MySQL database. An example of such a GUI client is KSql . However, for the sake of this article, use the MySQL command-line tool to build your first database.

Get to a command-line prompt, either on the console or by opening up an xterm window on your desktop. Connect to the MySQL server by using the following command:
mysql -u root -p

This will prompt you for your root password, and once you’ve entered it, you’ll see the following prompt:
mysql>

The next step is to enter the SQL commands needed to create the database. Enter the following command at the prompt:
CREATE DATABASE Website;

This command creates the Website database. Keep in mind the semicolon at the end of the command. You must end every command with the semicolon because the MySQL prompt can handle stacked commands, just as the Linux command line does. You can type multiple commands at the prompt, and they will be executed in the order written as long as each separate command ends with the semicolon. MySQL should now give you the OK message.

The next step is to change to, or use, the newly created database. Enter the following:
USE Website;

Now you are inside the Website database and are able to create the table. Use the following command to create your table:
CREATE TABLE news (id INT(8) NOT NULL PRIMARY KEY AUTO_INCREMENT,
author VARCHAR(30),
title VARCHAR(50),
date DATE,
body TEXT);


The first thing you should note is that yes, you can span commands across multiple lines. The above command is perfectly valid if you hit [Enter] at the end of each line. MySQL will give you a different kind of prompt, namely a continuation prompt that looks like this:
mysql>
 ->


MySQL will not actually execute the command until it receives the semicolon followed by [Enter].

Secondly, you have defined your table with the above command. You have created your id field, which is an eight-character integer number that cannot be a null character. It is also the primary key for this table and auto-increments with each new entry. You’ve then defined your author and title fields to be of the type variable characters, which basically means that anything can go in the field (letters or numbers). But you’ve restricted them to a certain size, 30 characters and 50 characters, respectively. Next, you defined the date field, which outputs dates in the format YYYY-MM-DD but can take them in the following formats: YY-MM-DD, YYYY-MM-DD, or YYMMDD. And lastly, you’ve defined body (which is of the type text), which means it can consist of any characters and be any length.

And with that, our database and table are created. Now, let’s take a look at how to manually insert a news item into the table. While we’re still in the MySQL server, issue the following command to create your initial entry:
INSERT INTO news (id, author, title, date, body)
VALUES (00000000, ‘Vincent Danen’, ‘Testing news items’, ‘2000-07-30",
‘This is a test of the new news server on this dynamic Web site!’);


Your first news item is now in the database. You’ve inserted news information that corresponds to the columns (or fields) you defined in the table. Recall that the first column is the id field, which we assign the easy number 00000000, which will be auto-incremented. Since this is our first entry, the id field in this entry will be assigned 1. The 00000000 assignment is merely a trigger to auto-increment that column and is not actually inserted into the entry. The next column is the author column, so you’ve provided the name of the author of this news item. The third column is the title field, which you have likewise assigned. The next column is the date, which you inserted in the YYYY-MM-DD format. The final column contains the text of the news item.

If you were to now issue this command:
SELECT * FROM news;

you would see the newly entered news item retrieved from the database. However, this is an extremely tedious way of dealing with entering MySQL data. You’re better off writing a PHP form to enter this information for you, which you’ll do in a moment.

To leave the MySQL server, simply type:
exit

and you will be back at the Linux command line.

Create the news item addition Web page
Let’s create a simple form to add new items to the database so you don’t have to learn that clumsy SQL syntax. This will make life much easier for you. By using this method, you can also have multiple people entering news items in the database without giving out shell access to everyone. If you think about it, you’ll see the definite benefits of this approach.

Let’s write an extremely simple form that inserts the information into the database. This HTML code is very basic and not meant to be pretty. Obviously, you’ll want to adapt it to your own site style and preferences.

Open up your favorite text editor and create a file called newsadd.php in your secured administration directory. (See part three of this series.) You’ll also want to secure the page using the .htaccess file (also discussed in part three).

Click here to read the code.
<? //
This checks to see if we need to add another record.
if (($REQUEST_METHOD=='POST')) {
 // This will catch if someone is trying to submit a blank
 // or incomplete form.
 if ( $date && $author && $body && $title ) {
 $query = "INSERT INTO news ";
 $query .= "(id, author, title, date, body) ";
 $query .= "values(00000000,’$author',’$title','$date','$body')";
 mysql_pconnect("localhost","root","$r_pw")
 or die("Unable to connect to SQL server");
 mysql_select_db("Website") or die("Unable to select database");
 $result = mysql_query($query);
 } else {
 // If they didn't include all the required fields set a variable
 // and keep going.
 $notall = 1;
 }
}
?>
<html>
<head>
<title>Administration: Add News</title>
</head>
<body bgcolor="#ffffff">
<h1>Add new item</h1>

<?
if ($result) {
 echo "<p>News item was successfully added to the database.</p>";
}
if ($notall == 1) {
 echo ‘<p><font color=\"red\">Please answer all fields</font></p>’;
}
?>

<form method="post" action="newsadd.php">
<li> News Date (ie. 1999-01-31):
<input type="text" name="date" size="10" maxlength="10"
<?
if ($notall == 1) {
 echo "value=\"$date\"";
} else {
 echo "value=\"\""; }
?>
>

<li> News Author:
<input type="text" name="author" size="30" maxlength="30"
<? if ($notall == 1) {
 echo "value=\"$author\"";
} else {
 echo "value=\"\"";
}
?>
>

<li> News Title:
<input type="text" name="title" size="50" maxlength="50"
<? if ($notall == 1) {
 echo "value=\"$title\"";
} else {
 echo "value=\"\"";
}
?>
>

<li> News: </li>
<textarea name="body" cols="40" rows="3" wrap="Virtual">
<?
if ($notall == 1) {
 echo "$body";
} else {
 echo "";
}
?></textarea>
<input type="submit" value="Add">
</form>

</body>
</html>

Okay, time to take a little break here. That was a big one. For anyone familiar with HTML, this technique shouldn’t be too hard to figure out. Basically, you’ve just designed a form that asks the user for the date, title, author, and body of a news item. When users click the Add button, they will be returned to the page, and one of two things will happen. Either they will be told that the submission was successful and that the item was added to the database, or they will be told that it was not completely filled out and be prompted to finish it.

The error-checking code is done by PHP. Basically, it checks to make sure that the values of $date, $author, $title, and $body are not null or nonexistent. If they are, it means that the user hasn’t filled out those particular fields. The nice thing is the user doesn’t need to press the Back button in his or her browser. PHP reinserts the values of the variables that were filled out into the form by using the many checks against the $notall variable, which was set at the beginning of the page. This makes the page a little more user-friendly.

The meat of the page is in the first section of the news item addition code, which checks to make sure that everything is filled out, and if it is, it inserts the information into the database. The error-checking code is not foolproof. For example, if someone were to enter Jan 12 00 in the date field, they would receive an SQL error, and not a graceful error as we do with incomplete forms. In order to compensate for errors such as this SQL error, you’d need to write more error-checking code. For simplicity, in this first section of code I’ve also provided the user with an example of how the date field should be filled out.

In this same section of code, you’ll notice the following:
$query = "INSERT INTO news ";
$query .= "(id, author, title, date, body) ";
$query .= "values(00000000,'$author','$title','$date','$body')";


Look familiar? It should; it’s the exact same syntax you used to manually insert your first item in the database. However, in this case you need to assign it to the variable $query. You could easily write the query as one long string, but for the sake of readability, split it up and use the = assignment operator to append it all together. The next part:
mysql_pconnect("localhost","root","$r_pw")
or die("Unable to connect to SQL server");
mysql_select_db("Website") or die("Unable to select database");
$result = mysql_query($query);


is what actually interacts with the MySQL database. In the first line, PHP is making a persistent connection to the server localhost with the user root and the password specified in the variable $r_pw. You can just as easily hard-code your password directly into the page, if you like, but I prefer reading it from a file outside of a harmful directory tree, like /home/httpd/html and its subdirectories. In the next part of this series, we’ll take a look at how to include external files in our pages.

If PHP cannot connect to the server, it will gracefully exit and print “Unable to connect to SQL server” on the Web page. The next line selects the database to use, which in this case is the Web site database we defined earlier. Again, if it’s unable to select the table for some reason, it will exit and generate an error.

Finally, use the mysql_query() function to execute the SQL query you have stored in the variable $query. Assign the return value of that operation to the variable $result, which allows us to determine whether or not your news item was successfully added to the database.

As you can see, you can safely break in and out of PHP code anywhere you like within your HTML code. This is what makes PHP so flexible and popular among many Web designers. The ability to test variables within HTML code and change what HTML code is presented to the user on the fly is one reason why dynamic Web sites are becoming so popular. Not so long ago, designers were writing semicomplex Perl scripts to accomplish the same thing. PHP is faster and easier to use and fits in quite nicely with your regular HTML code.

Conclusion
In this part of our series, I guided you through the creation of your first database that will be used for the dynamic Web site. I also showed you how to manually add information into your database, and examined an even simpler and more secure method of using a Web interface to insert the information.

In the next part of this series, I’ll tell how to create the page that will actually retrieve the information from the database and display it for users to view. I’ll also show you some variations that may interest you, like creating a short list of just the news titles with a link to another dynamic page that displays the actual news contents.

Vincent Danen, a native Canadian in Edmonton, Alberta, is an avid Linux "revolutionary" and a firm believer in the Open Source philosophy. He attempts to contribute to the Linux cause in as many ways as possible, from his Freezer Burn Web site to local advocacy in his hometown. Owner of a Linux consulting firm, Vincent is also the security updates manager for MandrakeSoft, creators of the Linux-Mandrake operating system. Vincent is a certified Linux Administrator by Tekmetrics.com.

The authors and editors have taken care in preparation of the content contained herein, but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for any damages. Always have a verified backup before making any changes.

About Vincent Danen

Vincent Danen works on the Red Hat Security Response Team and lives in Canada. He has been writing about and developing on Linux for over 10 years and is a veteran Mac user.

Editor's Picks