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:

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:

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:

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.

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

By submitting your information, you agree that developer.com may send you developer offers via email, phone and text message, as well as email offers about other products and services that developer believes may be of interest to you. developer will process your information in accordance with the Quinstreet Privacy Policy.