MDB2-Cheatsheet

When I started using MDB2, I found the possibilities quite overwhelming and so I decided to summarize everything you may need to start using it or want as a reference if you already use it and don't want to dig through the manuals for everything. Questions and hints on clarification are appreciated.

init a connection

$dsn = 'mysql://user:pass@host/db';

docs - factory

$mdb2 =& MDB2::factory(mixed $dsn, [array $options = false]);

if (PEAR::isError($mdb2)) {
    echo ($mdb2->getMessage());
}

querying

//Send a query to the database and return any results
$result = $mdb2->query(string $query, [mixed $types = null]);

docs - queryAll

//Execute the specified query, fetch all the rows of the result set into a two dimensional array and then frees the result set.
$result = $mdb2->queryAll(string $query, [array $types = null], [int $fetchmode = MDB2_FETCHMODE_DEFAULT], [boolean $rekey = false]);
//$rekey = if set to true, the $all will have the first column as its first dimension

docs - queryRow

//Execute the specified query, fetch the values from the first row of the result set into an array and then frees the result set.
$result = $mdb2->queryRow($string $query, [string $type = null]);

results

//Define the list of types to be associated with the columns of a given result set.
$types = array('integer', 'text', 'timestamp');
$result->setResultTypes($types)

docs - fetchRow

//Fetch and return a row of data
$result->fetchRow([int $fetchmode = MDB2_FETCHMODE_DEFAULT], [int $rownum = null]);

docs - fetchOne

//Fetch single column from the first row from a result set
$result->fetchOne([int $colnum = 0]);

docs - fetchAll

//Fetch and return a column of data (it uses fetchRow for that)
$result->fetchAll([int $fetchmode = MDB2_FETCHMODE_DEFAULT], [boolean $rekey = false]);
//$rekey = if set to true, the $all will have the first column as its first dimension

docs - fetchCol

//Fetch and return a column of data (it uses current for that)
$result->fetchCol([int $colnum = 0]);

docs - getColumnnames

//Retrieve the names of columns returned by the DBMS in a query result or from the cache.
$result->getColumnNames();

docs - numCols

//Count the number of columns returned by the DBMS in a query result.
$result->numCols();

docs - numRows

//returns the number of rows in a result object
$result->numRows();

docs - seek

//seek to a specific row in a result set
$result->seek($int);

docs - nextResult

//Move the internal result pointer to the next available result
$result->nextResult();

docs - free

//Free the internal resources associated with result.
$result->free();

fetch modes

MDB2_FETCHMODE_DEFAULT
MDB2_FETCHMODE_ORDERED
MDB2_FETCHMODE_ASSOC
MDB2_FETCHMODE_ORDERED | MDB2_FETCHMODE_FLIPPED
MDB2_FETCHMODE_ASSOC | MDB2_FETCHMODE_FLIPPED
MDB2_FETCHMODE_OBJECT

security

$mdb2->quote($stuff, $mode);
//text, integer, boolean, decimal, float, date, time, timestamp, blob

misc

//returns the autoincrement ID if supported or $id
$mdb2->lastInsertID();
$mdb2->nextID();