Home » Database » MDB » Manual

Introduction - DSN

Introduction - DSN – The Data Source Name

Description

To connect to a database through PEAR::MDB, 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:

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

Most variations are allowed:

phptype://username:password@protocol+hostspec:110//usr/db_file.mdb
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:

mysql  -> MySQL
pgsql  -> PostgreSQL
ibase  -> InterBase
mssql  -> Microsoft SQL Server
oci8   -> Oracle 7/8/8i
fbsql  -> FrontBase

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

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

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

Please note, that some features may be not supported by all database backends. Please refer to the PEAR MDB extensions status document located at: <pear base dir>/MDB/STATUS to get a detailed list about what features are supported by which backend.

Introduction - Connect

Introduction - Connect – Connecting and disconnecting a database

Description

To connect to a database you have to use the function MDB::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 recommended to check this return value with MDB::isError() . To disconnect use the method disconnect() from your database class instance.

Connect and disconnect

<?php
require_once 'MDB.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";

// MDB::connect will return a PEAR MDB object on success
// or an PEAR MDB Error object on error

$db = MDB::connect($dsn);

// With MDB::isError you can differentiate between an error or
// a valid connection.
if (MDB::isError($db)) {
    die ($db->getMessage());
}

....
// close conection
$db->disconnect();
?>

Introduction - Query

Introduction - Query – Performing a query against a database.

Description

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 MDB_Error object. Be sure to check it with MDB::isError(). On success, you get MDB_OK or when you set a SELECT-statement a result resource handle

A simple MDB query

<?php
// Once you have a valid MDB object...
$sql = "select * from clients";

$result = $db->query($sql);

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

// Continue on with your script
?>

Introduction - Fetch

Introduction - Fetch – Fetching rows from the query

Description

Fetch functions

In order to fetch data from a result resource you can use one if the following methods: fetchInto() , fetchOne() . , fetchRow() . , fetchCol() . and fetchAll() . All above mentioned methods except fetchOne() return the requested data encapsuled into a (multi-dimensional-)array, NULL on no more data or a MDB_Error , when an error occurs. All method prefixed with fetch() automatically free the result set.

Fetching a result set

<?php
...
$db = MDB::connect($dsn);
$res = $db->query("SELECT * FROM mytable");

// Get each row of data on each iteration until
// there are no more rows
while ($row = $db->fetchInto($res)) {
    $id = $row[0];
}

// If we are just interested in the first column of the first row
$id = $db->fetchOne($res);

// Since the fetch methods always free the result set
// we cannot loop across the result set but instead
// need to choose the proper fetch method
$data = $db->getAll($res);
foreach($data as $row)
{
    $id = $row[0];
}
?>

Select the format of the fetched row

The fetch modes supported are:

• MDB_FETCHMODE_ORDERED (default) The fetch*() returns an ordered array. The order is taken from the select statement.

  Fetch a ordered array

  <?php
  $res = $db->query('SELECT id, name, email FROM users');
  $row = $db->fetchRow($res, MDB_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];
  ?>

• MDB_FETCHMODE_ASSOC Returns an associative array with the column names as the array keys

  Fetch a assoc. array

  <?php
  $res = $db->query('SELECT id, name, email FROM users');
  $row = $db->fetchRow($res, MDB_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'];
  ?>

Set the format of the fetched row

You can set the fetch mode per result call or for your whole MDB instance.

Per call

<?php
while ($row = $db->fetchInto($res, MDB_FETCHMODE_ASSOC)) {
    $id = $row['id'];
}
?>

Once per instance

<?php
$db = MDB::connect($dsn);
// this will set a default fetchmode for this Pear MDB instance
// (for all queries)
$db->setFetchMode(MDB_FETCHMODE_ASSOC);

$result = $db->query(...);

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

Fetch rows by number

The PEAR MDB 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.

Fetching by number

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

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

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

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

Freeing the result set

It is recommended to finish the result set after processing in order to to save memory. Use freeResult() to do this.

Freeing

<?php
...
$res = $db->query('SELECT * FROM clients');
while ($row = $res->fetchInto($res)) {
...
}
$db->freeResult($res);
?>

Quick data retrieving

MDB provides some special ways to retrieve information from a query without the need of using fetch*() and loop throw results.

queryOne() retrieves the first result of the first column from a query

<?php
$numrows = $db->queryOne('select count(id) from clients');
?>

queryRow() returns the first row and returns it as an array.

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

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

<?php
$all_client_names = $db->queryCol('SELECT name FROM clients');
?>

The above sentence could return for example:

<?php
$all_client_names = array('Stig', 'Jon', 'Colin');
?>

getAll() fetches all the rows returned from a query. This method also has some advanced parameters still will also enable you to return the data as an associative array using the first column as the key.

<?php
$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 query*() 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 MDB functions they will return a MDB_Error object on errors.

Getting more information from query results

With MDB 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.

  <?php
  // Number of rows
  echo $db->numRows($res);
  ?>

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

  <?php
  // Number of cols
  echo $db->numCols($res);
  ?>

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

  <?php
  // 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.

  <?php
  // Table Info
  print_r($db->tableInfo($res));
  ?>

Don't forget to check if the returned result from your action is a MDB_Error object. If you get a error message like "MDB_Error: database not capable", means that your database backend doesn't support this action.

Introduction - Sequences

Introduction - Sequences – Database sequences

Description

Sequences are a way of offering unique IDs for data rows. If you do most of your work with e.g. MySQL, think of sequences as another way of doing AUTO_INCREMENT. It's quite simple, first you request an ID, and then you insert that value in the ID field of the new row you're creating. You can have more than one sequence for all your tables, just be sure that you always use the same sequence for any particular table. To get the value of this unique ID use nextId() , if a sequence doesn't exists, it will be created automaticlly.

Using a sequence

<?php
...
$id = $db->nextId('mySequence');
// Use the ID in your INSERT query
$res = $db->query("INSERT INTO myTable (id,text) VALUES ($id,'foo')");
...
?>

Introduction - Execute

Introduction - Execute – Prepare & Execute/ExecuteMultiple

Description

Purpose

prepareQuery() and executeQuery*() give you more power and flexibilty for query execution. You can use them, if you have to do more than one equal query (i.e. adding a list of adresses to a database) or if you want to support different databases, which have different implementations of the SQL standard.

Imagine you want to support two databases with different INSERT syntax:

Corresponding to create multi-lingual scripts you can create a array with queries like this:

<?php
$statement['db1']['INSERT_PERSON'] = "INSERT INTO person ( surname, name, age ) VALUES ( ?, ?, ? )" ;
$statement['db2']['INSERT_PERSON'] = "INSERT INTO person SET surname=?, name=?, age=?" ;
?>

Prepare

To use the features above, you have to do two steps. Step one is to prepareQuery the statement and the second is to executeQuery it.

Prepare() has to be called with the generic statement at least once. It returns a handle for the statement.

To create a generic statement is simple. Write the SQL query as usual, i.e.

Now check which parameters should be replaced while script runtime. Substitute this parameters with a placeholder.

So, thats all! Now you have a generic statement, required by prepareQuery() .

prepareQuery() can handle different types of placeholders or wildcards.

• ? - (recommended) stands for a scalar value like strings or numbers, the value will be quoted depending of the database
• ! - stands for a scalar value and will inserted into the statement "as is".
• & - requires an existing filename, the content of this file will be included into the statement (i.e. for saving binary data of a graphic file in a database)

Execute/ ExecuteMultiple

After preparing the statement, you can execute the query. This means to assign the variables to the prepared statement. To do this, executeQuery() requires two arguments, the statement handle of prepareQuery() and an array with the values to assign. The array has to be numerically ordered. The first entry of the array represents the first wildcard, the second the second wildcard etc. The order is independent from the used wildcard char.

Inserting data into a datebase

<?php
$alldata = array(  array(1, 'one', 'en'),
                   array(2, 'two', 'to'),
                   array(3, 'three', 'tre'),
                   array(4, 'four', 'fire'));
$sth = $dbh->prepareQuery("INSERT INTO numbers VALUES(?,?,?)");
foreach ($alldata as $row) {
    $dbh->executeQuery($sth, $row);
}
?>

In the example the query is done four times:

executeMultiple() works in the same way, but requires a two dimensional array. So you can avoid the explicit foreach in the eample above.

Using executeMultiple() instead of executeQuery()

<?php
...
$alldata = array(  array(1, 'one', 'en'),
                   array(2, 'two', 'to'),
                   array(3, 'three', 'tre'),
                   array(4, 'four', 'fire'));
$sth = $dbh->prepareQuery("INSERT INTO numbers VALUES(?,?,?)");
$dbh->executeMultiple($sth, $alldata);
}
?>

The result is the same. If one of the records failed, the unfinished records will not be executed.

If executeQuery*() fails a MDB_Error, else MDB_OK will returned.

MDB

MDB – Main class

Description

The main MDB class is simply a container class with some static methods for creating MDB objects.

MDB::connect()

MDB::connect() – connects to database

Synopsis

object MDB::connect ( string $dsn , boolean $options = false )

Description

Creates a new MDB connection object and connect to the specified database

Parameter

string $dsn

Data Source Name. See the "DSN" section for further information.

boolean $options

If $options is TRUE the connection will be persistent (requires support by database driver). Default is FALSE. In future releases, this parameter will be an array and take different options depending on the database.

Return value

object - the created MDB connection object, or a MDB_Error object on error.

Throws

Note

This function should be called statically.

See

MDB::disconnect() , Tutorial - Connect()

MDB::isError()

MDB::isError() – checks for an error

Synopsis

boolean MDB::isError ( mixed $value )

Description

Checks whether a result code from a MDB method is a MDB_Error object or not.

Parameter

mixed $value

Variable to check

Return value

boolean - TRUE, if $value is a MDB_Error object

Note

This function should be called statically.

MDB_Common

MDB_Common – Interface for database access

Description

MDB_Common is an interface class; that provides all methods to query a specific database. An instance of a database specific class will be returned by the MDB::connect() method.

MDB_Common::affectedRows()

MDB_Common::affectedRows() – Number of affected rows

Synopsis

integer affectedRows ( )

Description

Number of affected rows by a query

Return value

integer - number of rows or MDB_Error when fail

Throws

Note

This function can not be called statically.

See

query()

MDB_Common::createSequence()

MDB_Common::createSequence() – create a new sequence

Synopsis

integer createSequence ( string $seq_name , integer $start )

Description

See "Introduction - Sequences"

Parameter

string $seq_name

name of the new sequence to create

integer $start

starting value of the sequence

Return value

integer - MDB_OK or MDB_Error, if fail

Throws

Note

This function can not be called statically.

See

MDB::nextId() , MDB::dropSequence()

MDB_Common::currId()

MDB_Common::currId() – returns the current free id of a sequence

Synopsis

resource currId ( string $seq_name )

Description

See "Introduction - Sequences"

Parameter

string $seq_name

name of the sequence

Return value

resource - a free id or a MDB_Error, if fail

Throws

Note

This function can not be called statically.

See

createSequence() , dropSequence()

MDB_Common::disconnect()

MDB_Common::disconnect() – disconnect from a database

Synopsis

boolean MDB::disconnect ( )

Description

Disconnects from a database

Return value

boolean - Returns TRUE on success, FALSE on failure.

Note

This function can not be called statically.

See

MDB::connect() , Tutorial - Connect()

MDB_Common::dropSequence()

MDB_Common::dropSequence() – deletes a sequence

Synopsis

integer dropSequence ( string $seqName )

Description

See "Introduction - Sequences"

Parameter

string $seqName

name of the sequence to delete

Return value

• integer - MDB_OK or MDB_Error, if fail

Throws

Note

This function can not be called statically.

See

MDB_Common::createSequence() , MDB_Common::nextId()

MDB_Common::execute()

MDB_Common::execute() – executes a prepared SQL statement

Synopsis

mixed execute ( resource $stmt , array $types = null , array $params = array() , array $param_types = null )

Description

execute() joins the prepared SQL statement from prepareQuery() with the given data and executes the SQL query.

Parameter

resource $stmt

query handle from prepareQuery()

array $types

if supplied, the types of the columns in the result set will be set for fetching

array $params

a numeric array containing the data to insert into the query

array $param_types

if supplied, the values in $param will automatically be set to the passed datatypes

Return value

mixed - a resource id/MDB_OK or a MDB_Error, if fail

Throws

Note

This function can not be called statically.

See

prepareQuery() , executeMultiple()

MDB_Common::executeQuery()

MDB_Common::executeQuery() – executes a prepared SQL statement

Synopsis

mixed executeQuery ( resource $stmt , array $types = null )

Description

executeQuery() joins the prepared SQL statement from prepareQuery() with the data that was set using one of the setParam() methods and executes the SQL query.

Parameter

resource $stmt

query handle from prepareQuery()

array $types

if supplied, the types of the columns in the result set will be set for fetching

Return value

mixed - a resource id/MDB_OK or a MDB_Error, if fail

Throws

Note

This function can not be called statically.

See Introduction - Execute for general using and an example.

See

prepareQuery() , executeMultiple()

MDB_Common::executeMultiple()

MDB_Common::executeMultiple() – repeated execution of a prepared SQL statement

Synopsis

mixed executeMultiple ( resource $stmt , array $types = null , array $params = array() , array $param_types = null , array $data )

Description

executeMultiple() joins the prepared SQL statement from prepareQuery() with the given data and does the SQL query for every "row" in the $data array.

Parameter

resource $stmt

query handle from prepareQuery()

array $types

if supplied, the types of the columns in the result set will be set for fetching

array $params

if supplied, prepareQuery()/ executeQuery() will be used with this array as execute parameters

array $param_types

if supplied, the values in $param will automatically set to the passed datatypes

array array $data

a numeric array containing the data to insert into the query

Return value

mixed - a resource id/MDB_OK or a MDB_Error, if fail

Throws

Note

This function can not be called statically.

If an error occurs during execution, the function will be stopped. Possible remaining data will be unprocessed.

See Introduction - Execute for general using and an example.

See

prepareQuery() , executeQuery()

MDB_Common::fetchAll()

MDB_Common::fetchAll() – fetch result set as a nested array

Synopsis

array &fetchAll ( resource $result , integer $fetchmode = MDB_FETCHMODE_DEFAULT , boolean $rekey = false , boolean $force_array = false , boolean $group = false )

Description

Fetch the entire result set of a result set and return it into a nested array and free the result set.

Parameter

resource $result

a valid resource returned by query() or executeQuery()

integer $fetchmode

the fetch mode to use

boolean $rekey

if set to TRUE the array result be modified as follows: If the result set contains more than two columns, the value will be an array of the values from column 2 to n. If the result set contains only two columns, the returned value will be a scalar with the value of the second column (unless forced to an array with the $force_array parameter).

boolean $force_array

used only if the query returns exactly two columns. If TRUE, the values of the returned array will be one-element arrays instead of scalars.

boolean boolean $group

if TRUE, the values of the returned array is wrapped in another array. If the same key value (in the first column) repeats itself, the values will be appended to this array instead of overwriting the existing values.

Return value

array - an nested array or a MDB_Error, if fail.

Throws

Note

This function can not be called statically.

See

query() , limitQuery() , prepareQuery() , executeQuery() , fetchRow() , fetchOne() , fetchCol()

MDB_Common::fetchCol()

MDB_Common::fetchCol() – Fetch a single column

Synopsis

array &fetchCol ( resource $result , mixed $colnum = 0 )

Description

Fetch a single column from a result set of a result set and free the result set.

Parameter

resource $result

a valid resource returned by query() or executeQuery()

array $params

if supplied, prepareQuery()/ executeQuery() will be used with this array as execute parameters

mixed $colnum

which column to return (integer [column number, starting at 0] or string [column name])

Return value

array - the first row of results as an array indexed from 0 or a MDB_Error, if fail

Throws

Note

This function can not be called statically.

See

query() , limitQuery() , prepareQuery() , executeQuery() , fetchRow() , fetchOne() , fetchAll()

MDB_Common::fetchOne()

MDB_Common::fetchOne() – fetch the first column of the first row

Synopsis

mixed &fetchOne ( resource $result )

Description

Fetch the first column of the first row of data returned from a result set and free the result set.

Parameter

resource $result

a valid resource returned by query() or executeQuery()

Return value

mixed - the returned value or a MDB_Error, if fail

Throws

Note

This function can not be called statically.

See

query() , limitQuery() , prepareQuery() , executeQuery() , fetchRow() , fetchCol() , fetchCol()

MDB_Common::fetchRow()

MDB_Common::fetchRow() – fetch the first row

Synopsis

array &fetchRow ( resource $result , integer $fetchmode = MDB_FETCHMODE_DEFAULT , integer $rownum = null )

Description

Fetch the first row of data returned from a result set and free the result set.

Parameter

resource $result

a valid resource returned by query() or executeQuery()

integer $fetchmode

the fetch mode to use, default is MDB_FETCHMODE_DEFAULT

integer $rownum

the row number to fetch

Return value

array - the first row of results as an array indexed from 0 or a MDB_Error, if fail

Throws

Note

This function can not be called statically.

See

query() , limitQuery() , prepareQuery() executeQuery() , fetchCol() , fetchRow()

MDB_Common::fetchInto()

MDB_Common::fetchInto() – fetch a row into a variable

Synopsis

mixed fetchInto ( resource $result , integer $fetchMode = MDB_FETCHMODE_DEFAULT , integer $rownum = null )

Description

Fetch a row and return data in an array.

Parameter

resource $result

result identifier

integer $fetchMode

format of fetched row

integer $rownum

the row number to fetch

Return value

mixed - an array or NULL, if no more rows

MDB_Common::freeResult()

MDB_Common::freeResult() – delete the result set

Synopsis

boolean freeResult ( resource $result )

Description

Deletes the result set and frees the memory occupied by the result set.

Parameter

resource $result

A valid resource returned by query() or executeQuery()

Return value

• boolean - Returns TRUE on success, FALSE on failure.

Note

This function can not be called statically.

MDB_Common::getAll()

MDB_Common::getAll() – Fetch all rows

Synopsis

array &getAll ( string $query , array $types = null , array $params = array() , array $param_types = null , integer $fetchmode = MDB_FETCHMODE_DEFAULT )

Description

Fetch the entire result set of a query and return it into a nested array. The function takes care of doing the query and freeing the results when finished.

Parameter

string $query

the SQL query

array $types

if supplied, the types of the columns in the result set will be set for fetching

array $params

if supplied, prepareQuery()/ executeQuery() will be used with this array as execute parameters

array $param_types

if supplied, the values in $param will automatically set to the passed datatypes

integer $fetchmode

the fetch mode to use, default is MDB_FETCHMODE_DEFAULT

Return value

• array - an nested array or a MDB_Error, if fail

Throws

Note

This function can not be called statically.

See

query() , limitQuery() , prepareQuery() executeQuery() , getCol() , getRow() , getAssoc() , getOne()

MDB_Common::getAssoc()

MDB_Common::getAssoc() – fetch result set as associative array

Synopsis

array &getAssoc ( string $query , array $types = null , array $params = array() , array $param_types = null , integer $fetchmode = MDB_FETCHMODE_DEFAULT , boolean $force_array = false , boolean $group = false )

Description

Fetch the entire result set of a query and return it as an associative array using the first column as the key. The function takes care of doing the query and freeing the results when finished. If the result set contains more than two columns, the value will be an array of the values from column 2 to n. If the result set contains only two columns, the returned value will be a scalar with the value of the second column (unless forced to an array with the $force_array parameter).

Parameter

string $query

the SQL query

array $types

if supplied, the types of the columns in the result set will be set for fetching

array $params

if supplied, prepareQuery()/ executeQuery() will be used with this array as execute parameters

array $param_types

if supplied, the values in $param will automatically set to the passed datatypes

integer $fetchmode

the fetch mode to use

boolean $force_array

used only if the query returns exactly two columns. If TRUE, the values of the returned array will be one-element arrays instead of scalars.

boolean boolean $group

if TRUE, the values of the returned array is wrapped in another array. If the same key value (in the first column) repeats itself, the values will be appended to this array instead of overwriting the existing values.

Return value

array - associative array with results from the query.

Throws

Note

This function can not be called statically.

See

query() , limitQuery() , prepareQuery() , executeQuery() , getRow() , getOne() , getCol()

MDB_Common::getCol()

MDB_Common::getCol() – Fetch a single column

Synopsis

array &getCol ( string $query , string $type = null , array $params = array() , array $param_types = null , mixed $colnum = 0 )

Description

Fetch a single column from a result set of a query. The function takes care of doing the query and freeing the results when finished.

Parameter

string $query

the SQL query

string $type

if supplied, the type of the column in the result set will be set for fetching

array $params

if supplied, prepareQuery()/ executeQuery() will be used with this array as execute parameters

array $param_types

if supplied, the values in $param will automatically set to the passed datatypes

mixed $colnum

which column to return (integer [column number, starting at 0] or string [column name])

Return value

array - the first row of results as an array indexed from 0 or a MDB_Error, if fail

Throws

Note

This function can not be called statically.

See

query() , limitQuery() , prepareQuery() , executeQuery() , getRow() , getOne() , getAssoc()

MDB_Common::getOne()

MDB_Common::getOne() – fetch the first column of the first row

Synopsis

mixed &getOne ( string $query , string $type = null , array $params = array() , array $param_types = null )

Description

Fetch the first column of the first row of data returned from a query. The function takes care of doing the query and freeing the results when finished.

Parameter

string $query

the SQL query or the statement to prepare

string $type

if supplied, the type of the column in the result set will be set for fetching

array $params

if supplied, prepareQuery()/ executeQuery() will be used with this array as execute parameters

array $param_types

if supplied, the values in $param will automatically set to the passed datatypes

Return value

mixed - the returned value or a MDB_Error, if fail

Throws

Note

This function can not be called statically.

See

query() , limitQuery() , prepareQuery() , executeQuery() , getRow() , getCol() , getCol() , getCol()

MDB_Common::getRow()

MDB_Common::getRow() – fetch the first row

Synopsis

array &getRow ( string $query , array $types = null , array $params = array() , array $param_types = null , integer $fetchmode = MDB_FETCHMODE_DEFAULT )

Description

Fetch the first row of data returned from a query. The function takes care of doing the query and freeing the results when finished.

Parameter

string $query

the SQL query

array $types

if supplied, the types of the columns in the result set will be set for fetching

array $params

if supplied, prepareQuery()/ executeQuery() will be used with this array as execute parameters

array $param_types

if supplied, the values in $param will automatically set to the passed datatypes

integer $fetchmode

the fetch mode to use, default is MDB_FETCHMODE_DEFAULT

Return value

array - the first row of results as an array indexed from 0 or a MDB_Error, if fail

Throws

Note

This function can not be called statically.

See

query() , limitQuery() , prepareQuery() executeQuery() , getCol() , getRow() , getAssoc()

MDB_Common::getTextValue()

MDB_Common::getTextValue() – quotes a string

Synopsis

string MDB::getTextValue ( string $string )

Description

Quotes a string database-dependent, so it can be safely used in a query

Parameter

string $string

the input string to quote

Return value

string - the quoted string.

Note

This function can not be called statically.

MDB_Common::limitQuery()

MDB_Common::limitQuery() – send a limited query to the database

Synopsis

mixed &limitQuery ( string $query , array $types , integer $from , integer $count )

Description

Executes a SQL query, but fetches only the specificed count of rows. It is an emulation of the MySQL LIMIT option.

Parameter

string $query

the SQL query

array $types

if supplied, the types of the columns in the result set will be set for fetching

integer $from

the row to start to fetch

integer $count

the numbers of rows to fetch

Return value

mixed - a new a resource id/MDB_OK or a MDB_Error, if fail

Throws

Note

This function can not be called statically.

This module is EXPERIMENTAL. That means that the behaviour of these functions, these function names, in concreto ANYTHING documented here can change in a future release of this package WITHOUT NOTICE. Be warned, and use this module at your own risk.

Depending on the database you will not really get more speed compared to query() . The advantage of limitQuery() is the deleting of unneeded rows in the resultset, as early as possible. So this can decrease memory usage.

See

query()

MDB_Common::nextId()

MDB_Common::nextId() – returns the next free id of a sequence

Synopsis

resource nextId ( string $seq_name , boolean $onDemand = true )

Description

See "Introduction - Sequences"

Parameter

string $seq_name

name of the sequence

boolean $onDemand

when TRUE the sequence is automatic created, if it not exists.

Return value

resource - a free id or a MDB_Error, if fail

Throws

Note

This function can not be called statically.

See

createSequence() , dropSequence()

MDB_Common::numCols()

MDB_Common::numCols() – get number of columns

Synopsis

integer numCols ( resource $result )

Description

Get the number of columns of the rows in a result set.

Parameter

resource $result

a valid resource returned by query() or executeQuery()

Return value

integer - number of columns

Note

This function can not be called statically.

See

MDB_Common::numRows()

MDB_Common::numRows()

MDB_Common::numRows() – get number of rows

Synopsis

integer numRows ( resource $result )

Description

Get the number of rows in a result set.

Parameter

resource $result

a valid resource returned by query() or executeQuery()

Return value

integer - number of rows

Note

This function can not be called statically.

See

MDB_Common::numCols()

MDB_Common::prepareQuery()

MDB_Common::prepareQuery() – prepares a SQL statement

Synopsis

resource prepareQuery ( string $query )

Description

Prepares a query for execution with executeQuery() .

Parameter

string $query

the query to prepareQuery

Return value

resource - the query handle

Note

This function can not be called statically.

See Introduction - Execute for general using and an example.

See

executeQuery() , executeQuery()

MDB_Common::query()

MDB_Common::query() – send a query to the database

Synopsis

mixed &query ( string $query , array $types )

Description

Executes a SQL query

Parameter

string $query

the SQL query

array $types

if supplied, the types of the columns in the result set will be set for fetching

Return value

mixed - a new a resource id/MDB_OK or a MDB_Error, if fail

Throws

Note

This function can not be called statically.

MDB_Common::queryAll()

MDB_Common::queryAll() – fetch result set as a nested array

Synopsis

array &queryAll ( string $query , array $types = null , integer $fetchmode = MDB_FETCHMODE_DEFAULT , boolean $rekey = false , boolean $force_array = false , boolean $group = false )

Description

Fetch the entire result set of a query and return it into a nested array. The function takes care of doing the query and freeing the results when finished.

Parameter

string $query

the SQL query

array $types

if supplied, the types of the columns in the result set will be set for fetching

integer $fetchmode

the fetch mode to use

boolean $rekey

if set to TRUE the array result be modified as follows: If the result set contains more than two columns, the value will be an array of the values from column 2 to n. If the result set contains only two columns, the returned value will be a scalar with the value of the second column (unless forced to an array with the $force_array parameter).

boolean $force_array

used only if the query returns exactly two columns. If TRUE, the values of the returned array will be one-element arrays instead of scalars.

boolean boolean $group

if TRUE, the values of the returned array is wrapped in another array. If the same key value (in the first column) repeats itself, the values will be appended to this array instead of overwriting the existing values.

Return value

array - an nested array or a MDB_Error, if fail.

Throws

Note

This function can not be called statically.

See

query() , limitQuery() , prepareQuery() , executeQuery() , queryRow() , queryOne() , queryCol()

MDB_Common::queryCol()

MDB_Common::queryCol() – Fetch a single column

Synopsis

array &queryCol ( string $query , string $type = null , mixed $colnum = 0 )

Description

Fetch a single column from a result set of a query. The function takes care of doing the query and freeing the results when finished.

Parameter

string $query

the SQL query

string $type

if supplied, the type of the column in the result set will be set for fetching

array $params

if supplied, prepareQuery()/ executeQuery() will be used with this array as execute parameters

array $param_types

if supplied, the values in $param will automatically set to the passed datatypes

mixed $colnum

which column to return (integer [column number, starting at 0] or string [column name])

Return value

array - the first row of results as an array indexed from 0 or a MDB_Error, if fail

Throws

Note

This function can not be called statically.

See

query() , limitQuery() , prepareQuery() , executeQuery() , queryRow() , queryOne() , queryAll()

MDB_Common::queryOne()

MDB_Common::queryOne() – fetch the first column of the first row

Synopsis

mixed &queryOne ( string $query , string $type = null )

Description

Fetch the first column of the first row of data returned from a query. The function takes care of doing the query and freeing the results when finished.

Parameter

string $query

the SQL query or the statement to prepare

string $type

if supplied, the type of the cell in the result set will be set for fetching

Return value

mixed - the returned value or a MDB_Error, if fail

Throws

Note

This function can not be called statically.

See

query() , limitQuery() , prepareQuery() , executeQuery() , queryRow() , queryCol() , queryCol()

MDB_Common::queryRow()

MDB_Common::queryRow() – fetch the first row

Synopsis

array &queryRow ( string $query , array $types = null , integer $fetchmode = MDB_FETCHMODE_DEFAULT , integer $rownum = null )

Description

Fetch the first row of data returned from a query. The function takes care of doing the query and freeing the results when finished.

Parameter

string $query

the SQL query

array $types

if supplied, the types of the columns in the result set will be set for fetching

integer $fetchmode

the fetch mode to use, default is MDB_FETCHMODE_DEFAULT

integer $rownum

the row number to fetch

Return value

array - the first row of results as an array indexed from 0 or a MDB_Error, if fail

Throws

Note

This function can not be called statically.

See

query() , limitQuery() , prepareQuery() executeQuery() , queryCol() , queryRow()

MDB_Common::setFetchMode()

MDB_Common::setFetchMode() – sets the default fetch mode

Synopsis

void setFetchMode ( integer $fetchmode , string $object_class = null )

Description

Sets the fetch mode used by default on queries for the connection.

Parameter

integer $fetchmode

MDB_FETCHMODE_ORDEREDor MDB_FETCHMODE_ASSOC, all possibly bit-wise OR'ed with MDB_FETCHMODE_FLIPPED. See "Introduction - Fetch" for further information.

Return value

void - NULL, if ok(!)

Throws

Note

This function can not be called statically.

MDB_Common::tableInfo()

MDB_Common::tableInfo() – get table info from a query

Synopsis

boolean tableInfo ( resource $result , int $mode = null )

Description

Fetch table information from result query.

Return value

Returns an associative array of table and field information.

This function is currently not documented.

Note

This function can not be called statically.

MDB_Error

MDB_Error – MDB Error object

Description

In case of failure, most of the MDB functions return a MDB_Error object which contains information about the error. MDB_Error offers the same functions as PEAR_Error.