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


précédentsommaire

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:

 
CacherSélectionnez

Most variations are allowed:

 
CacherSélectionnez

The currently supported database backends are:

 
CacherSélectionnez

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

 
CacherSé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.

 
CacherSé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.

 
CacherSé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.

 
CacherSé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.
 
Sélectionnez
<?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
 
CacherSélectionnez
  • DB_FETCHMODE_OBJECT : returns a DB_row object with column names as properties
 
CacherSé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.

 
CacherSé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.

 
CacherSé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.

 
CacherSé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

 
CacherSélectionnez

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

 
CacherSélectionnez

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

 
CacherSélectionnez

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

 
CacherSélectionnez
 
CacherSélectionnez

getAll() fetches all the rows returned from a query

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

précédentsommaire

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Copyright © 2004 Serge Tahé. Aucune reproduction, même partielle, ne peut être faite de ce site et 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.