Book HomePHP CookbookSearch this book

Chapter 10. Database Access

Contents:

Introduction
Using Text-File Databases
Using DBM Databases
Connecting to a SQL Database
Querying a SQL Database
Retrieving Rows Without a Loop
Modifying Data in a SQL Database
Repeating Queries Efficiently
Finding the Number of Rows Returned by a Query
Escaping Quotes
Logging Debugging Information and Errors
Assigning Unique ID Values Automatically
Building Queries Programmatically
Making Paginated Links for a Series of Records
Caching Queries and Results
Program: Storing a Threaded Message Board

10.1. Introduction

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 Recipe 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 Recipe 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.

This example retrieves all the rows from the zodiac table with Oracle, using the OCI8 interface:

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( ).

Here's the same example using PostgreSQL:

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.

Here's the same exercise with MySQL:

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 

Recipe 10.5 through Recipe 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 Recipe 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. Recipe 10.10 discusses escaping special characters in queries. General debugging techniques you can use to handle errors resulting from database queries are covered in Recipe 10.11.

The remaining recipes cover database tasks that are more involved than just simple queries. Recipe 10.12 shows how to automatically generate unique ID values you can use as record identifiers. Recipe 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. Recipe 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 Recipe 10.15.



Library Navigation Links

Copyright © 2003 O'Reilly & Associates. All rights reserved.