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

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

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!

Latest Posts

Related Stories