Querying
Description
PEAR MDB2 provides several methods for querying databases. The most direct method is query(). It takes a SQL query string as an argument. There are two possible returns: A new MDB2_Result object for queries that return results (such as SELECT queries), or a MDB2_Error object on failure. It should not be used with statements that manipulate data (such as INSERT queries)
Doing a query
<?php
// Create a valid MDB2 object named $mdb2
// at the beginning of your program...
require_once 'MDB2.php';
$mdb2 =& MDB2::connect('pgsql://usr:pw@localhost/dbnam');
if (PEAR::isError($mdb2)) {
die($mdb2->getMessage());
}
// Proceed with a query...
$res =& $mdb2->query('SELECT * FROM clients');
// Always check that result is not an error
if (PEAR::isError($res)) {
die($res->getMessage());
}
// Disconnect
$mdb2->disconnect();
?>
exec() should be used for manipulation queries. There are two possible returns: An integer denoting the number of affected rows for statements that manipulate data (such as INSERT queries), or a MDB2_Error object on failure. It should not be used with statements that return results (such as SELECT queries)
Using exec to manipulate data
<?php
// Once you have a valid MDB2 object named $mdb2...
$sql = "INSERT INTO clients (name, address) VALUES ($name, $address)";
$affected =& $mdb2->exec($sql);
// Always check that result is not an error
if (PEAR::isError($affected)) {
die($affected->getMessage());
}
?>
Data types
MDB2 supports a number of data types across all drivers. These can be set for result sets at query or prepare time or using the setResultTypes() method. You can find an overview of the supported data types and their format here.
Limiting rows and reading from an offset
In order to read/write to only a limited number of rows from a result set and/or to start reading from a specific offset the setLimit() can be called prior to issueing the query. The limit and offset will only affected the next method call that will issue a query or prepare a statement and will automatically be reset after issueing the query. This also applies to any internal queries issues inside MDB2. Note that limit may not work with DML statements on RDBMS that emulate limit support and no error will be raised.
Using setLimit with query and exec
<?php
// Once you have a valid MDB2 object named $mdb2...
$sql = "SELECT * FROM clients";
// read 20 rows with an offset of 10
$mdb2->setLimit(20, 10);
$affected =& $mdb2->exec($sql);
$sql = "DELETE FROM clients";
if ($mdb2->supports('limit_queries') === 'emulated') {
echo 'offset will likely be ignored'
}
// only delete 10 rows
$mdb2->setLimit(10);
$affected =& $mdb2->exec($sql);
?>