Introducing DSNs - PHP

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.

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');

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->price\n";

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->price\n";

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->price\n";
$row = $sth->fetchRow();
// Prints out the second retrieved row
print "Flavor: $row->flavor, Price: $row->price\n";

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.