PHP Hacks

Chapter 5. Databases and XML

Hacks 34–50: Introduction

At the core of most PHP applications is the database, and in most cases that database is MySQL. This chapter has a variety of hacks to help you develop code for database access and for your work with XML. In particular, you should check out the dynamic database object hack, which provides a single class that will talk to any database. Add to that the code generation hacks, which will help automate your database access code from an XML representation of the database schema, and PHP and databases are going to be a piece of cake!

Design Better SQL Schemas

Most PHP applications use an SQL database. Here are some hints to help you avoid common problems.

PHP applications usually use MySQL databases for the back end. I’ve worked on a bunch of my own applications, as well as with open source application databases and some commercial ones. In my travels, I have seen a few common problems appear repeatedly; here are a few of those problems, along with easy solutions.

Bad Primary Keys

To find a unique record in a database table, you need a primary key. This is usually a unique, nonrepeating integer that starts at 1. All databases have the ability to handle this for you, but it seems that some engineers aren’t aware of it.

Take the simple schema in Example 5-1. You have an author table with an id and a name.

Example 5-1. SQL without a primary key

DROP TABLE IF EXISTS author;
CREATE TABLE author (
		id INT,
		name TEXT
		);

But who ensures that the ID is ...