Aplicativos em PHP/Abstrações de Bancos de Dados/PEAR/DB

Abstração PEAR/DB

Este pacote da PEAR foi substituído pelo MDB mas continua sendo mantido em termos de bugs e segurança.

É uma abstração de bancos de dados com boas funções que tanto economizam o trabalho de desenvolvimento quanto facilitam a migração entre os diversos SGBDs suportados: fbsql, ibase, informix, msql, mssql, mysql, mysqli, oci8, odbc, pgsql, sqlite and sybase.

PEAR/DB Exemplos Testados

A PEAR/DB é uma abstração de Bancos de Dados para uso com o PHP. Abstração é uma técnica que simplifica algo complexo.

Obs.: Nem todos os SGBDs suportam todos os métodos e propriedades da PEAR/DB.

Conexão

<?php
// Create a valid DB object named $db
// at the beginning of your program...
// Forma genérica: $db =& DB::connect('tiposgbd://$usuario:$senha@$host:$porta/$banco');

require_once 'DB.php';

$db =& DB::connect('pgsql://postgres:senha@localhost:5432/testes');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

Query

// Proceed with getting some data...
$res =& $db->query('SELECT * FROM clientes');

fetchInto

// Get each row of data on each iteration until
// there are no more rows
//while ($res->fetchInto($row)) {
    // Assuming DB's default fetchmode is DB_FETCHMODE_ORDERED
//    echo $row[0] . "<br>";
//}

fetchRow

// Or, you could have done the same thing using fetchRow()
 while ($row =& $res->fetchRow()) {
     // Assuming DB's default fetchmode is DB_FETCHMODE_ORDERED
     echo $row[0] . "<br>";
 }
?>

fetchInfo

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$db =& DB::connect('pgsql://postgres:senha@localhost:5432/testes');
if (PEAR::isError($db)) {
    die($db->getMessage());
}
// Once you have a valid DB object named $db...
$res =& $db->query('SELECT * FROM clientes');

while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) {
    echo $row['codigo'] . "\n";
}
?>

fetchInto

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$db =& DB::connect('pgsql://postgres:senha@localhost:5432/testes');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

// Once you have a valid DB object named $db...
$db->setFetchMode(DB_FETCHMODE_ASSOC);

$res =& $db->query('SELECT * FROM clientes');

while ($res->fetchInto($row)) {
    echo $row['nome'] . "\n";
}
?>

Número de Registros

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$db =& DB::connect('pgsql://postgres:senha@localhost:5432/testes');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

// Once you have a valid DB object named $db...
$res =& $db->query('SELECT * FROM clientes');
echo $res->numRows();

$res->free();
?>

Número de Campos

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$db =& DB::connect('pgsql://postgres:senha@localhost:5432/testes');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

// Once you have a valid DB object named $db...
$res =& $db->query('SELECT * FROM clientes');
echo $res->numCols();

$res->free();
?>

afectedRows

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$db =& DB::connect('pgsql://postgres:senha@localhost:5432/testes');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

// remember that this statement won't return a result object. Para: INSERT, UPDATE or DELETE
$db->query('DELETE * FROM clientes');
echo 'I have deleted ' . $db->affectedRows() . ' clients';
 
?>

TableInfo

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$db =& DB::connect('pgsql://postgres:senha@localhost:5432/testes');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

// Once you have a valid DB object named $db...
$res =& $db->query('SELECT * FROM clientes');
print_r($db->tableInfo($res));

// That usage works for DB 1.6.0 or later.
// Below is the syntax for earlier versions:
print_r($res->tableInfo());

$res->free(); 
?>

Prepare e Execute

<?php // Create a valid DB object named $db // at the beginning of your program... require_once 'DB.php';

$db =& DB::connect('pgsql://postgres:senha@localhost:5432/testes'); if (PEAR::isError($db)) {

   die($db->getMessage());

}

// Once you have a valid DB object named $db... // Inserir dois registros apenas com o campo código $sth = $db->prepare('INSERT INTO clientes (codigo) VALUES (?)'); $db->execute($sth, 15000); $db->execute($sth, 18000);

//$res->free(); ?>

Prepare e Execute (arrays)

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$db =& DB::connect('pgsql://postgres:senha@localhost:5432/testes');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

// Once you have a valid DB object named $db...(Adiciona um registro - 15001, com 3 campos)
$sth = $db->prepare('INSERT INTO clientes VALUES (?, ?, ?)');

$data = array(15001, 'quinze', 'amail15');
$db->execute($sth, $data);

//$res->free(); 
?>

Passando arrays para Execute

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$db =& DB::connect('pgsql://postgres:senha@localhost:5432/testes');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

// Once you have a valid DB object named $db...
$alldata = array(array(15002, 'quinze2', 'enemail'),
                 array(15003, 'quinze3', 'toemail'),
                 array(15004, 'quinze4', 'treemail'),
                 array(15005, 'quinze5', 'fireemail'));
$sth = $db->prepare('INSERT INTO clientes VALUES (?, ?, ?)');

foreach ($alldata as $row) {
    $db->execute($sth, $row);
}
?>

Usando executeMultiple ao invés de execute

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$db =& DB::connect('pgsql://postgres:senha@localhost:5432/testes');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

// Once you have a valid DB object named $db... 
$alldata = array(array(16001, 'one', 'en'),
                 array(16002, 'two', 'to'),
                 array(16003, 'three', 'tre'),
                 array(16004, 'four', 'fire'));
$sth = $db->prepare('INSERT INTO clientes VALUES (?, ?, ?)');
$db->executeMultiple($sth, $alldata);

?>

AutoPrepare

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$db =& DB::connect('pgsql://postgres:senha@localhost:5432/testes');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

// With autoPrepare(), you don't have to write your insert or update queries. For example: 
// Once you have a valid DB object named $db...
$table_name   = 'clientes';
$table_fields = array('codigo', 'nome', 'email');

$sth = $db->autoPrepare($table_name, $table_fields, DB_AUTOQUERY_INSERT);

$table_values = array(16006, 'Fabien', 'France@');

$res =& $db->execute($sth, $table_values);

if (PEAR::isError($sth)) {
    die($sth->getMessage());
}
?>

Outro

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$db =& DB::connect('pgsql://postgres:senha@localhost:5432/testes');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

// Once you have a valid DB object named $db...
$table_name   = 'clientes';
$table_fields = array('nome', 'email');
$table_values = array('Roberto', 'Brasil@');

$sth = $db->autoPrepare($table_name, $table_fields, DB_AUTOQUERY_UPDATE, 'codigo = 5600');

if (PEAR::isError($sth)) {
    die($sth->getMessage());
}

$res =& $db->execute($sth, $table_values);

if (PEAR::isError($res)) {
    die($res->getMessage());
}

?>

AutoExecute

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$db =& DB::connect('pgsql://postgres:senha@localhost:5432/testes');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

// Once you have a valid DB object named $db...
$table_name = 'clientes';

$fields_values = array(
    'codigo'      => 16007,
    'nome'    => 'Fabien',
    'email' => 'France@'
);

$res = $db->autoExecute($table_name, $fields_values, DB_AUTOQUERY_INSERT);

if (PEAR::isError($res)) {
    die($res->getMessage());
}

?>

Outro

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$db =& DB::connect('pgsql://postgres:senha@localhost:5432/testes');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

// Once you have a valid DB object named $db...
$table_name = 'clientes';

$fields_values = array(
    'nome'    => 'Fabien2',
    'email' => 'France2'
);

$res = $db->autoExecute($table_name, $fields_values, DB_AUTOQUERY_UPDATE, 'codigo = 1234');

if (PEAR::isError($res)) {
    die($res->getMessage());
}

?>

Tornando ON todas as opções de Portabilidade enquanto conecta

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$dsn='pgsql://postgres:senha@localhost:5432/testes';
$options = array(
    'debug'       => 2,
    'portability' => DB_PORTABILITY_ALL,
);

$db =& DB::connect($dsn, $options);
if (PEAR::isError($db)) {
    die($db->getMessage());
}

?>

Desconectar

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$dsn='pgsql://postgres:senha@localhost:5432/testes';
$options = array(
    'debug'       => 2,
    'portability' => DB_PORTABILITY_ALL,
);

$db =& DB::connect($dsn, $options);
if (PEAR::isError($db)) {
    die($db->getMessage());
}

$db->disconnect();
?>

GetAll

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$dsn='pgsql://postgres:senha@localhost:5432/testes';
$options = array(
    'debug'       => 2,
    'portability' => DB_PORTABILITY_ALL,
);

$db =& DB::connect($dsn, $options);
if (PEAR::isError($db)) {
    die($db->getMessage());
}

// Retorna um array de dados
$db->setFetchMode(DB_FETCHMODE_ASSOC);
$data =& $db->getAll('SELECT codigo, nome, email FROM clientes');

if (PEAR::isError($data)) {
    die($data->getMessage());
}

print_r($data);

?>

GetAssoc - ret array

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$dsn='pgsql://postgres:senha@localhost:5432/testes';
$options = array(
    'debug'       => 2,
    'portability' => DB_PORTABILITY_ALL,
);

$db =& DB::connect($dsn, $options);
if (PEAR::isError($db)) {
    die($db->getMessage());
}

// Retorna um array de dados
// Once you have a valid DB object named $db...
$data =& $db->getAssoc('SELECT codigo, nome FROM clientes');

if (PEAR::isError($data)) {
    die($data->getMessage());
}

print_r($data);

?>

getCol - dados de coluna

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$dsn='pgsql://postgres:senha@localhost:5432/testes';
$options = array(
    'debug'       => 2,
    'portability' => DB_PORTABILITY_ALL,
);

$db =& DB::connect($dsn, $options);
if (PEAR::isError($db)) {
    die($db->getMessage());
}

// Retorna dados de uma coluna
$data =& $db->getCol('SELECT codigo, nome FROM clientes');

if (PEAR::isError($data)) {
    die($data->getMessage());
}

print_r($data);
?>

Retorna primeira linha da primaira coluna

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$dsn='pgsql://postgres:senha@localhost:5432/testes';
$options = array(
    'debug'       => 2,
    'portability' => DB_PORTABILITY_ALL,
);

$db =& DB::connect($dsn, $options);
if (PEAR::isError($db)) {
    die($db->getMessage());
}

// Retorna primeira linha da primaira coluna
$data =& $db->getOne('SELECT nome FROM clientes');

if (PEAR::isError($data)) {
    die($data->getMessage());
}

echo "$data\n";?>

Outro

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$dsn='pgsql://postgres:senha@localhost:5432/testes';
$options = array(
    'debug'       => 2,
    'portability' => DB_PORTABILITY_ALL,
);

$db =& DB::connect($dsn, $options);
if (PEAR::isError($db)) {
    die($db->getMessage());
}

$data =& $db->getOne('SELECT nome FROM clientes WHERE codigo IN (?, ?)', array(3, 7));

if (PEAR::isError($data)) {
    die($data->getMessage());
}

echo "$data\n";

?>

Retorna Primeiro Registro

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$dsn='pgsql://postgres:senha@localhost:5432/testes';
$options = array(
    'debug'       => 2,
    'portability' => DB_PORTABILITY_ALL,
);

$db =& DB::connect($dsn, $options);
if (PEAR::isError($db)) {
    die($db->getMessage());
}

// Once you have a valid DB object named $db...
$data =& $db->getRow('SELECT codigo, nome FROM clientes',
        array(), DB_FETCHMODE_ORDERED);

if (PEAR::isError($data)) {
    die($data->getMessage());
}

print_r($data);

?>

limit

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$dsn='pgsql://postgres:senha@localhost:5432/testes';
$options = array(
    'debug'       => 2,
    'portability' => DB_PORTABILITY_ALL,
);

$db =& DB::connect($dsn, $options);
if (PEAR::isError($db)) {
    die($db->getMessage());
}

// Limit de, conte
$res =& $db->limitQuery('SELECT * FROM clientes', 1100, 30);

echo $res->numRows();

if (PEAR::isError($res)) {
    die($res->getMessage());
}

?>

Free

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$dsn='pgsql://postgres:senha@localhost:5432/testes';
$options = array(
    'debug'       => 2,
    'portability' => DB_PORTABILITY_ALL,
);

$db =& DB::connect($dsn, $options);
if (PEAR::isError($db)) {
    die($db->getMessage());
}

$res =& $db->query('SELECT nome, email FROM clientes');
while ($row =& $res->fetchRow()) {
    echo $row[1] . ', ' . $row[2] . "\n";
}
$res->free();

?>

DbError

<?php
// Create a valid DB object named $db
// at the beginning of your program...
require_once 'DB.php';

$dsn='pgsql://postgres:senha@localhost:5432/testes';
$options = array(
    'debug'       => 2,
    'portability' => DB_PORTABILITY_ALL,
);

$db =& DB::connect($dsn, $options);
if (PEAR::isError($db)) {
    /*
     * This is not what you would really want to do in
     * your program.  It merely demonstrates what kinds
     * of data you can get back from error objects.
     */
    echo 'Messagem Padrão: ' . $db->getMessage() . "\n";
    echo 'Código Padrão: ' . $db->getCode() . "\n";
    echo 'DBMS/User Message: ' . $db->getUserInfo() . "\n";
    echo 'DBMS/Debug Message: ' . $db->getDebugInfo() . "\n";
    exit;
}

?>

Referências

http://pear.php.net

http://vulcanonet.com/soft/?pack=pear_tut

http://www.phpbuilder.com/columns/allan20010115.php3?print_mode=1

Estes exemplos foram testados com:

Apache 2.2

PHP 5.1 (ambos no Xampp 1.5.1)

PostgreSQL 8.1.2