Querying

Querying – Performing queries

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(2010);
$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);

?>
Connecting and disconnecting a database (Previous) Quote values in a suitable format to compose a query. (Next)
Last updated: Tue, 26 Aug 2014 — Download Documentation
Do you think that something on this page is wrong? Please file a bug report or add a note.
View this page in:

User Notes:

Note by: Babbomaria
Is there a way to implement a timeout with in sql query execution?
Note by: petr.match@gmail.com
Solution (workaround) to security risk with stored plaintext password, mentioned in posts below:

<?php

// put this code just after initializing db connection (i.e. after MDB2::connect)
for ($__i 0$__i count($GLOBALS['_MDB2_databases']); $__i++) {
    
$GLOBALS['_MDB2_databases'][$__i]->dsn['password'] = '* hidden *';
    
$GLOBALS['_MDB2_databases'][$__i]->connected_dsn['password'] = '* hidden *';
}


?>


Enjoy!
Petr
Note by: till
I agree that a password in plaintext can lead to an issue - then again, you should not put that object on to your website. Log it, email it, whatever - straight output is a bad idea.

Therefor, this is more of a non-issue.
Note by: ropox.net
i think that ericfraz at gmail.com has a point, BUT this is done so we can develop the application and debug it without having to go through changing settings in other areas just to see an error. it is up to the developer how he will handle all errors and particularly with the Error object. for e.g. on a production environment you never print the message from an error in your database, but you either simply email the administrator and then kill your page..
Note by: isaac
Anyone tried to use MDB2's locking features? I can't seem to find any documentation.
Note by: cweiske
setLimit() is needed to make your code portable between database servers. Some use LIMIT X,Y while others support LIMIT X OFFSET Y only.
Note by: ericfraz@gmail.com
I don't know who came up with this idea of the setLimit function but it is a VERY bad idea. Why add a function that could cause so much potential trouble with performance and compatibly vs just limiting the query itself?

The mdb2_result_mysql Object CONTAINS THE DB PASSWORD!!!
This is a horrible thing for me to see in my print_r output and has to lead to a security risk.

mdb2_result_mysql Object
(
[db] => mdb2_driver_mysql Object
(
[db_index] => 1
[dsn] => Array
(
[phptype] => mysql
[dbsyntax] => mysql
[username] => dbuser
[password] => myrealpassword!
[protocol] => tcp
[hostspec] => localhost
[port] =>
[socket] =>
[database] =>
[mode] =>
)



BTW this is information, it needs to be said and said over and over util these things are changed.