Méthodologie de développement MVC d'une application web PHP4

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:

Sélectionnez

phptype(dbsyntax)://username:password@protocol+hostspec/database

Most variations are allowed:

Sélectionnez

1.
2.
3.
4.
5.
6.
7.
8.

phptype://username:password@protocol+hostspec:110//usr/db_file.db
phptype://username:password@hostspec/database_name
phptype://username:password@hostspec
phptype://username@hostspec
phptype://hostspec/database
phptype://hostspec
phptype(dbsyntax)
phptype

The currently supported database backends are:

Sélectionnez

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

mysql -> MySQL
pgsql -> PostgreSQL
ibase -> InterBase
msql -> Mini SQL
mssql -> Microsoft SQL Server
oci8 -> Oracle 7/8/8i
odbc -> ODBC (Open Database Connectivity)
sybase -> SyBase
ifx -> Informix
fbsql -> FrontBase

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

Sélectionnez

1.
2.
3.
4.
5.
6.
7.

phptype(syntax)://user:pass@protocol(proto_opts)/database

example: Connect to database through a socket
mysql://user@unix(/path/to/socket)/pear

Connect to database on a non standard port
pgsql://user:pass@word@tcp(localhost:5555)/pear

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.

Sélectionnez

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.

<?php
require_once 'DB.php';

$user = 'foo';
$pass = 'bar';
$host = 'localhost';
$db_name = 'clients_db';

// Data Source Name: This is the universal connection string
$dsn = "mysql://$user:$pass@$host/$db_name";

// DB::connect will return a PEAR DB object on success
// or an PEAR DB Error object on error
$db = DB::connect($dsn, true);

// Alternatively: $db = DB::connect($dsn);

// With DB::isError you can differentiate between an error or
// a valid connection.
if (DB::isError($db)) {
  die ($db->getMessage());
}.
...
// You can disconnect from the database with:
$db->disconnect();
?>

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.

Sélectionnez

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

<?php
// Once you have a valid DB object...
$sql = "select * from clients";
$result = $db->query($sql);

// Always check that $result is not an error
if (DB::isError($result)) {
  die ($result->getMessage());
}.
...
?>

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.

Sélectionnez

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

<?php

...
$db = DB::connect($dsn);
$res = $db->query("select * from mytable");

// Get each row of data on each iteration until
// there is no more rows
while ($row = $res->fetchRow()) {
   $id = $row[0];
}
// Or:
// an example using fetchInto()
while ($res->fetchInto($row)) {
   $id = $row[0];
} ?>

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

Sélectionnez

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

<?php
$res = $db->query('select id, name, email from users');
$row = $res->fetchRow(DB_FETCHMODE_ASSOC);
/*
$row will contain:
array (
   'id' => <column "id" data>,
   'name' => <column "name" data>,
   'email' => <column "email" data>
)
*/
// Access the data with:
$id = $row['id'];
$name = $row['name'];
$email = $row['email'];
?>

DB_FETCHMODE_OBJECT : returns a DB_row object with column names as properties

Sélectionnez

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

<?php
$res = $db->query('select id, name, email from users');
$row = $res->fetchRow(DB_FETCHMODE_OBJECT);
/*
$row will contain:
db_row Object
(
  [id] => <column "id" data>,
  [name] => <column "name" data>,
  [email] => <column "email" data>
)
*/
// Access the data with:
$id = $row->id;
$name = $row->name;
$email = $row->email;
?>

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.

Sélectionnez

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

<?php
// 1) Set the mode per call:
while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
  $id = $row['id'];
}

// 2) Set the mode for all calls:
$db = DB::connect($dsn);
// this will set a default fetchmode for this Pear DB instance
// (for all queries)
$db->setFetchMode(DB_FETCHMODE_ASSOC);

$result = $db->query(...);
while ($row = $result->fetchRow()) {
  $id = $row['id'];
} ?>

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.

Sélectionnez

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

<?php

...
// the row to start fetching
$from = 50;

// how many results per page
$res_per_page = 10;

// the last row to fetch for this page
$to = $from + $res_per_page;

foreach (range($from, $to) as $rownum) {
  if (!$row = $res->fetchrow($fetchmode, $rownum)) {
    break;
  }
  $id = $row[0];
....
} ?>

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.

Sélectionnez

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

<?php

...
$result = $db->query('SELECT * FROM clients');
while ($row = $result->fetchRow()) {

...
} $
result->free();
?>

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

Sélectionnez

$numrows = $db->getOne('select count(id) from clients');

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

Sélectionnez

1.
2.
3.
4.

$sql = 'select name, address, phone from clients where id=1';
if (is_array($row = $db->getRow($sql))) {
  list($name, $address, $phone) = $row;
}

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

Sélectionnez

$all_client_names = $db->getCol('select name from clients');

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

Sélectionnez

getAssoc() fetches the entire result set of a query and return it as an associative array using the first column as the key.

Sélectionnez

1.
2.
3.
4.
5.
6.
7.
8.
9.

$data = getAssoc('SELECT name, surname, phone FROM mytable')
/*
Will return:
array(
  'Peter' => array('Smith', '944679408'),
  'Tomas' => array('Cox', '944679408'),
  'Richard' => array('Merz', '944679408')
)
*/

getAll() fetches all the rows returned from a query

Sélectionnez

1.
2.
3.
4.
5.
6.
7.
8.
9.

$data = getAll('SELECT id, text, date FROM mytable');
/*
Will return:
array(
  1 => array('4', 'four', '2004'),
  2 => array('5', 'five', '2005'),
  3 => array('6', 'six', '2006')
)
*/

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.

Sélectionnez

1.
2.

// Number of rows
echo $res->numRows();

numCols(): Returns the total number of columns returned from a "SELECT" query.

Sélectionnez

1.
2.

// Number of cols
echo $res->numCols();

affectedRows(): Returns the number of rows affected by a data manipulation query ("INSERT", "UPDATE" or "DELETE").

Sélectionnez

1.
2.
3.

// remember that this statement won't return a result object
$db->query('DELETE * FROM clients');
echo 'I have deleted ' . $db->affectedRows() . ' clients';

tableInfo(): Returns an associative array with information about the returned fields from a "SELECT" query.

Sélectionnez

1.
2.

// Table Info
print_r($res->tableInfo());