IdentifiantMot de passe
Mot de passe oublié ?Je m'inscris ! (gratuit)

Méthodologie de développement MVC d'une application PHP



Note : le texte ci-dessous est tiré de la documentation officielle de PEAR DB []. 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


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:

Cacher/Afficher le codeSélectionnez

Most variations are allowed:

Cacher/Afficher le codeSélectionnez

The currently supported database backends are:

Cacher/Afficher le codeSélectionnez

With an up-to-date version of DB, you can use a second DSN format

Cacher/Afficher le codeSélectionnez

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.

Cacher/Afficher le codeSélectionnez

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.

Cacher/Afficher le codeSélectionnez

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.

Cacher/Afficher le codeSélectionnez
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.
$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
Cacher/Afficher le codeSélectionnez
  • DB_FETCHMODE_OBJECT : returns a DB_row object with column names as properties
Cacher/Afficher le codeSélectionnez
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.

Cacher/Afficher le codeSélectionnez
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.

Cacher/Afficher le codeSélectionnez
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.

Cacher/Afficher le codeSélectionnez
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

Cacher/Afficher le codeSélectionnez

getRow() returns the first row and return it as an array

Cacher/Afficher le codeSélectionnez

getCol() returns an array with the data of the selected column. It accepts the column number to retrieve as the second param.

Cacher/Afficher le codeSélectionnez

The above sentence could return for example: $all_client_names = array('Stig', 'Jon', 'Colin');

Cacher/Afficher le codeSélectionnez
Cacher/Afficher le codeSélectionnez

getAll() fetches all the rows returned from a query

Cacher/Afficher le codeSélectionnez

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.
Cacher/Afficher le codeSélectionnez
  • numCols(): Returns the total number of columns returned from a "SELECT" query.
Cacher/Afficher le codeSélectionnez
  • affectedRows(): Returns the number of rows affected by a data manipulation query ("INSERT", "UPDATE" or "DELETE").
Cacher/Afficher le codeSélectionnez
  • tableInfo(): Returns an associative array with information about the returned fields from a "SELECT" query.
Cacher/Afficher le codeSélectionnez


Copyright © 2004 Serge Tahé. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.