Accessing Databases with DB

This chapter discusses the database abstraction layer PEAR DB. This package supplies a standard set of functions for talking to many different kinds of databases (from Essential PHP Modules, Extensions, Tools, by David Sklar, 2004, Apress, ISBN: 1590592808).

phpPEAR DB provides a consistent set of methods for using a relational database no matter what database you’re using. It supports the following PHP database extensions: FrontBase, Informix, InterBase, Microsoft SQL Server, mSQL, ODBC, MySQL, Sybase, OCI8, and PostgreSQL. A driver that supports new features in version 4 of MySQL is also in the works. This chapter covers DB version 1.4.

Exploring a Simple DB Example

Retrieving a result and displaying it in a table with DB looks like this:

// Load the DB code
require ‘DB.php’;

// Connect to the database
$dbh = DB::connect(‘mysql://user:password@host/database’);

// Send a SELECT query to the database
$sth = $dbh->query(‘SELECT flavor, price, calories FROM ice_cream’);

// Check if any rows were returned
if ($sth->numRows()) {
     print “<table>”; 
     print “<tr><th>Ice Cream Flavor</th><th>Price per Serving</th><th>Calories per Serving</th></tr>”;
     // Retrieve each row 
     while ($row = $sth->fetchRow()) {
           // And print out the elements in the row
           print “<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2</td></tr>n";
     }
     print "</table>";
} else {
     print "No results";
}

DB::connect() is a static class method that returns an object. You interact with the database by calling methods on this object. In this example, the object is assigned to the variable $dbh, which stands for database handle. The argument to DB::connect() is a Data Source Name (DSN). DSNs are explained in the next section.

Next, you send a query to the database server with the $dbh->query() method. This method returns a statement handle that is assigned to $sth. This variable is a DB_Result object with methods that return information about the retrieved rows. The $sth->numRows() method returns the number of retrieved rows. In this example, it’s used to determine whether to print the retrieved data or just a message saying that no data was retrieved. The “Query Information” section explains methods such as numRows() that provide information about a query.

The $sth->fetchRow() method returns an array that contains one row of data retrieved from the database. The statement handle maintains an internal counter of what the “next” row to return is, so the first time you call fetchRow(), you get the first row retrieved from the database; the second time you call fetchRow(), you get the second row, and so on. After all retrieved rows have been returned, fetchRow() returns NULL instead of a result array. This makes it easy to use in a while() loop as the example does.

The fetchRow() method returns an indexed array with retrieved data. The first field requested by the SELECT query is in element 0 of the array, the second field in element 1, and so on. In this example, $row[0] is the value of the flavor column in each row, $row[1] is the price column, and $row[2] is the calories column. You can also retrieve rows as associative arrays or objects. The “Sending Queries and Retrieving Results” section discusses fetchRow() in more detail.

This chapter is from Essential PHP Modules, Extensions, Tools, by David Sklar (Apress, 2004, ISBN: 1590592808). Check it out at your favorite bookstore today.

Buy this book now.

{mospagebreak title=Introducing DSNs}

Introducing DSNs

As shown in the previous example, the argument to DB::connect() tells DB about the database to which you want to connect. This argument is a DSN, which has the following form:

databasetype://username:password@hostname/databasename

databasetype is the type of database you’re connecting to: MySQL, Oracle, ODBC, Sybase, and so on. Table 1-1 shows the acceptable databasetype values.

databasetype Database
fbsql FrontBase
ibase InterBase
ifx Informix
mssql Microsoft SQL Server
msql mSQL
mysql MySQL
oci8 Oracle 7, Oracle 8, Oracle 8i
odbc ODBC
pgsql PostgreSQL
sybase Sybase

The username and password components of a DSN are the username and password necessary for connecting to the database server, and the hostname component is the IP address or host name of the machine on which the database server is running. The databasename component is the name of the individual database to access.

If the database server is running on the same machine as your Web server, you can connect to it by specifying localhost as the host name or by using a slightly different syntax to specify a Unix socket to connect to instead:

$dbh = DB::connect(‘mysql://user:password@unix(socketpath)/database’);

For example, if the filename of the Unix socket you want to use is /tmp/mysql.sock, your DSN would look like this:

$dbh = DB::connect(‘mysql://user:password@unix(/tmp/mysql.sock)/database’);

Sending Queries and Retrieving Results

As you saw in the “Exploring a Simple DB Example” section, the DB::query() method sends queries to the database. These can be SELECT queries that retrieve data from the database:

$sth = $dbh->query(‘SELECT * FROM ice_cream’);

They can be INSERT, UPDATE, or DELETE queries that alter data in the database:

$res = $dbh->query(“INSERT INTO ice_cream (flavor, price)
                    VALUES (‘Chocolate’,4.50)”);
$res = $dbh->query(“UPDATE ice_cream SET price = 5.95 
                    WHERE flavor LIKE ‘Vanilla’”);
$res = $dbh->query(“DELETE FROM ice_cream WHERE price 
       < 2.00″);

They can be queries that create, alter, or drop tables:

$res = $dbh->query(‘CREATE TABLE ice_cream (flavor VARCHAR
                    (255),price DECIMAL(6,2), calories
                    INT)’);
$res = $dbh->query(‘ALTER TABLE ice_cream ADD flavor_id INT
                    UNSIGNED NOT NULL’);
$res = $dbh->query(‘DROP TABLE ice_cream’);

The query() method sends to the database whatever SQL you specify. If you specify an SQL query, then query() returns a DB_Result object that provides access to the returned data. The DB_Result::fetchRow() method returns one row from the retrieved data:

$sth = $dbh->query(‘SELECT flavor,price FROM ice_cream’);
$row = $sth->fetchRow();
// Prints out the first retrieved row
print “Flavor: $row[0], Price: $row[1]n”;
// Prints out the second retrieved row
$row = $sth->fetchRow();
print “Flavor: $row[0], Price: $row[1]n”;

Each time you call fetchRow(), you get the next retrieved row. The fetchRow() method returns NULL when there are no more rows available. The fetchInto() method is similar to fetchRow(), but it puts the row of data directly into a variable instead of returning it:

$sth = $dbh->query(‘SELECT flavor,price FROM ice_cream’);
$sth->fetchInto($row);
// Prints out the first retrieved row
print “Flavor: $row[0], Price: $row[1]n”;

By default, fetchRow() and fetchInto() arrange data retrieved from the database in an indexed array. You can pass either of them an additional argument to change that. The constant DB_FETCHMODE_ASSOC causes the data to be put in an associative array, and DB_FETCHMODE_OBJECT causes the data to be returned as properties of an object. Pass one of these constants as the only argument to fetchRow():

$sth = $dbh->query(‘SELECT flavor,price FROM ice_cream’);
$row = $sth->fetchRow(DB_FETCHMODE_ASSOC);
// Prints out the first retrieved row
print “Flavor: $row[flavor], Price: $row[price]n”;
// Prints out the second retrieved row
$row = $sth->fetchRow(DB_FETCHMODE_OBJECT);
print “Flavor: $row->flavor, Price: $row->pricen”;

Or, pass it as the second argument to fetchInto():

$sth = $dbh->query(‘SELECT flavor,price FROM ice_cream’);
$sth->fetchInto($row,DB_FETCHMODE_ASSOC);
// Prints out the first retrieved row
print “Flavor: $row[flavor], Price: $row[price]n”;
// Prints out the second retrieved row
$sth->fetchInto($row,DB_FETCHMODE_OBJECT);
print “Flavor: $row->flavor, Price: $row->pricen”;

The DB::setFetchMode() method sets a default fetch mode that is used on all subsequent calls to fetchRow() or fetchInto(). Pass setFetchMode() the constant that corresponds to the default fetch mode you want:

$dbh->setFetchMode(DB_FETCHMODE_ASSOC);
$sth = $dbh->query(‘SELECT flavor,price FROM ice_cream’);
$row = $sth->fetchRow();
// Prints out the first retrieved row
print “Flavor: $row[flavor], Price: $row[price]n”;
// Prints out the second retrieved row
$row = $sth->fetchRow();
print “Flavor: $row[flavor], Price: $row[price]n”;

Aside from DB_FETCHMODE_ASSOC and DB_FETCHMODE_OBJECT, you can also use DB_FETCHMODE_ORDERED to specify the default behavior of an indexed array. For example, with DB_FETCHMODE_ORDERED, the first two columns are accessed as $row[0] and $row[1]:

$dbh->setFetchMode(DB_FETCHMODE_ORDERED);
$sth = $dbh->query(‘SELECT flavor,price FROM ice_cream’);
$row = $sth->fetchRow();
// Prints out the first retrieved row
print “Flavor: $row[0], Price: $row[1]n”;
$row = $sth->fetchRow();

// Prints out the second retrieved row
print “Flavor: $row[0], Price: $row[1]n”;

With DB_FETCHMODE_OBJECT, the columns are accessed as $row->flavor and $row->price:

$dbh->setFetchMode(DB_FETCHMODE_OBJECT);
$sth = $dbh->query(‘SELECT flavor,price FROM ice_cream’);
$row = $sth->fetchRow();
// Prints out the first retrieved row
print “Flavor: $row->flavor, Price: $row->pricen”;
$row = $sth->fetchRow();
// Prints out the second retrieved row
print “Flavor: $row->flavor, Price: $row->pricen”;

The query() method only returns a DB_Result object when it successfully executes a SELECT query. If it successfully executes another kind of query that doesn’t return any rows, it returns the constant DB_OK. This is what happens for INSERT, DELETE, and UPDATE queries as well as for queries that change the structure of the database with CREATE TABLE, ALTER TABLE, or DROP TABLE.

If the query() method doesn’t successfully execute any kind of query, it returns a DB_Error object. A query may fail because of a syntax error, missing data, or larger problems with the database server. Use the DB::isError() static method to check that a query has executed successfully:

$sth = $dbh->query(‘SELECT flavor,price FROM ice_cream’);
if (DB::isError($sth)) {
     print “Error!”;
} else {
     // display results
}

The DB_Error object has some methods that return information about the error. The two most useful of these are getMessage() and getUserInfo(). The getMessage() method returns a broadly descriptive error message, and the getUserInfo() returns more specific error information. For example, this query tries to retrieve records from a table that doesn’t exist:

$sth = $dbh->query(‘SELECT * FROM frozen_yogurt’);
if (DB::isError($sth)) { 
     print $sth->getMessage() . “nDetails: ”
                              .$sth->getUserInfo();
} else {
     // display results
}

The error is displayed like this:

DB Error: no such table
Details: SELECT * FROM frozen_yogurt [nativecode=1146 ** 
                  Table 'test.frozen_yogurt' doesn't exist]

The “Introducing Error Handling” section discusses how to handle errors. 

This chapter is from Essential PHP Modules, Extensions, Tools, by David Sklar (Apress, 2004, ISBN: 1590592808). Check it out at your favorite bookstore today.

Buy this book now.

{mospagebreak title=Understanding Quoting and Placeholders}

Understanding Quoting and Placeholders

Frequently, database queries contain user input and other dynamic values. Before putting these data into queries, however, you need to escape any characters that have special meaning to your database. These characters are as follows:

  • ‘: The single quote character delimits string literals in queries. This helps the database tell the difference between string values and field and table names.

  • %: The percent character is an SQL wildcard that means “match any number of characters,” like * in the shell or in a regular expression.

  • _: The underscore character is an SQL wildcard that means “match one character,” like ? in the shell or . in a regular expression.

  • : The backslash character is used by some databases as an escape character, so it itself has to be escaped.

The proper way to escape these characters in many databases is to put a backslash before them: SELECT * FROM songs WHERE title LIKE ‘Don’t Be Cruel’ becomes SELECT * FROM songs WHERE title LIKE ‘Don’t Be Cruel’. The single quote in Don’t is escaped to tell the database the song title is the entire string Don’t Be Cruel, not just Don. Some databases escape the single quote character by turning it into two single quote characters: SELECT * FROM songs WHERE title LIKE ‘Don”t Be Cruel’.

Placeholders

PEAR DB makes it easy for you to escape single quotes by providing query placeholders. These are special characters in the query that are replaced by actual data when the query is sent to the database. When the data replaces the placeholder in the query, it is appropriately quoted. To use a placeholder, put a ? character where you want the data to end up and pass a second argument to query() that contains an array holding the data:

$sth = $dbh->query(‘SELECT * FROM ice_cream WHERE flavor
       LIKE ?’, array($_REQUEST['flavor']));

The query method replaces the ? with the value of $_REQUEST['flavor'], surrounded by single quotes. Any single quotes in $_REQUEST['flavor'] are escaped in a method appropriate for the database you’re using. For example, if the value of $_REQUEST['flavor'] is Straw’s Berry, then the query that’s sent to MySQL is this:

SELECT * FROM ice_cream WHERE flavor LIKE ‘Straw’s Berry’

The query that’s sent to PostgreSQL is this:

SELECT * FROM ice_cream WHERE flavor LIKE ‘Straw”s Berry’

The ? placeholder only quotes strings. Integers and doubles are put into the query as is, and nulls are replaced with NULL (without quotes).

The ! placeholder is replaced by a value without any quoting or modification. This is useful for interpolating table names or column names. In this example, the table name to work with comes from $_REQUEST['dessert'], and the value to insert comes from $_REQUEST['flavor']:

$desserts = array(‘ice_cream’ => 1, ‘frozen_yogurt’ => 1, ‘sorbet’ => 1);
if ($desserts[$_REQUEST['dessert']]) {
     $dbh->query(‘INSERT INTO ! (flavor) VALUES (?)’,
          array($_REQUEST['dessert'], $_REQUEST['flavor']));
} else {
     print “No such dessert.”;
}

Checking to see whether the specified table is defined in the $desserts array is necessary to prevent a malicious user from inserting data into an arbitrary table in your database. If you use the ! placeholder to specify table or field names, always check to see whether the value that is going to be substituted for the placeholder is an acceptable one before running the query.

In addition to ? and !, there is a third placeholder: &. This is replaced with the contents of a file. Include the name of the file in the array of values passed to query() as a second argument:

$dbh->query(“INSERT INTO dessert_pictures (flavor,image) 
     VALUES (?,&)”, array(‘Rum Raisin’,'rum-raisin.jpeg’));

The DB::quote() Method

To quote values outside of query(), use the DB::quote() method. When passed a string, it returns the string quoted with single quotes escaped. It returns integers and doubles unmodified and returns the string NULL when passed a null value. For example:

$flavor = $dbh->quote($_REQUEST['flavor']);
$dbh->query(“SELECT * FROM ice_cream WHERE flavor LIKE
             $flavor”);

SQL Wildcards

Placeholder quoting and the quote() method make strings safe for inclusion in queries. They don’t escape the SQL wildcard characters % and _. You have to escape those characters yourself with str_replace():

$safe_for_select = str_replace(array(‘%’,'_’),
                   array(‘%’,'_’),$_REQUEST['flavor']);

Replacing % with % and _ with _ tells the database to treat those characters as a literal percent sign and underscore and not as SQL wildcards. If the value of $_REQUEST['flavor'] is Chocolate%, $dbh->query(“SELECT * FROM ice_cream WHERE flavor LIKE ‘$safe_for_select’”) only finds rows where flavor exactly matches the string Chocolate%, not all rows where flavor begins with Chocolate.

Using str_replace() to escape SQL wildcards can present a problem when used with query placeholders. The string quoting must happen before the wildcard escaping. Otherwise, the backslashes that the wildcard escaping inserts are themselves escaped by the string quoting. A single % character becomes % by the wildcard escaping. The % is turned into ‘\%’ by string quoting. The expression ‘\%’ matches any string that begins with a literal backslash character because \ is interpreted by the database as a literal backslash and then % is interpreted as “match any number of characters.” If the string quoting happens first, % is turned into ‘%’, and then the wildcard escaping makes it ‘%’. This matches only a one-character string containing a percent sign.

What does all this mean in practice? If you want to protect against errant SQL wildcards in user input, use quote() to quote strings before escaping wild-cards. Put these quoted and escaped values into queries explicitly instead of using placeholders:

$flavor = str_replace(array(‘%’,'_’),array(‘%’,'_’), 
          $dbh->quote($_REQUEST['flavor']));
$sth = $dbh->query(“SELECT * FROM ice_cream WHERE flavor 
                    LIKE $flavor”);

Magic Quotes

PHP has some configuration settings that affect string quoting. These settings don’t change PEAR DB’s behavior, so improper configuration may result in values getting quoted twice.

If the magic_quotes_gpc configuration setting is on, single quotes and backslashes in incoming GET, POST, and cookie data are automatically backslash-escaped. If the magic_quotes_runtime configuration setting is on, the same quoting happens automatically for data read from a file or database. If magic_quotes_sybase is on, then magic_quotes_gpc and magic_quotes_runtime escape a single quote with another single quote and don’t escape backslashes at all.

Using query placeholder quoting with GET, POST, or cookie data when magic quotes_gpc is on or with data read from a file or database when magic_quotes runtime is on results in data being quoted twice. If the submitted POST form variable flavor contains Straw’s Berry and magic_quotes_gpc is on, then the value of $_REQUEST['flavor'] is Straw’s Berry. A problem results if that value is substituted for the placeholder in this query:

$sth = $dbh->query(‘SELECT * FROM ice_cream WHERE flavor 
       LIKE ?’, array($_REQUEST['flavor']));

The query sent to the database is SELECT * FROM ice_cream WHERE flavor LIKE ‘Straw\’s Berry’. Because the backslash inserted by magic_quotes_gpc is itself escaped by the placeholder substitution, this query doesn’t find the appropriate rows.

The easiest way to avoid this double escaping is to turn off magic_quotes_gpc and magic_quotes_runtime in your php.ini or Web server configuration file. If you can’t do that, use stripslashes() to remove the escaping that was added:

$_REQUEST['flavor'] = stripslashes($_REQUEST['flavor']);
$sth = $dbh->query(‘SELECT * FROM ice_cream WHERE flavor
         LIKE ?’, array($_REQUEST['flavor']));

To make your code more portable, call stripslashes() only when necessary. The get_magic_quotes_gpc() function returns 1 if magic_quotes_gpc is on. Use get magic_quotes_gpc() to determine whether to call stripslashes():

if (get_magic_quotes_gpc()) {
$_REQUEST['flavor'] = stripslashes($_REQUEST['flavor']);
}
$sth = $dbh->query(‘SELECT * FROM ice_cream WHERE flavor
         LIKE ?’, array($_REQUEST['flavor']));

 

Note: The stripslashes() function turns \ into and ‘ into ‘ It doesn’t unquote Sybase-style quoted strings. To do that, use str_replace() : $str = str_replace(“””,”‘”,$str); . 

This chapter is from Essential PHP Modules, Extensions, Tools, by David Sklar (Apress, 2004, ISBN: 1590592808). Check it out at your favorite bookstore today.

Buy this book now.

{mospagebreak title=Examining Data Retrieval Convenience Methods}

PEAR DB provides methods that make common data retrieval operations easier. These methods combine query() and fetchRow(), allowing you to send a SELECT query to the database and retrieve its results in one step.

DB::getRow()

Use the getRow() method when you want all values in the first or only row returned from a query. getRow() returns an array or an object, depending on the current fetch mode. The default fetch mode is an indexed array:

$row = $dbh->getRow(‘SELECT flavor,price FROM ice_cream  
       WHERE id = 56′);
print “Flavor: $row[0], Price: $row[1]“;

Placeholders work with getRow() just as they do with query():

$row = $dbh->getRow(‘SELECT flavor,price FROM ice_cream 
       WHERE id = ?’, array($id));

An alternate fetch mode can be passed as a third argument to getRow():

$row = $dbh->getRow(‘SELECT flavor,price FROM ice_cream
       WHERE id = ?’, array($id),DB_FETCHMODE_OBJECT);
print “Flavor: $row->flavor, Price: $row->price”;

Placeholders must be passed to getRow() in an array, just as they must be passed to query().

DB::getAll()

Use getAll() when you want all values in all rows returned from a query. Just like getRow(), getAll() accepts an array of placeholder replacements as a second argument and an optional alternate fetch mode as a third argument. The getAll() function always returns an array, but the type of each element in the array is controlled by the fetch mode. Iterate through the array that getAll() returns to display results:

$results = $dbh->getAll(‘SELECT flavor,price
           FROM ice_cream’);
foreach($results as $row) {
    print “Flavor: $row[0], Price: $row[1]<br>”;
}

DB::getOne()

Use getOne() when you want the first value from the first or only row returned from a query. The getOne() function accepts an array of placeholder replacements as an optional second argument. It returns a string containing the retrieved value or NULL if the query returned no results. It also returns NULL if the first value of the first row of the result is NULL:

$flavor = $dbh->getOne(‘SELECT flavor FROM ice_cream ORDER
           BY price DESC LIMIT 1′);
if (is_null($flavor)) {
     print “We don’t have any ice cream!”;
} else {
     print “Our most expensive flavor is $flavor.”;
}

DB::getCol()

Use getCol() when you want the value of a particular column in all the rows returned from a query. The getCol() function accepts a column name or number to return as an optional second argument. If this is not specified, getCol() returns the first column. The function also accepts an array of placeholder replacements as an optional third argument. The retrieved values are returned as an indexed array:

$flavors = $dbh->getCol(‘SELECT flavor FROM ice_cream’);
print “Our flavors: <ul> <li> ” . join(‘<li>’,$flavors) 
       .”</ul>”;

DB::getAssoc()

Use getAssoc() when you want an entire result set, such as getAll(), but you want to easily access particular rows of the result. The getAssoc() function returns an associative array whose keys are the values of the first column of the query results. If you select two columns, the associative array values are the values of the second column:

$res = $dbh->getAssoc(‘SELECT flavor,price FROM
       ice_cream’);
print “A serving of Heavenly Hash costs: “.$res['Heavenly
       Hash']

If you select more than two columns, the associative array values are themselves arrays of the remaining column values for each row:

$res = $dbh->getAssoc(‘SELECT id,flavor,price FROM
       ice_cream’);
if ($flavor = $res[$_REQUEST['flavor_id']]) {
     print “Your selected flavor is: $flavor[0] with price
            $flavor[1].”;
} else {
     print “No flavor has ID $_REQUEST[flavor_id].”;
}

To force getAssoc() to return values as arrays instead of scalars when only one column of values is involved, pass true as a second argument when calling the function:

$res = $dbh->getAssoc(‘SELECT flavor,price FROM
       ice_cream’, true);
print “A serving of Heavenly Hash costs: “.$res['Heavenly
       Hash'][0];

The third argument to getAssoc() is an array of values to replace any placeholders in the query:

$res = $dbh->getAssoc(‘SELECT id,flavor,price FROM
       ice_cream WHERE price > ?’, true,array($_REQUEST
       ['min_price']));
if ($flavor = $res[$_REQUEST['flavor_id']]) {
print “Your selected flavor is: $flavor[0] with price
       $flavor[1].”;
} else {
print “No flavor has ID $_REQUEST[flavor_id].”;
}

Tell getAssoc() to return each array of values as an associative array instead of an indexed array by passing DB_FETCHMODE_ASSOC as a fourth argument:

$res = $dbh->getAssoc(‘SELECT id,flavor,price FROM
       ice_cream’, null, null, DB_FETCHMODE_ASSOC);
if ($flavor = $res[$_REQUEST['flavor_id']]) {
print “Your selected flavor is: $flavor[flavor] with price
       $flavor[price].”;
} else {
print “No flavor has ID $_REQUEST[flavor_id].”;
}

The getAssoc() function returns an array whose values are easy to display as an HTML <select> widget. Select the columns for the value and label of each option and then use foreach to loop through the array:

$flavors= $dbh->getAssoc(‘SELECT id,flavor FROM
          ice_cream’);
print ‘<select name=”flavor”>’;
foreach ($flavors as $id => $flavor) {
     print “<option value=”$id”>$flavor</option>”;
}
print ‘</select>’;

This chapter is from Essential PHP Modules, Extensions, Tools, by David Sklar (Apress, 2004, ISBN: 1590592808). Check it out at your favorite bookstore today.

Buy this book now.

{mospagebreak title=Understanding Query Information}

In addition to methods that send queries to the database and return results, DB provides some methods that return information about a query. These methods tell you the size of the result set returned or modified by a query.

DB_Result::numRows()

The numRows() method returns the number of rows in a result set. This is useful for checking if any rows were selected before printing them:

$res = $dbh->query(‘SELECT flavor FROM ice_cream
       WHERE price < 5′);
if ($res->numRows() > 0) {
     print “Your choices: <ul>”;
     while($row = $res->fetchRow())
          { print “<li> $row[0]</li>”; }
     print “</ul>”;
} else { 
     print “No flavors available for less than five
            dollars.”;
}

The numRows() method is called on the statement handle, not on the database handle. You can only call numRows() after a SELECT query.

DB_Result::numCols()

The numCols() method returns the number of columns in a result set. You can use numCols() to dynamically display information about a table:

$res = $dbh->query(‘SELECT * FROM ice_cream’);
print ‘There are ‘.$res->numCols().’ columns in the
       ice_cream table.’; print ‘<table>’;
while($row = $res->fetchRow()) {
     print ‘<tr>’;
     foreach ($row as $val) { print “<td>$val</td>”; }
     print ‘</tr>’;
}
print ‘</table>’;

Like numRows(), the numCols() method is called on the statement handle, not on the database handle. You can only call numCols() after a SELECT query.

DB::affectedRows()

The affectedRows() method returns how many rows were changed by an UPDATE, INSERT, or DELETE query. It is called on the database handle, not the statement handle. This is because the query() method doesn’t return a statement handle for these kinds of queries, just a status code or error object:

$dbh->query(“UPDATE ice_cream SET price = price – 1 WHERE
      flavor
LIKE ‘Chocolate%’”);
print ‘Discount applied to ‘ . $dbh->affectedRows()
     
. ‘ Chocolate flavors.’;

This chapter is from Essential PHP Modules, Extensions, Tools, by David Sklar (Apress, 2004, ISBN: 1590592808). Check it out at your favorite bookstore today.

Buy this book now.

{mospagebreak title=Running a Query Multiple Times}

Often, a program needs to run a query many times with different values each time. A query that inserts a product into a product catalog is called ten times to insert ten new products into the catalog. Each time, the structure of the query is identical. However, new values such as product name and price must be incorporated into the query on each invocation.

DB::prepare() and DB::execute()

To run a query multiple times with different values each time, use prepare() and execute(). Call prepare() once with placeholders representing the values that change on each query execution. This returns a prepared statement handle. Then, call execute() with the prepared statement handle and each set of values:

$prh = $dbh->prepare(‘INSERT INTO ice_cream (flavor,price)
       VALUES (?,?)’);
$dbh->execute($prh,array(‘Coffee’,1.25));
$dbh->execute($prh,array(‘Pistachio’,2.00));
$dbh->execute($prh,array(‘Caramel Pecan’,1.75));

The prepare() method supports the same set of placeholders that query() does, so you can use ! for unquoted values and & for file contents:

$prh = $dbh->prepare(‘INSERT INTO ! (flavor,price,image)
       VALUES (?,?,&)’);
$dbh->execute($prh,array(‘frozen_yogurt’,'Tofu Health
      Crunch’,2.50, ‘yogurt-tofu-crunch.jpg’));
$dbh->execute($prh,array
     (‘ice_cream’,'Vanilla’,1.40,’delicious-vanilla.jpg’));

These methods can be used for SELECT queries as well. Each successful execute() of a SELECT query returns a statement handle. These are the same statement handles that query() returns:

$prh = $dbh->prepare(‘SELECT flavor FROM !’);
$res = $dbh->execute($prh,’frozen_yogurt’);
print_flavors(‘Frozen Yogurt’,$res);
$res = $dbh->execute($prh,’ice_cream’);
print_flavors(‘Ice Cream’,$res);

DB::autoPrepare() and DB::autoExecute()

While prepare() and execute() make it easier to run the same query multiple times, autoPrepare() and autoExecute() make it easier to build queries from arrays of field names and values. The autoPrepare() method returns a prepared statement handle just like prepare(). Instead of passing it an SQL query with placeholders, however, you pass it a table name, an array of field names, and amode. For example, these calls to autoPrepare() and prepare() return identical statement handles:

$dbh->autoPrepare(‘ice_cream’,array(‘flavor’,'price’),
      DB_AUTOQUERY_INSERT);
$dbh->prepare(“INSERT INTO ice_cream (‘flavor’,'price’) 
      VALUES (?,?)”);

The first argument to autoPrepare() is the name of the table to use. The second argument is an array of field names. The third argument tells autoPrepare() whether to prepare an INSERT or UPDATE query. To prepare an UPDATE query, use DB_AUTOQUERY_UPDATE:

$dbh->autoPrepare(‘ice_cream’,array(‘flavor’,'price’),
      DB_AUTOQUERY_UPDATE);

This returns a prepared statement handle as if you had called this:

$dbh->prepare(‘UPDATE ice_cream SET flavor = ?,
      price = ?’);

To include a WHERE clause in an UPDATE query generated by autoPrepare(), pass it as a fourth argument to autoPrepare():

$dbh->autoPrepare(‘ice_cream’,array(‘flavor’,'price’),
      DB_AUTOQUERY_UPDATE, ‘price < 10′);

This returns a prepared statement handle as if you had called this:

$dbh->prepare(‘UPDATE ice_cream SET flavor = ?, price = ?
      WHERE price < 10′);

The autoExecute() method takes autoPrepare() one step further. It prepares a query but also executes it with an array of values. Instead of an array of field names such as autoPrepare(), autoExecute() takes an associative array of fields and values:

$dbh->autoExecute(‘ice_cream’,array(‘flavor’ => ‘Blueberry’
      , ‘price’ => 3.00), DB_AUTOQUERY_INSERT);

This prepares and executes a query as if you had called this:

$prh = $dbh->prepare(‘INSERT INTO ice_cream (flavor,price)
       VALUES (?,?)’);
$dbh->execute($prh, array(‘Blueberry’,3.00));

The autoExecute() method runs UPDATE queries just like autoPrepare():

$dbh->autoExecute(‘ice_cream’,
                            array(‘flavor’ => ‘Blueberry’, ‘price’ => 3.00),
                            DB_AUTOQUERY_UPDATE);

This prepares and executes a query as if you had called this:

$prh = $dbh->prepare(‘UPDATE ice_cream SET flavor = ?,
       price = ?’);
$dbh->execute($prh, array(‘Blueberry’,3.00));

The autoExecute() method also accepts a WHERE clause just like autoPrepare():

$dbh->autoExecute(‘ice_cream’,array(‘flavor’ =>
      ‘Blueberry’, ‘price’ => 3.00),
      DB_AUTOQUERY_UPDATE,’id = 23′);

This prepares and executes a query as if you had called this:

$prh = $dbh->prepare(‘UPDATE ice_cream SET flavor = ?,
       price = ? WHERE id = 23′);
$dbh->execute($prh, array(‘Blueberry’,3.00));

The autoPrepare() and autoExecute() methods are especially useful for saving information from a Web form that has many fields. Define those fields in an array, and use autoExecute() to save information from the $_REQUEST array into the database. If the fields in the form change, you have to only update the line of code that defines the $fields array, and the query is automatically changed as well:

$fields = array(‘flavor’,'price’,'id’,'rating’);
$values = array();
foreach ($fields as $f) { $values[$f] = $_REQUEST[$f]; }
$dbh->autoExecute(‘ice_cream’,$values,DB_AUTOQUERY_INSERT);

This chapter is from Essential PHP Modules, Extensions, Tools, by David Sklar (Apress, 2004, ISBN: 1590592808). Check it out at your favorite bookstore today.

Buy this book now.

{mospagebreak title=Introducing Sequences}

A sequence is a source of unique integer identifiers. When you need an ID for an item in your database, ask a sequence for its next available ID. This ID is guaranteed to be unique within the sequence. If two queries ask the same sequence for an ID at the same time, each query gets a different answer.

The easiest way to use a sequence is just to call the DB::nextID() method. This creates the specified sequence if it doesn’t already exist and returns the next available ID in the sequence:

$flavor_id = $dbh->nextID(‘flavors’);
$dbh->query(‘INSERT INTO ice_cream (id,flavor) VALUES
      (?,?)’, array($flavor_id,’Walnut’));

To create sequences explicitly, use createSequence():

$dbh->createSequence(‘flavors’);

If you are creating your sequences with createSequence(), you can tell nextID() not to create sequences automatically by passing false as a second argument:

$flavor_id = $dbh->nextID(‘flavors’,false);
if (DB::isError($flavor_id)) { 
     die(“Can’t get sequence ID”);
}
$dbh->query(‘INSERT INTO ice_cream (id,flavor) VALUES 
      (?,?)’, array($flavor_id,’Walnut’));

Whether they are created automatically by nextID() or explicitly with createSequence(), the dropSequence() method deletes a sequence:

$dbh->dropSequence(‘flavors’);

Introducing Error Handling

DB methods return a DB_Error object if they fail. The DB_Error object contains fields that describe the error condition. The “Sending Queries and Receiving Results” section describes the basic use of DB::isError(). This function returns true when the object passed to it is a DB_Error object. To comprehensively catch errors with this test, use it each time you call a DB method that may return an error:

$dbh = DB::Connect(‘mysql://user:pwd@localhost/dbname’);
if (DB::isError($dbh)) { 
     die(“Can’t connect: “.$dbh->getMessage());
}
$sth = $dbh->query(‘SELECT flavor,price FROM ice_cream’);
if (DB::isError($dbh)) {
     die(“Can’t SELECT: “.$sth->getMessage());
}
while($res = $sth->fetchRow()) {
     if (DB::isError($res)) {
          die(“Can’t get row: “.$res->getMessage());
     }
     print “Flavor: $res[0], Price: $res[1]“;
}

Instead of testing each call to DB::isError() with if(), you can use the and logical operator instead, which is slightly more concise:

$dbh = DB::Connect(‘mysql://test:@localhost/test’);
DB::isError($dbh) and die(“Can’t connect: “
    .$dbh->getMessage());
$sth = $dbh->query(‘SELECT flavor,price FROM ice_cream’);
DB::isError($sth) and die(“Can’t SELECT: “
    .$sth->getMessage());
while($res = $sth->fetchRow()) {
     DB::isError($res) and die(“Can’t get row: “
         .$res->getMessage());
     print “Flavor: $res[0], Price: $res[1]n”;
}

Still, using DB::isError() after each relevant method call is cumbersome and error prone. If you forget to check the results of one method and it fails, subsequent operations won’t work properly. The DB::setErrorHandling() method allows you to tell DB to automatically take an action whenever a DB method call returns an error. To print the error and exit the program immediately, pass the constant PEAR_ERROR_DIE to setErrorHandling():

$dbh = DB::Connect(‘mysql://test:@localhost/test’);
DB::isError($dbh) and die($dbh->getMessage());
$dbh->setErrorHandling(PEAR_ERROR_DIE);
$sth = $dbh->query(‘SELECT flavor,price FROM ice_cream’);
while($res = $sth->fetchRow()) {
     print “Flavor: $res[0], Price: $res[1]n”;
}

To print the error but continue program execution, use PEAR_ERROR_PRINT instead of PEAR_ERROR_DIE. You can also use setErrorHandling() to have a custom function called each time there’s a DB error. Pass PEAR_ERROR_CALLBACK as the first argument to setErrorHandling() and the name of the function to call as the second argument:

$dbh = DB::Connect(‘mysql://test:@localhost/test’);
DB::isError($dbh) and die($dbh->getMessage());
$dbh->setErrorHandling(PEAR_ERROR_CALLBACK,’db_error’);
$sth = $dbh->query(‘SELECT flavor_name,price FROM
       ice_cream’);
while($res = $sth->fetchRow()) {
     print “Flavor: $res[0], Price: $res[1]n”;
}
function db_error($err_obj) {
     print “Error! [$err_obj->code] $err_obj->userinfo”;
}

Because this example has an unknown field in the SQL query (flavor_name instead of flavor), the db_error() error callback is called, and it prints this:

Error! [-19] SELECT flavor_name,price FROM ice_cream
[nativecode=1054 ** Unknown column 'flavor_name' in
'field list']

Error callbacks are useful for queries inside of a transaction. If there’s an error, the callback can automatically roll back the transaction and print a message or return the proper value to indicate that the transaction failed. 

This chapter is from Essential PHP Modules, Extensions, Tools, by David Sklar (Apress, 2004, ISBN: 1590592808). Check it out at your favorite bookstore today.

Buy this book now.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan