VI. ANNEXE - PEAR DB▲
Note : le texte ci-dessous est tiré de la documentation officielle de PEAR DB [http://pear.php.net/]. Il n'est là que pour faciliter le travail du lecteur de ce document.
VI-A. PEAR DB: a unified API for accessing SQL-databases▲
This chapter describes how to use the PEAR database abstraction layer.
DSN -- The data source name
Connect -- Connecting and disconnecting
Query -- Performing a query against a database.
Fetch -- Fetching rows from the query
VI-A-1. DSN▲
To connect to a database through PEAR::DB, you have to create a valid DSN - data source name. This DSN consists in the following parts:
phptype: Database backend used in PHP (i.e. mysql, odbc etc.)
dbsyntax: Database used with regards to SQL syntax etc.
protocol: Communication protocol to use ( i.e. tcp, unix etc.)
hostspec: Host specification (hostname[:port])
database: Database to use on the DBMS server
username: User name for login
password: Password for login
proto_opts: Maybe used with protocol
The format of the supplied DSN is in its fullest form:
Most variations are allowed:
The currently supported database backends are:
With an up-to-date version of DB, you can use a second DSN format
VI-A-2. Connect▲
To connect to a database you have to use the function DB::connect(), which requires a valid DSN as parameter and optional a boolean value, which determines wether to use a persistent connection or not. In case of success you get a new instance of the database class. It is strongly recommened to check this return value with DB::isError(). To disconnect use the method disconnect() from your database class instance.
VI-A-3. Query▲
To perform a query against a database you have to use the function query(), that takes the query string as an argument. On failure you get a DB Error object, check it with DB::isError(). On succes you get DB_OK (predefined PEAR::DB constant) or when you set a SELECT-statment a DB Result object.
VI-A-4. Fetch▲
The DB_Result object provides two functions to fetch rows: fetchRow() and fetchInto(). fetchRow() returns the row, null on no more data or a DB_Error, when an error occurs. fetchInto() requires a variable, which be will directly assigned by reference to the result row. It will return null when result set is empty or a DB_Error too.
VI-A-4-a. Select the format of the fetched row▲
The fetch modes supported are:
- DB_FETCHMODE_ORDERED (default) : returns an ordered array. The order is taken from the select statment.
<?php
$res = $db->query('select id, name, email from users');
$row = $res->fetchRow(DB_FETCHMODE_ORDERED);
/*
$row will contain:
array (
0 => <column "id" data>,
1 => <column "name" data>,
2 => <column "email" data>
)
*/
// Access the data with:
$id = $row[0];
$name = $row[1];
$email = $row[2];
?>- DB_FETCHMODE_ASSOC : returns an associative array with the column names as the array keys
- DB_FETCHMODE_OBJECT : returns a DB_row object with column names as properties
VI-A-4-b. Set the format of the fetched row▲
You can set the fetch mode for every call or for your whole DB instance.
VI-A-4-c. Fetch rows by number▲
The PEAR DB fetch system also supports an extra parameter to the fetch statement. So you can fetch rows from a result by number. This is especially helpful if you only want to show sets of an entire result (for example in building paginated HTML lists), fetch rows in an special order, etc.
VI-A-4-d. Freeing the result set▲
It is recommended to finish the result set after processing in order to to save memory. Use free() to do this.
VI-A-4-e. Quick data retrieving▲
PEAR DB provides some special ways to retrieve information from a query without the need of using fetch*() and loop throw results.
getOne() retrieves the first result of the first column from a query
getRow() returns the first row and return it as an array
getCol() returns an array with the data of the selected column. It accepts the column number to retrieve as the second param.
The above sentence could return for example: $all_client_names = array('Stig', 'Jon', 'Colin');
getAll() fetches all the rows returned from a query
The get*() family methods will do all the dirty job for you, this is: launch the query, fetch the data and free the result. Please note that as all PEAR DB functions they will return a PEAR DB_error object on errors.
VI-A-4-f. Getting more information from query results▲
With PEAR DB you have many ways to retrieve useful information from query results. These are:
- numRows(): Returns the total number of rows returned from a "SELECT" query.
- numCols(): Returns the total number of columns returned from a "SELECT" query.
- affectedRows(): Returns the number of rows affected by a data manipulation query ("INSERT", "UPDATE" or "DELETE").
- tableInfo(): Returns an associative array with information about the returned fields from a "SELECT" query.