http://www.developer.com/

Back to article

Building Database-Driven Applications with PHP and MySQL: Part II


August 25, 2003

In the previous article you were introduced to the basics of PHP and MySQL. We explored some of the functions PHP provides for working with MySQL and saw how they could be used to retrieve, edit, add and delete information from a database.

The functions covered in the last article are at the core of creating even the most complex database driven web site.

In this article, you will learn how to pull those functions together to create a fully functional database-based news application. This is an ideal "first script" for three reasons: first, it is relatively simple to write; second, it encapsulates most of the primary tasks involved in writing any database application; and third, it can be easily adapted for other uses.

When complete, the news application will be able to display a list of news articles to general users and include a simple administrative interface for adding and managing items.

The Application – An Overview

This type of script can be broken into two primary groups of functions: those responsible for displaying the news and those responsible for managing them. That division is the basis of the structure for the entire application. To keep it simple for now, this script will not include password protection on the administration, so we are left with an architecture that looks something like this:

Global File
- global.php (database connection variables and functions)

Back End
- /admin/index.php (displays existing news articles and links to add/edit articles)
- /admin/article.php (handles additions and edits)

Front End
- index.php (displays news items)


MySQL Table Structure

Before writing any code, it is generally a good idea to create the table structures needed for the application. This requires some planning and foresight. When an application is relatively small and utilizes a simple database design, having to alter existing tables because you miscalculated something is not a disaster. On the other hand, if there are dozens of tables, an initial mistake in design can translate into costly time spent re-arranging the database and rewriting queries in any existing code.

For this application, designing the table structure is fairly painless. Since all we're storing are articles, we can use a single table in which each row holds a separate article. The easiest way to decide which columns should be included is to break down the elements of each article.

Every article should have a unique numeric id which make it easy to access individual articles for display, editing, and deleting. We also want to store a title for each news item and the date it was posted. Additionally, we need to include a field for the actual news body and the name of the person who posted. This adds up to a total of five fields for the articles table.

We also need to consider which MySQL field types are best for the type of data we are storing in each column.

MySQL has a sepecial property which can be used on numeric fields to make them automatically increment each time a new record is added. This is ideal for creating our unique identifier. Since the id number will increase by one each time and support for decimals is not required, we can use MySQL's integer data type. This field will also be set as the table's primary id, and we will define it as auto_increment.

The title and author fields will likely be relatively short, so we can safely store them using the VARCHAR type. The VARCHAR field type is limited to a maximum of 255 characters, so it cannot be used for the field storing the article body. For that, we turn to one of MySQL's large text handling field types - TEXT.

For the date field there are numerous options. MySQL has several different date/time field types including DATE (stores date in yyyy-mm-dd format), DATETIME (stores date and time information) and TIMESTAMP (automatically inserts the current time when data is inserted. Optionally, automatically updates when an UPDATE query is performed). We could also use PHP to generate a formatted date or a UNIX time stamp which could then be inserted into a VARCHAR field.

In many situations, one option is as good as the other. But for this application, we will use the last method because it is easy to use PHP's date function to format the date after it has been retrieved from the database and easy to sort the date within the database.

With all this in mind, the queries to generate the database and table can be written and run:

CREATE DATATBASE news;

CREATE TABLE news (
id int(10) NOT NULL auto_increment,
title varchar(255) default NULL,
date varchar(10) default NULL,
author varchar(255) default NULL,
article text,
PRIMARY KEY (`id`)
);


The Global Configuration File – global.php

Good coding practice says that variables and functions which are used multiple times in an application should be consolidated into a separate file and included as needed.

Since all of the scripts in this application require a connection to the database, it makes sense to isolate the MySQL database connection variables and functions in this way. By doing this, we also make the script more portable. If the MySQL server or login information ever changes, the script only needs to be updated in one location to affect the entire application. Take a look at the complete file:

global.php:

<?php

//set database connection variables
$hostname='hostname';
$username='username';
$password='password';
$database='news';

//establish database connection
$connect = mysql_connect($hostname,$username,$password);

//display error if connection fails
if ($connect==FALSE) {
   print 'Unable to connect to database: '.mysql_error();
   exit;
}

//select database
mysql_select_db($database);
?>

After assigning variables to store the MySQL server, login and database information, the connection is established using mysql_connect();

There is always the possibility that the connection to the database server may fail, so it is a good idea to include some error catching. Depending on how PHP's error reporting option is set, if a MySQL query fails, users may be presented with an error message for the initial connection and any following queries, or they may see an entirely blank screen.

In this case, we assign the results of the mysql_connection statement to $connect, and then check its value. FALSE, we display a friendly error message and exit the script; TRUE, the script continues processing.

Because this file will be included at the beginning of all the other scripts, PHP will not try to execute any other queries if the original connection has failed. It is unlikely that any other queries will result in an error if they work to begin with, so this is a quick and dirty method of handling potential MySQL errors. It is not, however, the most error proof method.

Adding and Editing Articles – article.php

There needs to be actual data in the database before we try to display anything, so a good place to start is with programming the file that will allow new articles to be added (and existing ones edited).

Fundamentally, adding and editing a news article is pretty much the same. The fields in the HTML forms for both processes would be identical, as would any validation on the incoming data. The primary differences between adding and editing occur at two different points:

1. After the form has been submitted, when the information is actually entered into the database
2. In the edit screen, when the form must be pre-filled with the information from the article that is being edited.

Because of these differences, the first inclination might be to create two separate scripts to handle each task, including two separate forms. But by folding the code for both adding and editing articles into a single script and using a single form, chunks of redundant PHP and HTML scripting can be eliminated. The differences outlined above can be handled with relative ease just by using a series of well-placed if statements.

Here is the complete script.

/admin/articles.php:

<?php

//include global file, establishes connection to database
include '../global.php';

//check to see if the form has been submitted
if (isset($_POST['submit'])) {

   //perform any error checking on incoming data
   
   if (!empty($_POST['id'])) {
      //id is set, editing an existing article

      //insert data into database
      mysql_query("UPDATE news SET title='{$_POST['title']}',author='{$_POST['author']}',article='{$_POST['article']}' WHERE id='{$_POST['id']}'");
      $message = 'Article Successfully Edited';
      
   } else {
      //id isn't set, adding new article

      //get today's date, unix timestamp format
      $date=time();

      //insert data into database
      mysql_query("INSERT INTO news (id,title,date,author,article) VALUES('','{$_POST['title']}','$date','{$_POST['author']}','{$_POST['article']}')");
      $message = 'New Article Successfully Added';
   }
}

//get data to prefill form if we're editing an existing entry
if (!empty($_REQUEST['id'])) {
   $result = mysql_query("SELECT id, title, author, article FROM news WHERE id='{$_REQUEST['id']}'");
   list($id,$title,$author,$article)=mysql_fetch_array($result);
}

//output html form
?>


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Articles</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
td,body {
   font-family: Arial, Helvetica, sans-serif;
   font-size: 12px;
}
-->
</style>
</head>

<body>

<?if (isset($message)) print $message?>
<br><br>

<form method="post" action="<?=$_SERVER['PHP_SELF']?>">
<table width="400" border="0" cellspacing="0" cellpadding="3">
<tr>
<td width="99">Headline:</td>
<td width="301"><input name="title" type="text" value="<?if (isset($title)) print $title; ?>"></td>
</tr>
<tr>
<td>Author:</td>
<td><input name="author" type="text" value="<?if (isset($author)) print $author; ?>"></td>
</tr>
<tr>
<td>Article:</td>
<td><textarea name="article" wrap="VIRTUAL"><?if (isset($article)) print $article; ?></textarea></td>
</tr>
<tr>
<td> </td>
<td>
   <input type="hidden" name="id" value="<?if (isset($_REQUEST['id'])) print $_REQUEST['id']?>">
   <input type="submit" name="submit" value="submit"></td>
</tr>
</table></form>
</body>
</html>

Let's take a look at each part in detail.

Whether adding or editing an article, the HTML form will always be displayed first, since it is not encapsulated in any If Statements. It can be considered the default state or action of the script.

By placing an If Statement before the form, the execution of the script can easily be diverted, depending on the user's actions. In this case, the If Statement is used to check whether or not the form has been submitted.

If it has, we next need to determine whether an existing article is being edited, or a new one added. When an existing article is being edited, article.php will be called with a link that includes the primary ID number associated with that article. That ID number will be stored in a hidden field in the form (notice that it is referenced through the $_REQUEST array, since the id could be coming from the query string or the form), and passed along with the rest of the data when the form is submitted. To check and see if we're editing an existing article, we need only see if that hidden field is empty or contains a value.

At this point, the script either inserts the new entry into the database, or updates an existing one associated with the id number.

Once complete, a thank you message is generated and displayed. The form is outputted again, ready to add another article or make further changes to the selected one.

Pre-Filling the Form for Editing

To edit an article, articles.php is called with the article's id number appended in the query string. In order to pre-fill the form we need to retrieve the associated row of data from the table. First, we check to make sure an id number is set, and then we query the database. Then, we query the database for the row matching the id number. Since the id number is unique and we can expect only one row to be returned, there is no need for a While Loop to cycle through the results of the query. We use the list function to assign variable names to each element of the result array.

This entire chunk of code is placed right before the actual form is outputted.

Notes on Security and Data Validation

Web-based scripts are entryways to the server, so security should always be at the forefront of your mind when programming. There is always the potential for malicious individuals to exploit a weakness in a script to disable the running application, mess up the display, or in a worst case scenario, gain access to files on the server.

Accepting external data (be it from the query string, a form, or any other means) is like opening your door to a crowd of strangers without knowing what they're bringing into your home; it is the most vulnerable point of access.

As a programmer, you don't always know who's coming in, but you can at least check their pockets on the way in. Even for an application like this, where the only input will come from yourself or another administrator, it is a good idea to get into the habit of validating incoming data.

Validation is a two-pronged process of first rending users' input harmless and then forcing it to conform to any additional guidelines you establish.

If you were collecting an email address, you might want to check to make sure it was a valid format. For file uploads, you would need to make sure it was of allowed file types and under a certain file size. In this script, likely validation would include making sure that all the fields are filled in and that the fields were stripped of any HTML or PHP.

Because all of the incoming form data is stored in a single array, $_POST, it is easy to validate each field simply by looping through the array. For example, this small function checks each field to make sure it has been filled out:

<?php
function checkmissing() {
 $missing=array();
 foreach ($_POST as $name=>$val) {
  if ($val=="") {
   $missing[]=$name;
  }
  if (count($missing)>0) {
   return $missing;
  } else {
   return FALSE;
  }
 }
}
?>

$_POST is a Super Global array, so it is automatically available within the local scope of any functions. Within this function, a Foreach Loop is used to check each field to make sure it is not empty. If it is, the field name is added to an array. The function returns the array if there are missing values (which can then be outputted to let the user know they've left out some information) or FALSE if everything has been filled in. The results of the function could be used in an If Statement to determine whether the form should be redisplayed with errors, or processing should continue.

A similar method of looping through the $_POST array can be used to strip any code from incoming data by way of PHP's strip_tags function.

<?
foreach($_POST as $name=>$val) {
   $_POST[$name]=strip_tags($val);
}
?>

strip_tags also accepts an optional second argument which allows certain tags to be specified as "allowed". This is convenient in instances where you may wish to allow some basic formatting tags.

Handling Quotes

Remember that quotes within a string that are the same as those used to terminate the same string must be escaped. This is done so that the parser does not see a matching quote and think that the string has been terminated.

So what happens when an article containing text like "That's a great idea!" is submitted and included in a query statement? Without escaping the quotes, the query will fail and MySQL will throw back an error like: "You have an error in your SQL syntax near 's a great'".

There are two ways to escape quotes. The first is to do it manually using addslashes() on each value within your query. The second way relies on a PHP configuration setting: magic_quotes_gpc. The setting automatically escapes " (double quotes), ' (single quotes) and \ (backslashes) in all incoming data. Most programmers have a love/hate relationship with magic_quotes_gpc. Superficially, having it on is convenient because it removes the burden of needing to add slashes manually when you are inserting information into the database. But there are a couple of downsides. First, quotes will have to be stripped off when any of the $_REQUEST variables are being outputted to the browser directly; second, though most servers do have magic_quotes_gpc turned on, there is a minority that does not. not all servers have magic_quotes_gpc turned on. If you program with the assumption that it will always be on, then you may run into trouble later if your script is ever distributed or moved to a different server. The sheer number of scripts that have been distributed without taking this into consideration are staggering.

Generally, if you plan on ever distributing your scripts, or forsee moving them to a different server, the rule of thumb here is to never assume that magic_quotes_gpc will be on. PHP has a built in function that checks to find the state of magic_quotes; it returns TRUE when its on, FALSE when its off. Using that, you can easily write a quick function that checks to see if the magic_quotes are on and remove slashes if it is. Included in the global.php file, it will ensure that your script works regardless of the server's configuration settings.

<?
function stripQuotes(&$array){

   //Run a stripslashes on all get/post/cookie variables if magic quotes is turned on.
   if (get_magic_quotes_gpc()){
      foreach ($array as $key=>$val){

         //if the array value is an array, run the function again
         if (is_array($val)){
            stripQuotes($val);
            $array[$key]=$val;
         } else {
            $array[$key]=stripslashes($val);
         }
      }
   }
}
stripQuotes($_REQUEST);
?>

When you are sure that slashes are being removed from incoming data, all the time, you can change your queries to add slashes to your data. For example, the query that was used to add a new article would be changed to look like this:

mysql_query("UPDATE news SET title='".addslashes($_POST['title'])."',author='".addslashes($_POST['author'])."',article='".addslashes($_POST['article'])."' WHERE id='{$_POST['id']}'");

 

News Article Listing – index.php

index.php is the main administration page. In addition to providing a link to the add article form, this page also lists all of the existing articles in the database. For each one, an edit and delete link is displayed.

admin/index.php:

<?php
//include global file, establishes connection to database
include '../global.php';

//remove article
if (isset($_GET['id'])) {
   mysql_query("DELETE FROM news WHERE id='{$_GET['id']}'");
   $message = "Article Successfully Deleted";
}

$result = mysql_query("SELECT id,title FROM news");
$row = mysql_fetch_array($result);

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Add Article</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
td,body {
   font-family: Arial, Helvetica, sans-serif;
   font-size: 12px;
}
-->
</style>
</head>
<body>

<?if (isset($message)) print $message?>
<br><br>

<b><a href="articles.php">Add new Article</a></b><br><br>
<table width="90%" cellpadding="3" cellspacing="0" border="0">

<?php
while($row=mysql_fetch_array($result)) {
   print '<tr><td>'.$row['title'].'</td><td><a href="articles.php?id='.$row['id'].'">edit</a> | <a href="'.$_SERVER['PHP_SELF'].'?id='.$row['id'].'">delete</a></td></tr>';
}
?>

</table>
</body>
</html>

The first line of the script is a call to include the global.php file. With a connection to the database established, we can execute the query to retrieve all the entries. To loop through them, we use a while loop and the mysql_fetch_array() function. Since each itineration of the loop handles just one row of the results, any html within the loop will affect that particular row, be repeated for the next and so on. In this way, it is easy to generate edit and delete links for each entry.

Notice the delete link points back to the running script. Since deleting an article is a relatively simple and small function, it can easily be included in the same script. To remove the article, we simply query the database and remove the row with the matching id.

The Front End – Article Display

The final part of this application is the front end. As is the case with most applications, building this part is significantly less involved than the back end. Most of the programming involves grabbing the news items from the database. The rest involves designing how the information should be laid out. Take a look at the script:

index.php:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>News Articles</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
td,body {
   font-family: Arial, Helvetica, sans-serif;
   font-size: 12px;
}
-->
</style>
</head>
<body>

<?php
//include global file, establishes connection to database
include 'global.php';

$result = mysql_query("SELECT * FROM news ORDER BY date DESC");

//output each article
while($row=mysql_fetch_array($result)) {
   print '<h3>'.$row['title'].' - '.date("F j, Y", $row['date']).'</h3><p><b>Author:</b> '.$row['author'].'</p><p>'.nl2br($row['article']).'</p>';
}
?>

</table>
</body>
</html>

In many respects, this script is identical to the administrative article listing. Here, too, we are retrieving all of the articles from the database and outputting them. This time though, all the columns in the table are retrieved. We also add an ORDER BY clause to the query so the latest article is listed first; MySQL will sort on the date column here.

Within the loop, there is some additional formatting of various fields. Because the date was stored as a Unix timestamp, it needs to be converted to something that's understandable. PHP's date() function is used to change it to "Day Month, Year" format. We also run nl2br() on the article body, so that any line breaks entered when the article was created are preserved as HTML <br> tags.


Finally

Whether it is a guestbook for a small site or a shopping cart with a 500 product inventory, every dynamic application is composed of the same basic parts. Admin pages, which are typically password protected, allow content to be added to a database, edited and deleted and a front end accessible to all users organizes, which assembles and displays the content in a meaningful way.

In this article you've learned how to use the basics of PHP/MySQL interaction to develop a complete application which incorporates all of these elements.

The next article will revisit the news application; you'll learn how to expand it using some of MySQL more advanced features.

Stay Tuned!

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date