Building Database-Driven Applications with PHP and MySQL: Part IIIn 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.