The PHP Anthology Volume I, Chapter 3 - PHP and MySQL

--Kevin Yank

In the "old days" of the Internet, most Web pages were nothing more than text files containing HTML. When people surfed to your site, your Web server simply made the file available to their browser, which parsed the contents and rendered something a human being could read. This approach was fine to start with, but as Websites grew and issues such as design and navigation became more important, developers realized that maintaining hundreds of HTML files was going to be a massive headache. To solve this problem, it became popular to separate variable content (articles, news items, etc.) from the static elements of the site—its design and layout.

Using a database as a repository to store variable content, a server side language such as PHP performs the task of fetching the data and placing it within a uniform "framework," the design and layout elements being reused. This means that modifying the overall look and feel of a site can be handled as a separate task from the addition or maintenance of content. Suddenly, running a Website is no longer a task that consumes a developer's every waking hour.

PHP supports all relational databases worth mentioning, including those commonly used in large companies, such as Oracle, IBM's DB2 and Microsoft's SQL Server. The two most noteworthy open source alternatives are PostgreSQL and MySQL. Although PostgreSQL is arguably the better database, in that it supports more of the features that are common to relational databases, MySQL is better supported on Windows, and is a popular choice among Web hosts that provide support for PHP. These factors combine to make PHP and MySQL a very popular combination. This book is geared to the use of MySQL with PHP but it's important to remember that there are alternatives with full support for features such as stored procedures, triggers and constraints, many of which become important for applications with complex data structures.

This chapter covers all the common operations PHP developers have to perform when working with MySQL, from retrieving and modifying data, to searching and backing up a database. The examples focus on using a single table, so no discussion is made of table relationships here. For a full discussion of table relationships, see Kevin Yank's Build Your Own Database Driven Website Using PHP & MySQL (ISBN 0-9579218-1-0), or see an example of them in practice when we deal with user groups in Chapter 1, Access Control.

The examples used here work with a sample database called sitepoint, which contains the following single table:

Example 3.1. articles.sql

CREATE TABLE articles (
 article_id INT(11)       NOT NULL AUTO_INCREMENT,
 title      VARCHAR(255)  NOT NULL DEFAULT '',
 intro      TEXT          NOT NULL,
 body       TEXT          NOT NULL,
 author     VARCHAR(255)  NOT NULL DEFAULT '',
 published  VARCHAR(11)   DEFAULT NULL,
 public     ENUM('0','1') NOT NULL DEFAULT '0',
 PRIMARY KEY (article_id),
 FULLTEXT KEY art_search (title, body, author)
)

A query to construct this table along with some sample data is available in the code archive, contained in the file sql/articles.sql. The table will be used for examples in later chapters of the book.