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.
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
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 unique? Often the PHP code that
uses a table like this will first do a
SELECT to find the maximum value of the ID
field, and then create a new record with that value plus 1. But that
takes an extra SQL statement and assumes the PHP developer remembers
to take this step. It's much better to let the database handle this (rather routine)
Example 5-2. Adding an auto-incrementing ID field
DROP TABLE IF EXISTS author; CREATE TABLE author ( id INT NOT NULL AUTO_INCREMENT, name TEXT, PRIMARY KEY( ...