Databases are central to many
web applications. A database can hold almost any collection of information you
may want to search and update, such as a user list, a product catalog, or recent
headlines. One reason why PHP is such a great web programming language is its
extensive database support. PHP can interact with (at last count) 17 different
databases, some relational and some not. The relational databases it can talk to
are DB++, FrontBase, Informix, Interbase, Ingres II, Microsoft SQL Server, mSQL,
MySQL, Oracle, Ovrimos SQL Server, PostgreSQL, SESAM, and Sybase. The nonrelational databases it can talk
to are dBase, filePro, HyperWave, and the DBM family of flat-file databases. It also has ODBC support, so even if your favorite database isn't in
the list, as long as it supports ODBC, you can use it with PHP.
If your data
storage needs are simple and you don't need to serve many users, you may be able
to use a plaintext file as a makeshift database. This is
discussed in Section
10.2. Text files require no special database software but are appropriate
only for lightly used, basic applications. A text file can't handle structured
data well; if your data changes a lot, it's inefficient to store it in a plain
file instead of a database.
DBM flat-file databases, discussed in Section
10.3, offer more robustness and efficiency than flat files but still limit
the structure of your data to key/value pairs. They scale better than plaintext
files, especially for read-only (or read-almost-always) data.
PHP really shines, though, when paired with a SQL database. This combination is used for most of the
recipes in this chapter. SQL databases can be complicated, but they are
extremely powerful. To use PHP with a particular SQL database, PHP must be
explicitly told to include support for that database when it is compiled. If PHP
is built to support dynamic module loading, the database support can also be
built as a dynamic module.
Many SQL examples in this chapter use a table of information
about Zodiac signs. The table's structure is:
CREATE TABLE zodiac ( id INT UNSIGNED NOT NULL, sign CHAR(11), symbol CHAR(13), planet CHAR(7), element CHAR(5), start_month TINYINT, start_day TINYINT, end_month TINYINT, end_day TINYINT, PRIMARY KEY(id) );
And the data in the table is:
INSERT INTO zodiac VALUES (1,'Aries','Ram','Mars','fire',3,21,4,19); INSERT INTO zodiac VALUES (2,'Taurus','Bull','Venus','earth',4,20,5,20); INSERT INTO zodiac VALUES (3,'Gemini','Twins','Mercury','air',5,21,6,21); INSERT INTO zodiac VALUES (4,'Cancer','Crab','Moon','water',6,22,7,22); INSERT INTO zodiac VALUES (5,'Leo','Lion','Sun','fire',7,23,8,22); INSERT INTO zodiac VALUES (6,'Virgo','Virgin','Mercury','earth',8,23,9,22); INSERT INTO zodiac VALUES (7,'Libra','Scales','Venus','air',9,23,10,23); INSERT INTO zodiac VALUES (8,'Scorpio','Scorpion','Mars','water',20,24,11,21); INSERT INTO zodiac VALUES (9,'Sagittarius','Archer','Jupiter','fire',11,22,12,21); INSERT INTO zodiac VALUES (10,'Capricorn','Goat','Saturn','earth',12,22,1,19); INSERT INTO zodiac VALUES (11,'Aquarius','Water Carrier','Uranus','air',1,20,2,18); INSERT INTO zodiac VALUES (12,'Pisces','Fishes','Neptune','water',2,19,3,20);
The specific functions required to talk
to the database differ with each database, but each follows a similar pattern.
Connecting to the database returns a database connection handle. You use the connection handle
to create statement handles, which are associated with
particular queries. A query statement handle then gets the results of that
query.
if (! $dbh = OCILogon('david', 'foo!bar','ORAINST')) {
die("Can't connect: ".OCIError());
}
if (! $sth = OCIParse($dbh,'SELECT * FROM zodiac')) {
die("Can't parse query: ".OCIError());
}
if (! OCIExecute($sth)) {
die("Can't execute query: ".OCIError());
}
$cols = OCINumCols($sth);
while (OCIFetch($sth)) {
for ($i = 1; $i <= $cols; $i++) {
print OCIResult($sth,$i);
print " ";
}
print "\n";
}
The OCILogin( ) function connects to a given Oracle instance with a
username and password. You can leave out the third argument (the instance) if
the environment variable
ORACLE_SID is set to the desired Oracle instance. A statement handle is
returned from OCIParse( ) , and OCIExecute( ) runs the query. Each time
OCIFetch( ) is called, the next row in the
result is retrieved into a result buffer. The value of a particular column of
the current row in the result buffer is retrieved by OCIResult( ).
if (! $dbh = pg_connect('dbname=test user=david password=foo!bar')) {
die("Can't connect: ".pg_errormessage());
}
if (! $sth = pg_exec($dbh,'SELECT * FROM zodiac')) {
die("Can't execute query: ".pg_errormessage());
}
for ($i = 0, $j = pg_numrows($sth); $i < $j; $i++) {
$ar = pg_fetch_row($sth,$i);
foreach ($ar as $col) {
print "$col ";
}
print "\n";
}
In this case, pg_connect( )
connects to PostgreSQL using the provided database name, user, and password. The
query is run by pg_exec( ). There's no need for
a separate parse and execute step as with Oracle. Because pg_fetch_row(
) retrieves a specific row from the result set into
an array, you loop over all the rows (using pg_numrows( ) to get the total number of rows) and print out each
element in the array.
if (! $dbh = mysql_connect('localhost','david','foo!bar')) {
die("Can't connect: ".mysql_error());
}
mysql_select_db('test');
if (! $sth = mysql_query('SELECT * FROM zodiac')) {
die("Can't execute query: ".mysql_error());
}
while ($ar = mysql_fetch_row($sth)) {
foreach ($ar as $col) {
print "$col ";
}
print "\n";
}
First, mysql_connect( )
returns a database handle using the provided hostname, username, and password.
You then use mysql_select_db( ) to indicate
which database to use. The query is executed by mysql_query( ). The mysql_fetch_row( )
function retrieves the next row in the result set and NULL when there
are no more rows; use a while loop to retrieve all the rows.
Each example prints out all the data in the zodiac table, one
row per line, with spaces between each field, as shown here:
Aries Ram Mars fire 3 21 4 19 Taurus Bull Venus earth 4 20 5 20 Gemini Twins Mercury air 5 21 6 21 Cancer Crab Moon water 6 22 7 22 Leo Lion Sun fire 7 23 8 22 Virgo Virgin Mercury earth 8 23 9 22 Libra Scales Venus air 9 23 10 23 Scorpio Scorpion Mars water 20 24 11 21 Sagittarius Archer Jupiter fire 11 22 12 21 Capricorn Goat Saturn earth 12 22 1 19 Aquarius Water Carrier Uranus air 1 20 2 18 Pisces Fishes Neptune water 2 19 3 20
Section
10.5 through Section
10.9 cover the basics of sending queries to the database and getting the
results back, as well as using queries that change the data in the database.
There are a
number of options and optimizations for each database PHP supports. Most
database interfaces support persistent connections with separate connection
functions. In the previous three examples, you would use OCIPLogon( ) ,
pg_pconnect( ), and mysql_pconnect( ) for persistent instead
of single-request connections.
If you require a database-specific set of functions, the PHP
online manual section for each database has many useful tips for proper
configuration and use. If you can, use a database abstraction layer instead.
Starting with Section
10.4, all the SQL examples use the PEAR DB database abstraction layer, which minimizes the amount of code that
has to change to make the examples work on different databases. Here's code that
can display all the rows in the zodiac table using DB and MySQL:
require 'DB.php';
$dbh = DB::connect('mysql://david:foo!bar@localhost/test');
$sth = $dbh->query('SELECT * FROM zodiac');
while ($row = $sth->fetchRow()) {
print join(' ',$row)."\n";
}
The only thing that needs to change to make this code work on
another database is the argument passed to DB::connect( ) , which specifies what database to
connect to. However, a database abstraction layer doesn't make SQL completely portable. Each
database vendor generally has custom SQL extensions that enable handy features
on one database and don't work at all on another database.
While it's possible to write SQL that works on different
databases with a minimum of changes, tuning a database for speed and efficiency
is not portable. Having portable database interactions can be a useful goal, but
it needs to be balanced with the likelihood of your code being used with
multiple databases. If you're writing code for wide distribution, working with
many databases is a plus. If your code is an internal project, however, you
probably don't need to be as concerned with database independence.
Whatever database you're using, you're probably going to be
capturing information from HTML form fields and storing that information in the
database. Some characters, such
as the apostrophe and backslash, have special meaning in SQL, so you have to be
careful if your form data contains those characters. PHP has a feature called "magic quotes" to make this easier.
When the configuration setting magic_quotes_gpc is on,
variables coming from GET requests, POST requests, and
cookies have single quotes, double quotes, backslashes, and nulls escaped with a
backslash. You can also turn on magic_quotes_runtime to automatically
escape quotes, backslashes, and nulls from external sources such as database
queries or text files. For example, if magic_quotes_runtime is
on, and you read a file into an array with file( ), the
special characters in that array are backslash-escaped.
For example, if $_REQUESTS['excuse'] is "Ferris wasn't
sick," and magic_quotes_gpc is on, this query executes
successfully:
$dbh->query("INSERT INTO excuses (truth) VALUES ('" . $_REQUESTS['excuse'] . ')');
Without the magic quotes, the apostrophe in "wasn't" signals
the end of the string to the database, and the query produces a syntax error. To
instruct magic_quotes_gpc and magic_quotes_runtime to escape
single quotes with another single quote instead of a backslash, set
magic_quotes_sybase to on. Section
10.10 discusses escaping special characters in queries. General debugging
techniques you can use to handle errors resulting from database queries are
covered in Section
10.11.
The remaining recipes cover database tasks that are more
involved than just simple queries. Section
10.12 shows how to automatically generate unique ID values you can use as
record identifiers. Section
10.13 covers building queries at runtime from a list of fields. This makes
it easier to manage INSERT and UPDATE queries that involve a
lot of columns. Section
10.14 demonstrates how to display links that let you page through a result
set, displaying a few records on each page. To speed up your database access,
you can cache queries and their results, as explained in Section
10.15.
