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 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:
For example, if the filename of the Unix socket you want to use is /tmp/mysql.sock, your DSN would look like this:
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:
They can be INSERT, UPDATE, or DELETE queries that alter data in the database:
They can be queries that create, alter, or drop tables:
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:
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:
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():
Or, pass it as the second argument to fetchInto():
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:
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 and $row:
With DB_FETCHMODE_OBJECT, the columns are accessed as $row->flavor and $row->price:
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:
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:
The error is displayed like this:
The “Introducing Error Handling” section discusses how to handle errors.
blog comments powered by Disqus