09 7090 ch08 7/16/04 8:45 AM Page 144
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
9
PHP and Databases
P
HP
IS USED TOGETHER WITH A DATABASE SERVER
(DBMS) of some kind, and the
platform (of which the DBMS is part) is usually referred to by an acronym that
incorporates a particular brand of database—for example, LAMP stands for
Linux/Apache/MySQL/PHP.
When it comes to the certification program, however, you are not required to know
how any DBMS in particular works.This is because, in a real-world scenario, you might
find yourself in a situation in which any number of different DBMSs could be used.
Because the goal of the certification program is to test your proficiency in PHP—and
not in a particular DBMS—you will find yourself facing questions that deal with the
best practices that a PHP developer should, in general, know about database program-
ming.
This doesn’t mean that you shouldn’t expect technical, to-the-point questions—they
will just be less based on actual PHP code than on concepts and general knowledge.You
should, nonetheless, expect questions that deal with the basic aspects of the SQL lan-
guage in a way that is DBMS agnostic—and, if you’re used to a particular DBMS, this
might present a bit of a problem because the SQL language is quite limited in its nature
and each specific DBMS uses its own dialect that is often not compatible with other
database systems.
As a result, if you are familiar with databases, you will find this chapter somewhat lim-
ited in its explanation of database concepts and techniques because we are somewhat
constrained by the rules set in place by the certification process. However, you can find a
very large number of excellent resources on creating good databases and managing them,
both dedicated to a specific DBMS and to general techniques. Our goal in this chapter is
to provide you with the basic elements that you are likely to find in your exam.

Escaping
Techniques You’ll Need to Master
n
Creating tables
n
Designing and optimizing indices
n
Inserting and deleting data
n
Selecting data from tables
n
Sorting resultsets
n
Grouping and aggregating data
n
Using transactions
n
Escaping user input
n
Managing dates
“Databasics”
Most modern general-purpose DBMSs belong to a family known as “relational databas-
es.” In a relational DBMS, the information is organized in schemas (or databases), which,
in turn contain zero or more tables. A table, as its name implies, is a container of rows (or
records)—each one of which is composed of one or more columns (or fields).
Generally speaking, each column in a table has a data type—for example, integer or
floating-point number, variable-length character string (VARCHAR), fixed-length char-
acter string (CHAR), and so on. Although they are not part of the SQL-92 standard,
10 7090 ch09 7/16/04 8:42 AM Page 146
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

n
Whenever you write a query that accesses data, try to ensure that your table’s
indices are going to be able to satisfy your selection criteria. For example, if your
search is limited by the contents of columns A, B, and C, all three of them should
be part of a single index for maximum performance.
n
Don’t assume that a query is optimized just because it runs quickly. In reality, it
might be fast only because there is a small amount of data and, even though no
indices are being used, the database server can go through the existing information
without noticeable performance deterioration.
n
Do your homework. Most DMBSs provide a set of tools that can be used to mon-
itor the server’s activity.These often include the ability to view how each query is
being optimized by the server. Spotting potential performance issues is easy when
the DBMS itself is telling you that it can’t find an index that satisfies your needs!
10 7090 ch09 7/16/04 8:42 AM Page 147
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
148
Chapter 9 PHP and Databases
Primary Keys
The columns that are part of an index are called keys.A special type of index uses a key
known as a “primary key.”The primary key is a designated column (or a set of columns)
inside a table whose values must always respect these constraints:
n
The value assigned to the key column (or columns) in any one row must not be
NULL.
n
The value assigned to the key column (or columns) in any one row must be com-
pletely unique within the table.
Primary keys are extremely important whenever you need to uniquely identify a partic-

orphaned records to be created.
10 7090 ch09 7/16/04 8:42 AM Page 148
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
149
Creating Tables or Adding and Removing Rows
Creating Tables or Adding and Removing Rows
Although the exact details of the syntax used to create a new table varies significantly
from one DBMS to another, this operation is always performed by using the
CREATE
TABLE
statement, which usually takes this form:
CREATE TABLE table_name
(
Column1 datatype[,
Column2 datatype[,
...]]
)
It’s important to note that a table must have at least one field because its existence would
be completely meaningless otherwise. Most database systems also implement limits on
the length of each field’s name, as well as the number of fields that can be stored in any
given table (remember that this limit can be circumvented, at least to a certain degree, by
creating multiple tables and referencing them using foreign keys).
Inserting a Row
The
INSERT
statement is used to insert a new row inside a table:
INSERT [INTO] table_name
[(column1[, column2[, column]])]
VALUES
(value1[, value2[, valuen]])

my_table
, in which the value of the
user_name
column is
‘Daniel’
, to be deleted. Naturally, a
FROM
clause can contain a wide-ranging number of
different expressions you can use to determine which information is deleted from a table
with a very fine level of detail—but those go beyond the scope of this chapter. Although
a few basic conditions are common to most database systems, a vast number of these
implement their own custom extensions to the
WHERE
syntax.
Retrieving Information from a Database
The basic tool for retrieving information from a database is the
SELECT
statement:
Select *
From my_table
This is perhaps the most basic type of data selection that you can perform. It extracts all
the values for all the columns from the table called
my_table
.The asterisk indicates that
we want the data from all the columns, whereas the
FROM
clause indicates which table we
want to extract the data from.
Extracting all the columns from a table is, generally speaking, not advisable—even if
you need to use all of them in your scripts.This is because by using the wildcard opera-

example:
Select *
From table1 inner join table2 on table1.id = table2.id
When executing this query, the database will look at the
table1.id = table2.id
con-
dition and only return those rows from both tables where it is satisfied.You might think
that by changing the condition to
table1.id <> table2.id
, you could find all the
rows that appear in one table but not the other. In fact, this causes the DBMS to actually
go through each row of the first table and extract all the rows from the second table
where the
id
column doesn’t have the same value, and then do so for the second row,
and so forth—and you’ll end up with a resultset that contains every row in both tables
many times over.
You can, on the other hand, select all the rows from one of the two tables and only
those of the other that match a given condition using an outer join. For example,
Select *
From table1 left outer join table2 on table1.id = table2.id
This will cause the database system to retrieve all the rows from
table1
and only those
from
table2
where the
id
column has the same value as its counterpart in
table1