Home » Database » DB » Manual

Introduction - DSN

Introduction - DSN – The Data Source Name

Description

To connect to a database through PEAR::DB, 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. When using ODBC as the phptype, set this to the DBMS type the ODBC driver is connecting to. Examples: access, db2, mssql, navision, solid, 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
• option: Additional connection options in URI query string format. options get separated by &

The format of the supplied DSN is in its fullest form:

phptype(dbsyntax)://username:password@protocol+hostspec/database?option=value

Most variations are allowed:

phptype://username:password@protocol+hostspec:110//usr/db_file.db
phptype://username:password@hostspec/database
phptype://username:password@hostspec
phptype://username@hostspec
phptype://hostspec/database
phptype://hostspec
phptype:///database
phptype:///database?option=value&anotheroption=anothervalue
phptype(dbsyntax)
phptype

The currently supported database backends are:

dbase  -> dBase
fbsql  -> FrontBase (functional since DB 1.7.0)
ibase  -> InterBase (functional since DB 1.7.0)
ifx    -> Informix
msql   -> Mini SQL (functional since DB 1.7.0)
mssql  -> Microsoft SQL Server (NOT for Sybase. Compile PHP --with-mssql)
mysql  -> MySQL (for MySQL <= 4.0)
mysqli -> MySQL (for MySQL >= 4.1) (requires PHP 5) (since DB 1.6.3)
oci8   -> Oracle 7/8/9
odbc   -> ODBC (Open Database Connectivity)
pgsql  -> PostgreSQL
sqlite -> SQLite
sybase -> Sybase

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

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

If your database, option values, username or password contain characters used to delineate DSN parts, you can escape them via URI hex encodings:

: = %3a   / = %2f   @ = %40
+ = %2b   ( = %28   ) = %29
? = %3f   = = %3d   & = %26

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

Example

Connect to database through a socket

mysql://user@unix(/path/to/socket)/pear

Connect to database on a non standard port

pgsql://user:pass@tcp(localhost:5555)/pear

Connect to SQLite on a Unix machine using options

sqlite:////full/unix/path/to/file.db?mode=0666

Connect to SQLite on a Windows machine using options

sqlite:///c:/full/windows/path/to/file.db?mode=0666

Connect to MySQLi using SSL

mysqli://user:pass@localhost/pear?key=client-key.pem&cert=client-cert.pem

Connecting to MS Access sometimes requires admin as the user name

odbc(access)://admin@/datasourcename

Connecting to ODBC with a specific cursor

odbc(access)://admin@/datasourcename?cursor=SQL_CUR_USE_ODBC

Introduction - Connect

Introduction - Connect – Connecting and disconnecting a database

Description

To connect to a database you have to use the function connect(), which requires a valid DSN as the first parameter. This parameter can either be a string or an array. If using an array, the array used gets merged with the default information:

$dsn = array(
    'phptype'  => false,
    'dbsyntax' => false,
    'username' => false,
    'password' => false,
    'protocol' => false,
    'hostspec' => false,
    'port'     => false,
    'socket'   => false,
    'database' => false,
);

Any elements you set override the defaults and the remainder stay at their defaults.

The second parameter is the optional $options array that can contain runtime configuration settings for this package. See setOption() for more information on the available settings.

In case of success you get a new instance of the database class. It is strongly recommended to check this return value with isError().

To disconnect use the method disconnect() from your database class instance.

Connect and disconnect

<?php
require_once 'DB.php';

$dsn = 'pgsql://someuser:apasswd@localhost/thedb';
$options = array(
    'debug'       => 2,
    'portability' => DB_PORTABILITY_ALL,
);

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

// ...

$db->disconnect();
?>

Connect using an array for the DSN information

<?php
require_once 'DB.php';

$dsn = array(
    'phptype'  => 'pgsql',
    'username' => 'someuser',
    'password' => 'apasswd',
    'hostspec' => 'localhost',
    'database' => 'thedb',
);

$options = array(
    'debug'       => 2,
    'portability' => DB_PORTABILITY_ALL,
);

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

When connecting to SQLite using a DSN array, the value of the mode element must be a string:

<?php
$dsn = array(
    'phptype'  => 'sqlite',
    'database' => 'thedb',
    'mode'     => '0644',
);
?>

Connect to MySQLi via SSL using an array for the DSN information

The ssl element of the $options array must be set to TRUE in order for SSL to work. Each of the extra elements in the $dsn array (key through cipher in the example below) are optional.

<?php
require_once 'DB.php';

$dsn = array(
    'phptype'  => 'mysqli',
    'username' => 'someuser',
    'password' => 'apasswd',
    'hostspec' => 'localhost',
    'database' => 'thedb',
    'key'      => 'client-key.pem',
    'cert'     => 'client-cert.pem',
    'ca'       => 'cacert.pem',
    'capath'   => '/path/to/ca/dir',
    'cipher'   => 'AES',
);

$options = array(
    'ssl' => true,
);

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

Connect to a PostgreSQL database via a socket

<?php
require_once 'DB.php';

$dsn = 'pgsql://someuser:apasswd@unix(/tmp)/thedb';
$options = array(
    'debug'       => 2,
    'portability' => DB_PORTABILITY_ALL,
);

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

See

DB_Error, setOption(), "Intro - Portability"

Introduction - Query

Introduction - Query – Performing queries

Description

PEAR DB provides several methods for querying databases. The most direct method is query(). It takes a SQL query string as an argument. There are three possible returns: a new DB_result object for queries that return results (such as SELECT queries), DB_OK for queries that manipulate data (such as INSERT queries) or a DB_Error object on failure.

Doing a query

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

$db =& DB::connect('pgsql://usr:pw@localhost/dbnam');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

// Proceed with a query...
$res =& $db->query('SELECT * FROM clients');

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

query() can be used instead of prepare() and execute(), if you set the $params parameter and your query uses placeholders.

Using query in prepare/execute mode with a scalar parameter

<?php
// Once you have a valid DB object named $db...
$sql  = 'select * from clients where clientid = ?';
$data = 53;

$res =& $db->query($sql, $data);

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

Using query in prepare/execute mode with an array parameter

<?php
// Once you have a valid DB object named $db...
$sql  = 'select * from clients where clientid = ? and statusid = ?';
$data = array(53, 4);

$res =& $db->query($sql, $data);

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

Introduction - Results

Introduction - Results – Obtaining data from query results

Description

Fetching Individual Rows From Query Results

The DB_result object provides two methods for fetching data from rows of a result set: fetchRow() and fetchInto().

fetchRow() returns the row's data. fetchInto() assigns the row's data to a variable you provide and returns DB_OK.

The result pointer gets moved to the next row each time these methods are called. NULL is returned when the end of the result set is reached.

DB_Error is returned if an error is encountered.

Fetching a result set

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

$db =& DB::connect('pgsql://usr:pw@localhost/dbnam');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

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

// 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] . "\n";
}

// 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] . "\n";
// }
?>

Formats of Fetched Rows

The data from the row of a query result can be placed into one of three constructs: an ordered array (with column numbers as keys), an associative array (with column names as keys) or an object (with column names as properties).

DB_FETCHMODE_ORDERED (default)

     
Array
(
    [0] => 28
    [1] => hi
)
     
    

DB_FETCHMODE_ASSOC

     
Array
(
    [a] => 28
    [b] => hi
)
     
    

DB_FETCHMODE_OBJECT

     
stdClass Object
(
    [a] => 28
    [b] => hi
)
     
    

NOTE: When a query contains the same column name more than once (such as when joining tables which have duplicate column names) and the fetch mode is DB_FETCHMODE_ASSOC or DB_FETCHMODE_OBJECT, the data from the last column with a given name will be the one returned. There are two immediate options to work around this issue:

• Use aliases in your query, for example People.Name AS PersonName
• Change the fetch mode to DB_FETCHMODE_ORDERED

TIP: If you are running into this issue, it likely indicates poor planning of the database schema. Either data is needlessly being duplicated or the same names are being used for different kinds of data.

How to Set Formats

You can set the fetch mode each time you call a fetch method and/or you can set the default fetch mode for the whole DB instance by using the setFetchMode() method.

Determining fetch mode per call

<?php
// Once you have a valid DB object named $db...
$res =& $db->query('SELECT * FROM users');

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

Changing default fetch mode

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

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

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

Fetch Rows by Number

The PEAR DB 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
// Once you have a valid DB object named $db...

// 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 (!$res->fetchInto($row, DB_FETCHMODE_ORDERED, $rowNum)) {
        break;
    }
    echo $row[0] . "\n";
}
?>

Getting Entire Result Sets

The DB_common object provides several methods that make data retrieval easy by combining the processes of running of the query string you provide, putting the returned information into a PHP data structure and freeing the results. These methods include getOne(), getRow(), getCol(), getAssoc() and getAll().

Freeing Result Sets

Once you finish using a result set, if your script continues for a while, it's a good idea to save memory by freeing the result set via Use free().

Freeing

<?php
// Once you have a valid DB object named $db...
$res =& $db->query('SELECT name, address FROM clients');
while ($res->fetchInto($row)) {
    echo $row['name'] . ', ' . $row['address'] . "\n";
}
$res->free();
?>

Getting More Information From Query Results

With DB there are four ways to retrieve useful information about the query result sets themselves:

numRows() tells how many rows are in a SELECT query result

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

numCols() tells how many columns are in a SELECT query result

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

affectedRows() tells how many rows were altered by a data change 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 columns in a SELECT query result

<?php
// Once you have a valid DB object named $db...
$res =& $db->query('SELECT * FROM phptest');
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());
?>

Checking for Errors

Don't forget to use isError() to check if your actions return a DB_Error object.

Introduction - Prepare & Execute

Introduction - Prepare & Execute – Prepare and execute SQL statements

Description

Purpose

prepare() and execute*() give you more power and flexibilty for query execution. Prepare/execute mode is helpful when you have to run the same query several times but with different values in it, such as adding a list of addresses into a database.

Another place prepare/execute is useful is supporting databases which have different SQL syntaxes. Imagine you want to support two databases with different INSERT syntax:

Correspondending 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 prepare the statement and the second is to execute it.

To start out, you need to prepare() a generic SQL statement. Create a generic statement by writing the SQL query as usual:

SELECT surname, name, age
    FROM person
    WHERE name = 'name_to_find' AND age < age_limit

Then substitute "placeholders" for the literal values which will be provided at run time:

SELECT surname, name, age
    FROM person
    WHERE name = ? AND age < ?

Then pass this SQL statement to prepare(), which returns a statement handle to be used when calling execute().

prepare() can handle different types of placeholders (a.k.a. wildcards).

• ? - (recommended) stands for a scalar value like strings or numbers. The value will be automatically escaped and quoted according to the current DBMS's requirements.
• ! - 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)

Use backslashes to escape placeholder characters if you don't want them to be interpreted as placeholders:

UPDATE foo SET col=? WHERE col='over \& under'

Execute

After preparing the statement, you can execute the query. This means to assign the variables to the prepared statement. To do this, execute() requires two arguments: the statement handle returned by prepare() and a scalar or array with the values to assign.

Passing scalars to execute()

<?php
// Once you have a valid DB object named $db...
$sth = $db->prepare('INSERT INTO numbers (number) VALUES (?)');
$db->execute($sth, 1);
$db->execute($sth, 8);
?>

When a prepared statement has multiple placeholders, you must use an array to pass the values to execute(). The first entry of the array represents the first placeholder, the second the second placeholder, etc. The order is independent of the type of placeholder used.

Passing an array to execute()

<?php
// Once you have a valid DB object named $db...
$sth = $db->prepare('INSERT INTO numbers VALUES (?, ?, ?)');

$data = array(1, 'one', 'en');
$db->execute($sth, $data);
?>

The values passed in $data must be literals. Do not submit SQL functions (for example CURDATE()). SQL functions that should be performed at execution time need to be put in the prepared statement. Similarly, identifiers (i.e. table names and column names) can not be used because the names get validated during the prepare phase.

ExecuteMultiple

DB contains a process for executing several queries at once. So, rather than having to execute them manually, like this:

Passing arrays to execute()

<?php
// Once you have a valid DB object named $db...
$alldata = array(array(1, 'one', 'en'),
                 array(2, 'two', 'to'),
                 array(3, 'three', 'tre'),
                 array(4, 'four', 'fire'));
$sth = $db->prepare('INSERT INTO numbers VALUES (?, ?, ?)');
foreach ($alldata as $row) {
    $db->execute($sth, $row);
}
?>

which would issue four queries:

INSERT INTO numbers VALUES ('1', 'one', 'en')
INSERT INTO numbers VALUES ('2', 'two', 'to')
INSERT INTO numbers VALUES ('3', 'three', 'tre')
INSERT INTO numbers VALUES ('4', 'four', 'fire')

you can use executeMultiple() to avoid the explicit foreach in the eample above:

Using executeMultiple() instead of execute()

<?php
// Once you have a valid DB object named $db...
$alldata = array(array(1, 'one', 'en'),
                 array(2, 'two', 'to'),
                 array(3, 'three', 'tre'),
                 array(4, 'four', 'fire'));
$sth = $db->prepare('INSERT INTO numbers VALUES (?, ?, ?)');
$db->executeMultiple($sth, $alldata);
?>

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

execute*() has three possible returns: a new DB_result object for queries that return results (such as SELECT queries), DB_OK for queries that manipulate data (such as INSERT queries) or a DB_Error object on failure

Introduction - autoPrepare & autoExecute

Introduction - autoPrepare & autoExecute – Automatically prepare and execute SQL statements

Description

Purpose

autoPrepare() and autoExecute() reduce the need to write boring INSERT or UPDATE SQL queries which are difficult to maintain when you add a field for instance.

Imagine you have a 'user' table with 3 fields (id, name and country). You have to write sql queries like:

INSERT INTO table (id, name, country) VALUES (?, ?, ?)
UPDATE table SET id=?, name=?, country=? WHERE ...

If you add a field ('birthYear' for example), you have to rewrite your queries which is boring and can lead to bugs (if you forget one query for instance).

autoPrepare

With autoPrepare(), you don't have to write your insert or update queries. For example:

<?php
// Once you have a valid DB object named $db...
$table_name   = 'user';
$table_fields = array('id', 'name', 'country');

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

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

In this example, autoPrepare() will build the following SQL query:

INSERT INTO user (id, name, country) VALUES (?, ?, ?)

And then, it will call prepare() with it.

To add records, you have to use execute() or executeMultiple() like:

<?php
// ... contining from the example above...
$table_values = array(1, 'Fabien', 'France');

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

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

So, you don't have to write any SQL INSERT queries! And it works with UPDATE queries too. For flexibility reasons, you have only to write the WHERE clause of the query. For instance:

<?php
// Once you have a valid DB object named $db...
$table_name   = 'user';
$table_fields = array('name', 'country');
$table_values = array('Bob', 'USA');

$sth = $db->autoPrepare($table_name, $table_fields,
                        DB_AUTOQUERY_UPDATE, 'id = 1');

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

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

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

autoPrepare() will build the following query:

UPDATE user SET name=?, country=? WHERE id=1

Then, it will call prepare() with it.

Be careful, if you don't specify any WHERE clause, all the records of the table will be updated.

autoExecute

Using autoExecute() is the easiest way to do insert or update queries. It is a mix of autoPrepare() and execute().

You only need an associative array (key => value) where keys are fields names and values are corresponding values of these fields. For instance:

<?php
// Once you have a valid DB object named $db...
$table_name = 'user';

$fields_values = array(
    'id'      => 1,
    'name'    => 'Fabien',
    'country' => 'France'
);

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

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

And that's all! The following query is built and executed:

INSERT INTO user (id, name, country)
  VALUES (1, 'Fabien', 'France')

And it's the same thing for UPDATE queries:

<?php
// Once you have a valid DB object named $db...
$table_name = 'user';

$fields_values = array(
    'name'    => 'Fabien',
    'country' => 'France'
);

$res = $db->autoExecute($table_name, $fields_values,
                        DB_AUTOQUERY_UPDATE, 'id = 1');

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

which prepares and executes the following query:

UPDATE user SET name='Fabien', country='France'
  WHERE id = 1

Be careful, if you don't specify any WHERE statement, all the records of the table will be updated.

The values passed in $data must be literals. Do not submit SQL functions (for example CURDATE()). SQL functions that should be performed at execution time need to be put in the prepared statement.

Introduction - Portability

Introduction - Portability – Database portability features

Description

Each database management system (DBMS) has its own behaviors. For example, some databases capitalize field names in their output, some lowercase them, while others leave them alone. These quirks make it difficult to port your scripts over to another server type. PEAR DB strives to overcome these differences so your program can switch between DBMS's without any changes.

You control which portability modes are enabled by using the portability configuration option. Configuration options are set via connect() and setOption().

The portability modes are bitwised, so they can be combined using | and removed using ^. See the examples section below on how to do this.

Portability Mode Constants

• DB_PORTABILITY_ALL turn on all portability features
• DB_PORTABILITY_DELETE_COUNT force reporting the number of rows deleted. Some DBMS's don't count the number of rows deleted when performing simple DELETE FROM tablename queries. This mode tricks such DBMS's into telling the count by adding WHERE 1=1 to the end of DELETE queries.
• DB_PORTABILITY_ERRORS makes certain error messages in certain drivers compatible with those from other DBMS's

  Error Code Re-mappings

  Driver	Description	Old Constant	New Constant
  mysql, mysqli	unique and primary key constraints	DB_ERROR_ALREADY_EXISTS	DB_ERROR_CONSTRAINT
  mysql, mysqli	not-null constraints	DB_ERROR_CONSTRAINT	DB_ERROR_CONSTRAINT_NOT_NULL
  odbc(access)	MS's ODBC driver mistakenly reports 'no such field' as code 07001, which means 'too few parameters.' When this option is on, that code gets remapped.	DB_ERROR_MISMATCH	DB_ERROR_NOSUCHFIELD

• DB_PORTABILITY_LOWERCASE convert names of tables and fields to lower case when using get*(), fetch*() and tableInfo()
• DB_PORTABILITY_NONE (default) turn off all portability features
• DB_PORTABILITY_NULL_TO_EMPTY convert null values to empty strings in data output by get*() and fetch*(). Needed because Oracle considers empty strings to be null, while most other DBMS's know the difference between empty and null.
• DB_PORTABILITY_NUMROWS enable hack that makes numRows() work in Oracle
• DB_PORTABILITY_RTRIM right trim the data output by get*() and fetch*()

Backwards Compatibility

Some of this functionality used to be handled by the now deprecated optimize option. For backwards compatibility, when this option is set to portability, the following databases get these portability modes turned on:

• oci8: DB_PORTABILITY_LOWERCASE and DB_PORTABILITY_DELETE_COUNT
• fbsql, mysql, mysqli, sqlite: DB_PORTABILITY_DELETE_COUNT

When the optimize option gets set to performance the portability mode is switched to DB_PORTABILITY_NONE.

Example

Turning on all portability options while connecting

<?php
require_once 'DB.php';

$dsn = 'mysql://user:password@host/database'
$options = array(
    'debug'       => 2,
    'portability' => DB_PORTABILITY_ALL,
);

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

Using setOption() to enable portability for lowercasing and trimming

<?php
// Once you have a valid DB object named $db...
$db->setOption('portability',
        DB_PORTABILITY_LOWERCASE | DB_PORTABILITY_RTRIM);
?>

Using setOption() to enable all portability options except trimming

<?php
// Once you have a valid DB object named $db...
$db->setOption('portability',
        DB_PORTABILITY_ALL ^ DB_PORTABILITY_RTRIM);
?>

Introduction - Sequences

Introduction - Sequences – Sequences and auto-incrementing

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 automatically.

The sequence is automatically incremented each time nextId() is called.

Using a sequence

<?php
// Once you have a valid DB object named $db...
$id = $db->nextId('mySequence');
if (PEAR::isError($id)) {
    die($id->getMessage());
}

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

Note

When using PEAR DB's sequence methods, we strongly advise using these methods for all procedures, including the creation of the sequences. Do not use PEAR DB's methods to access sequences that were created directly in the DBMS.

If you have a compelling reason to ignore this advice, be aware that the $seq_name argument given to all of PEAR DB's sequence methods are modified before DB calls the underlying DBMS.

$seq_name is passed through PHP's sprintf() function using the value from the seqname_format option as sprintf()'s format argument. The default seqname_format is %s_seq. So, for example, if you use person_id_sequence as the $seq_name, PEAR DB will change that name to person_id_sequence_seq when querying the DBMS about creating/accessing/updating the sequence.

The seqname_format can be modified when connect()'ing or via setOption().

Please note that you may need to change the seqname_format option to represent a quoted form of the sequence name if the sequence names contain characters that cannot be used unquoted within queries. For example, PostgreSQL users with uppercase table names will probably need to use "%s_seq" (note the quotes) for queries to work as expected.

See

createSequence(), nextId(), dropSequence()

Overview - get* methods

Overview - get* methods – Comparison of get* methods

Description

This page gives an overview about the methods that allow you to fetch entire result sets without looping over single result rows: getOne(), getRow(), getCol(), getAssoc() and getAll().

Our data source is the following table:

A simple var_dump() is used to make clear in which use cases the methods can be used:

<?php
require_once 'DB.php';
$db = DB::connect('mysql://user:pass@localhost/example');

var_dump(
    $db->getOne('SELECT nickname, id, email FROM users')
);
?>

getOne

getOne() fetches the first column of the first row.

string(5) "pinky"

getRow

getRow() fetches the first row.

array(3) {
  [0] => string(5)  "pinky"
  [1] => string(1)  "1"
  [2] => string(20) "pinky@domination.org"
}

getCol

getCol() fetches the first column of all rows.

array(2) {
  [0]=> string(5) "pinky"
  [1]=> string(8) "thebrain"
}

getAssoc

getAssoc() fetches all rows into an array, using the first colum of the rows as array key. This column is removed from the array itself.

array(2) {
  ["pinky"] => array(2) {
    [0] => string(1) "1"
    [1] => string(20) "pinky@domination.org"
  }
  ["thebrain"] => array(2) {
    [0] => string(1) "2"
    [1] => string(20) "brain@dominators.net"
  }
}

getAll

getAll() fetches all columns from all rows into an array.

array(2) {
  [0] => array(3) {
    [0] => string(5) "pinky"
    [1] => string(1) "1"
    [2] => string(20) "pinky@domination.org"
  }
  [1] => array(3) {
    [0] => string(8) "thebrain"
    [1] => string(1) "2"
    [2] => string(20) "brain@dominators.net"
  }
}

DB

DB – Main class

Description

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

DB::connect()

DB::connect() – Connects to a database

Synopsis

object connect ( mixed $dsn , array $options = array() )

Description

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

Parameter

string or array $dsn

Data Source Name. String formats are described in the DSN section while array formats are covered in the "Intro - Connect" section.

array $options

An optional argument can contain runtime configuration settings for this package. See setOption() for more information on the available settings.

Return value

object - a new DB object or a DB_Error object on failure

Throws

Note

This function should be called statically.

See

disconnect(), "Intro - Connect"

DB::isError()

DB::isError() – Determines if a variable is a DB_Error object

Synopsis

boolean isError ( mixed $value )

Description

Checks whether a result code from a DB method is a DB_Error object or not.

You're generally better off using PEAR::isError() instead of the DB specific isError().

Parameter

mixed $value

Variable to check

Return value

boolean - TRUE if $value is a DB_Error object

Note

This function should be called statically.

Example

Using isError()

<?php
require_once 'DB.php';

$db =& DB::connect('pgsql://usr:pw@localhost/dbnam');
if (PEAR::isError($db)) {
    die($db->getMessage());
}
?>

DB_common

DB_common – Interface for database access

Description

DB_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 connect() method.

DB_common::affectedRows()

DB_common::affectedRows() – Finds number of rows affected by a data changing query

Synopsis

integer affectedRows ( )

Description

Number of rows affected by a data manipulation query (for example INSERT, UPDATE or DELETE). Returns 0 for SELECT queries.

Return value

integer - number of rows or a DB_Error object on failure

Throws

Note

This function can not be called statically.

Example

Using affectedRows()

<?php
// Once you have a valid DB object named $db...
$res =& $db->query('DELETE * FROM clients');

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

echo 'I have deleted ' . $db->affectedRows() . ' clients';
?>

See

numRows(), numCols(), query(), execute(), autoExecute(), executeMultiple()

DB_common::autoCommit()

DB_common::autoCommit() – Turns auto-commit on or off

Synopsis

mixed autoCommit ( boolean $onoff = false )

Description

Turns auto-commit on or off.

Parameter

boolean $onoff

TRUE to turn auto-commit on. FALSE to turn auto-commit off.

Return value

integer - DB_OK on success or a DB_Error object on failure

Throws

Note

This function can not be called statically.

When using MySQL as your DBMS, transactions can only be used when the tables in question use the InnoDB format.

Example

Using autocommit()

<?php
$db =& DB::connect('ibase(firebird)://user:pw@localhost/path/file');

$db->autoCommit(false);

$db->query('CREATE TABLE blah (a integer)');
$db->query('CREATE TABLE blue (b integer)');
$db->commit();

$db->query('INSERT INTO blah (a) VALUES (11)');
$db->query('INSERT INTO blah (a) VALUES (12)');

$res1 =& $db->query('SELECT a FROM blah');
if (DB::isError($res1)) {
    echo $res1->getMessage() . "\n";
}
$i = 1;
while ($res1->fetchInto($row, DB_FETCHMODE_ORDERED)) {
    echo "fetch data $row[0]\n";
    echo "insert number $i...\n";
    $res2 =& $db->query("INSERT INTO blue (b) VALUES ($i)");
    if (DB::isError($res2)) {
        echo $res2->getMessage() . "\n";
    }
    $i++;
}
$res1->free();

$db->query('DROP TABLE blah');
$db->query('DROP TABLE blue');
$db->commit();
?>

See

commit(), rollback()

DB_common::autoExecute()

DB_common::autoExecute() – Prepares and runs an INSERT or UPDATE query based on variables you supply

Synopsis

integer autoExecute ( string $table , array $fields_values , integer $mode = DB_AUTOQUERY_INSERT , string $where = false )

Description

Automatically prepares and executes INSERT or UPDATE queries.

This method builds a SQL statement using autoPrepare() and then executes the statement using execute() with it.

Parameter

string $table

name of the table

array $fields_values

assoc (key => value), keys are fields names, values are values of these fields

Values are automatically escaped and quoted according to the current DBMS's requirements.

integer $mode

type of query to make (DB_AUTOQUERY_INSERT or DB_AUTOQUERY_UPDATE)

string $where

a string to be used in the WHERE clause. This is only used when $mode is DB_AUTOQUERY_UPDATE. The string is put directly into the query, so you must escape and quote literals according to the DBMS's standards.

Return value

integer - DB_OK on success or a DB_Error object on failure

Throws

Note

This function can not be called statically.

The values passed in $data must be literals. Do not submit SQL functions (for example CURDATE()). SQL functions that should be performed at execution time need to be put in the prepared statement.

Example

Using autoExecute() in insert mode

<?php
// Once you have a valid DB object named $db...
$table_name = 'user';

$fields_values = array(
    'id'      => 1,
    'name'    => 'Fabien',
    'country' => 'France'
);

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

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

Using autoExecute() in update mode

<?php
// Once you have a valid DB object named $db...
$table_name = 'user';

$fields_values = array(
    'country' => 'France',
);

$res = $db->autoExecute($table_name, $fields_values,
                        DB_AUTOQUERY_UPDATE, "country = 'Japan'");

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

See

"Intro - Prepare & Execute", "Intro - autoPrepare & autoExecute", prepare(), execute(), executeMultiple(), autoPrepare()

DB_common::autoPrepare()

DB_common::autoPrepare() – Prepares an INSERT or UPDATE statement based on variables you supply

Synopsis

resource autoPrepare ( string $table , array $table_fields , integer $mode = DB_AUTOQUERY_INSERT , string $where = false )

Description

Automatically builds an INSERT or UPDATE SQL statement so it can later be used by execute() or executeMultiple().

Parameter

string $table

name of the table

array $table_fields

ordered array containing the fields names

Be aware that these fields are assigned ? placeholders, therefore the data you pass to them in the execute() will be automatically escaped and quoted according to the current DBMS's requirements.

integer $mode

type of query to make (DB_AUTOQUERY_INSERT or DB_AUTOQUERY_UPDATE)

string $where

a string to be used in the WHERE clause. This is only used when $mode is DB_AUTOQUERY_UPDATE. The string is put directly into the query, so you must escape and quote literals according to the DBMS's standards.

Return value

resource - resource handle for the query or a DB_Error object on failure

Throws

Note

This function can not be called statically.

Example

Using autoPrepare() in insert mode

<?php

// Once you have a valid DB object named $db...

$table_name   = 'user';

$table_fields = array('name', 'country');

$table_values = array('Zoe', 'France');



$sth = $db->autoPrepare($table_name, $table_fields,

                        DB_AUTOQUERY_INSERT);



if (PEAR::isError($sth)) {

    die($sth->getMessage());

}



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



if (PEAR::isError($res)) {

    die($res->getMessage());

}

?>

Using autoPrepare() in update mode

<?php

// Once you have a valid DB object named $db...

$table_name   = 'user';

$table_fields = array('name', 'country');

$table_values = array('Bob', 'USA');



$sth = $db->autoPrepare($table_name, $table_fields,

                        DB_AUTOQUERY_UPDATE, "country = 'Japan'");



if (PEAR::isError($sth)) {

    die($sth->getMessage());

}



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



if (PEAR::isError($res)) {

    die($res->getMessage());

}

?>

See

"Intro - Prepare & Execute", "Intro - autoPrepare & autoExecute", prepare(), execute(), executeMultiple(), autoExecute()

DB_common::commit()

DB_common::commit() – Commits the current transaction

Synopsis

mixed commit ( )

Description

Commits the current transaction.

Return value

integer - DB_OK on success or a DB_Error object on failure

Throws

Note

This function can not be called statically.

When using MySQL as your DBMS, transactions can only be used when the tables in question use the InnoDB format.

Example

Using commit()

<?php
// Once you have a valid DB object named $db...

$db->autoCommit(false);

$db->query('CREATE TABLE blah (a integer)');
$db->commit();

$db->query('INSERT INTO blah (a) VALUES (11)');

$res =& $db->query('SELECT a FROM blah');
if (DB::isError($res)) {
    echo $res->getMessage() . "\n";
}
while ($res->fetchInto($row, DB_FETCHMODE_ORDERED)) {
    echo $row[0] . "\n";
}
$res->free();

$db->query('DROP TABLE blah');
$db->commit();
?>

See

rollback(), autoCommit()

DB_common::createSequence()

DB_common::createSequence() – Creates a new sequence

Synopsis

integer createSequence ( string $seq_name )

Description

See "Intro - Sequences"

Parameter

string $seq_name

name of the new sequence to create

To avoid problems with various database systems, sequence names should start with a letter and only contain letters, numbers and the underscore character.

Return value

integer - DB_OK or a DB_Error object on failure

Throws

Note

This function can not be called statically.

When using PEAR DB's sequence methods, we strongly advise using these methods for all procedures, including the creation of the sequences. Do not use PEAR DB's methods to access sequences that were created directly in the DBMS. See the warning on the "Intro - Sequences" page complete information.

Example

Using createSequence()

<?php
// Once you have a valid DB object named $db...
$tmp = $db->createSequence('mySequence');

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

See

"Intro - Sequences", nextId(), dropSequence()

DB_common::disconnect()

DB_common::disconnect() – Disconnects from a database

Synopsis

boolean disconnect ( )

Description

Disconnects from a database

Return value

boolean - Returns TRUE on success, FALSE on failure.

Note

This function can not be called statically.

Example

Using disconnect()

<?php
require_once 'DB.php';

$db =& DB::connect('pgsql://someuser:apasswd@localhost/thedb');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

$db->disconnect();
?>

See

connect(), "Intro - Connect"

DB_common::dropSequence()

DB_common::dropSequence() – Deletes a sequence

Synopsis

integer dropSequence ( string $seqName )

Description

See "Intro - Sequences"

Parameter

string $seqName

name of the sequence to delete

Return value

integer - DB_OK or a DB_Error object on failure

Throws

Note

This function can not be called statically.

When using PEAR DB's sequence methods, we strongly advise using these methods for all procedures, including the creation of the sequences. Do not use PEAR DB's methods to access sequences that were created directly in the DBMS. See the warning on the "Intro - Sequences" page complete information.

Example

Using dropSequence()

<?php
// Once you have a valid DB object named $db...
$tmp = $db->dropSequence('mySequence');

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

See

"Intro - Sequences", createSequence(), nextId()

DB_common::escapeSimple()

DB_common::escapeSimple() – Escapes a string according to the current DBMS's standards

Synopsis

string escapeSimple ( string $str )

Description

Escape a string according to the current DBMS's standards.

Parameter

string $str

the input to be escaped

Return value

string - the escaped string

Note

This function can not be called statically.

Function available since: Release 1.6.0

Example

Using escapeSimple()

<?php
// Once you have a valid DB object named $db...
$name = "all's well";
$sql  = "SELECT * FROM clients WHERE name = '"
        . $db->escapeSimple($name) . "'";

$res =& $db->query($sql);
?>

See

quoteIdentifier(), quoteSmart()

DB_common::execute()

DB_common::execute() – Executes a prepared SQL statement

Synopsis

mixed &execute ( resource $stmt , mixed $data = array() )

Description

Merges the SQL statement you submitted to prepare() with the information in $data and then sends the query to the database.

Parameter

resource $stmt

query handle from prepare()

mixed $data

array, string or numeric data to be added to the prepared statement. Quantity of items passed must match quantity of placeholders in the prepared statement: meaning 1 placeholder for non-array parameters or 1 placeholder per array element.

Return value

mixed - a new DB_result object for queries that return results (such as SELECT queries), DB_OK for queries that manipulate data (such as INSERT queries) or a DB_Error object on failure

Throws

Note

This function can not be called statically.

The values passed in $data must be literals. Do not submit SQL functions (for example CURDATE()). SQL functions that should be performed at execution time need to be put in the prepared statement.

Example

Passing a scalar to execute()

<?php
// Once you have a valid DB object named $db...
$sth = $db->prepare('INSERT INTO numbers (number) VALUES (?)');
if (PEAR::isError($sth)) {
    die($sth->getMessage());
}

$res =& $db->execute($sth, 1);
if (PEAR::isError($res)) {
    die($res->getMessage());
}
?>

Passing an array to execute()

<?php
// Once you have a valid DB object named $db...
$sth = $db->prepare('INSERT INTO numbers VALUES (?, ?, ?)');

$data = array(1, 'one', 'en');
$db->execute($sth, $data);
?>

See

"Intro - Prepare & Execute", "Intro - autoPrepare & autoExecute", prepare(), executeMultiple()

DB_common::executeMultiple()

DB_common::executeMultiple() – Runs a prepared SQL statement for each element of an array

Synopsis

integer executeMultiple ( resource $stmt , array $data )

Description

Automatically passes the information in $data (a multi-dimensional array) to execute(), which then runs the SQL statement you submitted to prepare().

Parameter

resource $stmt

query handle from prepare()

array $data

a numeric array containing the data to insert into the query

Return value

integer - DB_OK on success or a DB_Error object on failure

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.

The values passed in $data must be literals. Do not submit SQL functions (for example CURDATE()). SQL functions that should be performed at execution time need to be put in the prepared statement.

Example

Using executeMultiple()

<?php
// Once you have a valid DB object named $db...
$sth = $db->prepare('INSERT INTO numbers VALUES (?, ?, ?)');

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

$alldata = array(array(1, 'one', 'en'),
                 array(2, 'two', 'to'),
                 array(3, 'three', 'tre'),
                 array(4, 'four', 'fire'));

$res =& $db->executeMultiple($sth, $alldata);

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

See

"Intro - Prepare & Execute", "Intro - autoPrepare & autoExecute", prepare(), execute(), autoPrepare(), autoExecute()

DB_common::freePrepared()

DB_common::freePrepared() – Releases resources associated with a prepared SQL statement

Synopsis

boolean freePrepared ( resource $stmt , boolean $free_resource = true )

Description

Removes the memory occupied by the internal notations that keep track of prepared SQL statements. Does not delete the DB_result object itself.

Parameter

resource $stmt

statement resource identifier returned from prepare()

boolean $free_resource

should the PHP resource be freed too? Use false if you need to get data from the result set later.

Parameter available since Release 1.7.0.

Return value

boolean - Returns TRUE on success, FALSE on failure.

Note

This function can not be called statically.

Example

Using freePrepared()

<?php
// Once you have a valid DB object named $db...
$sth = $db->prepare('INSERT INTO numbers (number) VALUES (?)');
$db->execute($sth, 1);
$db->freePrepared($sth);
?>

See

prepare(), autoPrepare(), free()

DB_common::getAll()

DB_common::getAll() – Runs a query and returns all the data as an array

Synopsis

array &getAll ( string $query , array $params = array() , integer $fetchmode = DB_FETCHMODE_DEFAULT )

Description

Runs the query provided and puts the entire result set into a nested array then frees the result set.

Parameter

string $query

the SQL query or the statement to prepare

array $params

array to be used in execution of the statement. Quantity of array elements must match quantity of placeholders in query.

If supplied, prepare()/ execute() is used.

This method does not allow scalars to be used for this argument.

integer $fetchmode

the fetch mode to use. The default is DB_FETCHMODE_DEFAULT, which tells this method to use DB's current fetch mode. The current fetch mode can be changed using setFetchMode(). Potential values include:

• DB_FETCHMODE_ORDERED
• DB_FETCHMODE_ASSOC
• DB_FETCHMODE_OBJECT
• DB_FETCHMODE_ORDERED | DB_FETCHMODE_FLIPPED
• DB_FETCHMODE_ASSOC | DB_FETCHMODE_FLIPPED

Return value

array - a nested array or a DB_Error on failure

Throws

Note

This function can not be called statically.

Example

Using getAll() to return an associative array by setting the default fetch mode first

<?php

// Once you have a valid DB object named $db...

$db->setFetchMode(DB_FETCHMODE_ASSOC);

$data =& $db->getAll('SELECT cf, nf, df FROM foo');



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

    

Array

(

    [0] => Array

        (

            [cf] => Juan

            [nf] => 5

            [df] => 1991-01-11 21:31:41

        )

    [1] => Array

        (

            [cf] => Kyu

            [nf] => 10

            [df] => 1992-02-12 22:32:42

        )

)

    
   

Using getAll() to return an ordered array

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAll('SELECT cf, nf, df FROM foo',

        array(), DB_FETCHMODE_ORDERED);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

    

Array

(

    [0] => Array

        (

            [0] => Juan

            [1] => 5

            [2] => 1991-01-11 21:31:41

        )

    [1] => Array

        (

            [0] => Kyu

            [1] => 10

            [2] => 1992-02-12 22:32:42

        )

)

    
   

Using getAll() to return a flipped ordered array

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAll('SELECT cf, nf, df FROM foo',

        array(), DB_FETCHMODE_ORDERED | DB_FETCHMODE_FLIPPED);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

    

Array

(

    [0] => Array

        (

            [0] => Juan

            [1] => Kyu

        )

    [1] => Array

        (

            [0] => 5

            [1] => 10

        )

    [2] => Array

        (

            [0] => 1991-01-11 21:31:41

            [1] => 1992-02-12 22:32:42

        )

)

    
   

Using getAll() to return an associative array

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAll('SELECT cf, nf, df FROM foo',

        array(), DB_FETCHMODE_ASSOC);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

    

Array

(

    [0] => Array

        (

            [cf] => Juan

            [nf] => 5

            [df] => 1991-01-11 21:31:41

        )

    [1] => Array

        (

            [cf] => Kyu

            [nf] => 10

            [df] => 1992-02-12 22:32:42

        )

)

    
   

Using getAll() to return a flipped associative array

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAll('SELECT cf, nf, df FROM foo',

        array(), DB_FETCHMODE_ASSOC | DB_FETCHMODE_FLIPPED);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

    

Array

(

    [cf] => Array

        (

            [0] => Juan

            [1] => Kyu

        )

    [nf] => Array

        (

            [0] => 5

            [1] => 10

        )

    [df] => Array

        (

            [0] => 1991-01-11 21:31:41

            [1] => 1992-02-12 22:32:42

        )

)

    
   

Using getAll() to return an array of objects

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAll('SELECT cf, nf, df FROM foo',

        array(), DB_FETCHMODE_OBJECT);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

    

Array

(

    [0] => stdClass Object

        (

            [cf] => Juan

            [nf] => 5

            [df] => 1991-01-11 21:31:41

        )

    [1] => stdClass Object

        (

            [cf] => Kyu

            [nf] => 10

            [df] => 1992-02-12 22:32:42

        )

)

    
   

Using getAll() in prepare/execute mode to return an associative array

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAll('SELECT cf, nf, df FROM foo WHERE nf = ?',

        array(5), DB_FETCHMODE_ASSOC);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

    

Array

(

    [0] => Array

        (

            [cf] => Juan

            [nf] => 5

            [df] => 1991-01-11 21:31:41

        )

)

    
   

See

setFetchMode(), getOne(), getRow(), getCol(), getAssoc(), query(), "Intro - Prepare & Execute"

DB_common::getAssoc()

DB_common::getAssoc() – Runs a query and returns the data as an array

Synopsis

array &getAssoc ( string $query , boolean $force_array = false , mixed $params = array() , integer $fetchmode = DB_FETCHMODE_DEFAULT , boolean $group = false )

Description

Runs the query provided and puts the entire result set into an associative array then frees the result set.

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 or the statement to prepare

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.

mixed $params

array, string or numeric data to be added to the prepared statement. Quantity of items passed must match quantity of placeholders in the prepared statement: meaning 1 placeholder for non-array parameters or 1 placeholder per array element.

If supplied, prepare()/ execute() is used.

integer $fetchmode

the fetch mode to use. The default is DB_FETCHMODE_DEFAULT, which tells this method to use DB's current fetch mode. DB's current default fetch mode can be changed using setFetchMode(). Potential values include:

• DB_FETCHMODE_ORDERED
• DB_FETCHMODE_ASSOC
• DB_FETCHMODE_OBJECT

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 the query results or a DB_Error object on failure

Throws

Note

This function can not be called statically.

Example

All of the examples use the following data set:

INSERT INTO foo VALUES ('Juan', 5, '1991-01-11 21:31:41');

INSERT INTO foo VALUES ('Kyu', 10, '1992-02-12 22:32:42');

INSERT INTO foo VALUES ('Kyu', 15, '1993-03-13 23:33:43');

Result sets having two columns

When using getAssoc() for results which have two columns and $force_array = FALSE (the default) changing $fetchmode has no impact on the format of the resulting array.

Using getAssoc() in default mode

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAssoc('SELECT cf, df FROM foo');



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

     

Array

(

    [Juan] => 1991-01-11 21:31:41

    [Kyu] => 1993-03-13 23:33:43

)

     
    

Using getAssoc() with $group = TRUE

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAssoc('SELECT cf, df FROM foo',

        false, array(), DB_FETCHMODE_ORDERED, true);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

     

Array

(

    [Juan] => Array

        (

            [0] => 1991-01-11 21:31:41

        )

    [Kyu] => Array

        (

            [0] => 1992-02-12 22:32:42

            [1] => 1993-03-13 23:33:43

        )

)

     
    

Using getAssoc() with $force_array = TRUE and $fetchmode = DB_FETCHMODE_ORDERED

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAssoc('SELECT cf, df FROM foo',

        true, array(), DB_FETCHMODE_ORDERED);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

     

Array

(

    [Juan] => Array

        (

            [0] => 1991-01-11 21:31:41

        )

    [Kyu] => Array

        (

            [0] => 1993-03-13 23:33:43

        )

)

     
    

Using getAssoc() with $force_array = TRUE and $fetchmode = DB_FETCHMODE_ASSOC

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAssoc('SELECT cf, df FROM foo',

        true, array(), DB_FETCHMODE_ASSOC);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

     

Array

(

    [Juan] => Array

        (

            [df] => 1991-01-11 21:31:41

        )

    [Kyu] => Array

        (

            [df] => 1993-03-13 23:33:43

        )

)

     
    

Using getAssoc() with $force_array = TRUE and $fetchmode = DB_FETCHMODE_OBJECT

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAssoc('SELECT cf, df FROM foo',

        true, array(), DB_FETCHMODE_OBJECT);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

     

Array

(

    [Juan] => stdClass Object

        (

            [cf] => Juan

            [df] => 1991-01-11 21:31:41

        )

    [Kyu] => stdClass Object

        (

            [cf] => Kyu

            [df] => 1993-03-13 23:33:43

        )

)

     
    

Using getAssoc() with $force_array = TRUE, $fetchmode = DB_FETCHMODE_ORDERED and $group = TRUE

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAssoc('SELECT cf, df FROM foo',

        true, array(), DB_FETCHMODE_ORDERED, true);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

     

Array

(

    [Juan] => Array

        (

            [0] => Array

                (

                    [0] => 1991-01-11 21:31:41

                )

        )

    [Kyu] => Array

        (

            [0] => Array

                (

                    [0] => 1992-02-12 22:32:42

                )

            [1] => Array

                (

                    [0] => 1993-03-13 23:33:43

                )

        )

)

     
    

Using getAssoc() with $force_array = TRUE, $fetchmode = DB_FETCHMODE_ASSOC and $group = TRUE

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAssoc('SELECT cf, df FROM foo',

        true, array(), DB_FETCHMODE_ASSOC, true);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

     

Array

(

    [Juan] => Array

        (

            [0] => Array

                (

                    [df] => 1991-01-11 21:31:41

                )

        )

    [Kyu] => Array

        (

            [0] => Array

                (

                    [df] => 1992-02-12 22:32:42

                )

            [1] => Array

                (

                    [df] => 1993-03-13 23:33:43

                )

        )

)

     
    

Using getAssoc() with $force_array = TRUE, $fetchmode = DB_FETCHMODE_OBJECT and $group = TRUE

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAssoc('SELECT cf, df FROM foo',

        true, array(), DB_FETCHMODE_OBJECT, true);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

     

Array

(

    [Juan] => Array

        (

            [0] => stdClass Object

                (

                    [cf] => Juan

                    [df] => 1991-01-11 21:31:41

                )

        )

    [Kyu] => Array

        (

            [0] => stdClass Object

                (

                    [cf] => Kyu

                    [df] => 1992-02-12 22:32:42

                )

            [1] => stdClass Object

                (

                    [cf] => Kyu

                    [df] => 1993-03-13 23:33:43

                )

        )

)

     
    

Result sets having more than two columns

Using getAssoc() with $fetchmode = DB_FETCHMODE_ORDERED

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAssoc('SELECT cf, nf, df FROM foo',

        false, array(), DB_FETCHMODE_ORDERED);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

     

Array

(

    [Juan] => Array

        (

            [0] => 5

            [1] => 1991-01-11 21:31:41

        )

    [Kyu] => Array

        (

            [0] => 15

            [1] => 1993-03-13 23:33:43

        )

)

     
    

Using getAssoc() with $fetchmode = DB_FETCHMODE_ASSOC

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAssoc('SELECT cf, nf, df FROM foo',

        false, array(), DB_FETCHMODE_ASSOC);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

     

Array

(

    [Juan] => Array

        (

            [nf] => 5

            [df] => 1991-01-11 21:31:41

        )

    [Kyu] => Array

        (

            [nf] => 15

            [df] => 1993-03-13 23:33:43

        )

)

     
    

Using getAssoc() with $fetchmode = DB_FETCHMODE_OBJECT

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAssoc('SELECT cf, nf, df FROM foo',

        false, array(), DB_FETCHMODE_OBJECT);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

     

Array

(

    [Juan] => stdClass Object

        (

            [cf] => Juan

            [nf] => 5

            [df] => 1991-01-11 21:31:41

        )

    [Kyu] => stdClass Object

        (

            [cf] => Kyu

            [nf] => 15

            [df] => 1993-03-13 23:33:43

        )

)

     
    

Using getAssoc() with $fetchmode = DB_FETCHMODE_ORDERED and $group = TRUE

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAssoc('SELECT cf, nf, df FROM foo',

        false, array(), DB_FETCHMODE_ORDERED, true);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

     

Array

(

    [Juan] => Array

        (

            [0] => Array

                (

                    [0] => 5

                    [1] => 1991-01-11 21:31:41

                )

        )

    [Kyu] => Array

        (

            [0] => Array

                (

                    [0] => 10

                    [1] => 1992-02-12 22:32:42

                )

            [1] => Array

                (

                    [0] => 15

                    [1] => 1993-03-13 23:33:43

                )

        )

)

     
    

Using getAssoc() with $fetchmode = DB_FETCHMODE_ASSOC and $group = TRUE

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAssoc('SELECT cf, nf, df FROM foo',

        false, array(), DB_FETCHMODE_ASSOC, true);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

     

Array

(

    [Juan] => Array

        (

            [0] => Array

                (

                    [nf] => 5

                    [df] => 1991-01-11 21:31:41

                )

        )

    [Kyu] => Array

        (

            [0] => Array

                (

                    [nf] => 10

                    [df] => 1992-02-12 22:32:42

                )

            [1] => Array

                (

                    [nf] => 15

                    [df] => 1993-03-13 23:33:43

                )

        )

)

     
    

Using getAssoc() with $fetchmode = DB_FETCHMODE_OBJECT and $group = TRUE

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAssoc('SELECT cf, nf, df FROM foo',

        false, array(), DB_FETCHMODE_OBJECT, true);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

     

Array

(

    [Juan] => Array

        (

            [0] => stdClass Object

                (

                    [cf] => Juan

                    [nf] => 5

                    [df] => 1991-01-11 21:31:41

                )

        )

    [Kyu] => Array

        (

            [0] => stdClass Object

                (

                    [cf] => Kyu

                    [nf] => 10

                    [df] => 1992-02-12 22:32:42

                )

            [1] => stdClass Object

                (

                    [cf] => Kyu

                    [nf] => 15

                    [df] => 1993-03-13 23:33:43

                )

        )

)

     
    

Prepare / Execute Mode

Using getAssoc() with one placeholder

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAssoc('SELECT cf, df FROM foo WHERE nf = ?',

        false, 5);



if (PEAR::isError($data)) {

    die($data->getMessage());

}

?>

Using getAssoc() with two placeholders

<?php

// Once you have a valid DB object named $db...

$data =& $db->getAssoc('SELECT cf, df FROM foo WHERE nf IN (?, ?)',

        false, array(5, 10));



if (PEAR::isError($data)) {

    die($data->getMessage());

}

?>

See

setFetchMode(), getOne(), getRow(), getCol(), getAll(), query(), "Intro - Prepare & Execute"

DB_common::getCol()

DB_common::getCol() – Runs a query and returns the data from a single column

Synopsis

array &getCol ( string $query , mixed $col = 0 , mixed $params = array() )

Description

Runs the query provided and puts the first column of data into an array then frees the result set.

Parameter

string $query

the SQL query or the statement to prepare

mixed $col

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

mixed $params

array, string or numeric data to be added to the prepared statement. Quantity of items passed must match quantity of placeholders in the prepared statement: meaning 1 placeholder for non-array parameters or 1 placeholder per array element.

If supplied, prepare()/ execute() is used.

Return value

array - an ordered array containing data from a result set column or a DB_Error object on failure. The array's index starts at 0.

Throws

Note

This function can not be called statically.

Example

Using getCol()

<?php
// Once you have a valid DB object named $db...
$data =& $db->getCol('SELECT cf, df FROM foo');

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

print_r($data);
?>

Output:

    
Array
(
    [0] => Juan
    [1] => Kyu
)
    
   

Using getCol() to retrieve a numerically specified column

<?php
// Once you have a valid DB object named $db...
$data =& $db->getCol('SELECT cf, df FROM foo', 1);

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

print_r($data);
?>

Output:

    
Array
(
    [0] => 1991-01-11 21:31:41
    [1] => 1992-02-12 22:32:42
)
    
   

Using getCol() to retrieve a named column

<?php
// Once you have a valid DB object named $db...
$data =& $db->getCol('SELECT cf, df FROM foo', 'df');

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

print_r($data);
?>

Output:

    
Array
(
    [0] => 1991-01-11 21:31:41
    [1] => 1992-02-12 22:32:42
)
    
   

Using getCol() in prepare/execute mode with one placeholder

<?php
// Once you have a valid DB object named $db...
$data =& $db->getCol('SELECT cf, df FROM foo WHERE nf = ?',
        'df', 5);

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

print_r($data);
?>

Output:

    
Array
(
    [0] => 1991-01-11 21:31:41
)
    
   

Using getCol() in prepare/execute mode with two placeholders

<?php
// Once you have a valid DB object named $db...
$data =& $db->getCol('SELECT cf, df FROM foo WHERE nf IN (?, ?)',
        'df', array(5, 10));

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

print_r($data);
?>

Output:

    
Array
(
    [0] => 1991-01-11 21:31:41
    [1] => 1992-02-12 22:32:42
)
    
   

See

getOne(), getRow(), getAssoc(), getAll(), query(), "Intro - Prepare & Execute"

DB_common::getListOf()

DB_common::getListOf() – Views database system information

Synopsis

array getListOf ( string $type )

Description

Retrieves information about database users, avaible databases, views and functions.

Parameter

string $type

type of requested info, valid values for $type are database dependent, ie: tables, databases, users, view, functions

Return value

array - an ordered array containing the requested data or a DB_Error object on failure

Throws

Note

This function can not be called statically.

Example

Using getListOf()

<?php
// Once you have a valid DB object named $db...
$data = $db->getListOf('tables');

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

echo implode("\n", $data);
?>

DB_common::getOne()

DB_common::getOne() – Runs a query and returns the first column of the first row

Synopsis

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

Description

Runs the query provided and returns the data from the first column of the first row then frees the result set.

Parameter

string $query

the SQL query or the statement to prepare

mixed $params

array, string or numeric data to be added to the prepared statement. Quantity of items passed must match quantity of placeholders in the prepared statement: meaning 1 placeholder for non-array parameters or 1 placeholder per array element.

If supplied, prepare()/ execute() is used.

Return value

mixed - the first column's data, NULL if there is no data, or a DB_Error object on failure

Throws

Note

This function can not be called statically.

Example

Using getOne()

<?php
// Once you have a valid DB object named $db...
$data =& $db->getOne('SELECT cf FROM foo');

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

echo "$data\n";
?>

Using getOne() with one placeholder

<?php
// Once you have a valid DB object named $db...
$data =& $db->getOne('SELECT cf FROM foo WHERE nf = ?',
        5);

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

echo "$data\n";
?>

Using getOne() with two placeholders

<?php
// Once you have a valid DB object named $db...
$data =& $db->getOne('SELECT cf FROM foo WHERE nf IN (?, ?)',
        array(5, 10));

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

echo "$data\n";
?>

See

getRow(), getCol(), getAssoc(), getAll(), query(), "Intro - Prepare & Execute"

DB_common::getOption()

DB_common::getOption() – Determines current state of a PEAR DB configuration option

Synopsis

mixed getOption ( string $option )

Description

Determines current state of a PEAR DB configuration option

Parameter

string $option

name of the option to examine

Return value

mixed the option's value

Throws

Note

This function can not be called statically.

Example

Simple getOption() example

<?php
// Once you have a valid DB object named $db...
if ($db->getOption('autofree')) {
    // do something...
}
?>

DB_common::getRow()

DB_common::getRow() – Runs a query and returns the first row

Synopsis

array &getRow ( string $query , array $params = array() , integer $fetchmode = DB_FETCHMODE_DEFAULT )

Description

Runs the query provided and puts the first row of data into an array then frees the result set.

Parameter

string $query

the SQL query or the statement to prepare

array $params

array to be used in execution of the statement. Quantity of array elements must match quantity of placeholders in query.

If supplied, prepare()/ execute() is used.

This method does not allow scalars to be used for this argument.

integer $fetchmode

the fetch mode to use. The default is DB_FETCHMODE_DEFAULT, which tells this method to use DB's current fetch mode. DB's current default fetch mode can be changed using setFetchMode(). Potential values include:

• DB_FETCHMODE_ORDERED
• DB_FETCHMODE_ASSOC
• DB_FETCHMODE_OBJECT

Return value

array - the first row's data in an array or a DB_Error object on failure. The array may be ordered or associative depending on $fetchmode. The column index starts at 0 for ordered arrays.

Throws

Note

This function can not be called statically.

Example

Using getRow() with $fetchmode = DB_FETCHMODE_ORDERED

<?php

// Once you have a valid DB object named $db...

$data =& $db->getRow('SELECT cf, df FROM foo',

        array(), DB_FETCHMODE_ORDERED);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

    

Array

(

    [0] => Juan

    [1] => 1991-01-11 21:31:41

)

    
   

Using getRow() with $fetchmode = DB_FETCHMODE_ASSOC

<?php

// Once you have a valid DB object named $db...

$data =& $db->getRow('SELECT cf, df FROM foo',

        array(), DB_FETCHMODE_ASSOC);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

    

Array

(

    [cf] => Juan

    [df] => 1991-01-11 21:31:41

)

    
   

Using getRow() with $fetchmode = DB_FETCHMODE_OBJECT

<?php

// Once you have a valid DB object named $db...

$data =& $db->getRow('SELECT cf, df FROM foo',

        array(), DB_FETCHMODE_OBJECT);



if (PEAR::isError($data)) {

    die($data->getMessage());

}



print_r($data);

?>

Output:

    

stdClass Object

(

    [cf] => Juan

    [df] => 1991-01-11 21:31:41

)

    
   

Using getRow() with one placeholder

<?php

// Once you have a valid DB object named $db...

$data =& $db->getRow('SELECT cf, df FROM foo WHERE nf = ?',

        array(5));



if (PEAR::isError($data)) {

    die($data->getMessage());

}

?>

Using getRow() with two placeholders

<?php

// Once you have a valid DB object named $db...



$data =& $db->getRow('SELECT cf, df FROM foo WHERE nf IN (?, ?)',

        array(5, 10));



if (PEAR::isError($data)) {

    die($data->getMessage());

}

?>

See

setFetchMode(), getOne(), getCol(), getAssoc(), getAll(), query(), "Intro - Prepare & Execute"

DB_common::limitQuery()

DB_common::limitQuery() – Sends a LIMIT query to the database

Synopsis

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

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

integer $from

the row to start to fetch. Note that 0 returns the first row, 1 returns the second row, etc.

integer $count

the numbers of rows to fetch

mixed $params

array, string or numeric data to be added to the prepared statement. Quantity of items passed must match quantity of placeholders in the prepared statement: meaning 1 placeholder for non-array parameters or 1 placeholder per array element.

Return value

mixed - a new DB_result object for queries that return results (such as SELECT queries), DB_OK for queries that manipulate data (such as INSERT queries) or a DB_Error object on failure

Throws

Note

This function can not be called statically.

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.

Also note that $from and $count are not being escaped. You should take care of sanitizing input yourself, or you are open to an SQL injection attack.

Example

Using limitQuery()

<?php
// Once you have a valid DB object named $db...
$res =& $db->limitQuery('SELECT * FROM foo', 49, 10);

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

See

query()

DB_common::nextId()

DB_common::nextId() – Returns the next number from a sequence

Synopsis

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

Description

Returns the next available number from a sequence. The sequence is automatically incremented each time this method is called.

See "Intro - Sequences" for a more complete discussion.

Parameter

string $seq_name

name of the sequence

To avoid problems with various database systems, sequence names should start with a letter and only contain letters, numbers and the underscore character.

boolean $onDemand

When TRUE, the sequence is automatically created if it does not exist yet.

The on demand creation process necessitates the database user specified in the script having the database permissions needed to create a table or sequence. The exact privileges required depends on the DBMS being used.

Return value

integer - a free id number or a DB_Error object on failure

Throws

Note

This function can not be called statically.

When using PEAR DB's sequence methods, we strongly advise using these methods for all procedures, including the creation of the sequences. Do not use PEAR DB's methods to access sequences that were created directly in the DBMS. See the warning on the "Intro - Sequences" page complete information.

Example

Using nextId()

<?php
// Once you have a valid DB object named $db...
$id = $db->nextId('mySequence');
if (PEAR::isError($id)) {
    die($id->getMessage());
}

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

See

"Intro - Sequences", createSequence(), dropSequence()

DB_common::nextQueryIsManip()

DB_common::nextQueryIsManip() – Informs the DB driver that the next query is a manipulation query

Synopsis

void nextQueryIsManip ( boolean $manip )

Description

Informs the DB driver that the next query that is called will manipulate data within the database and will not return a result set, irrespective of the usual detection performed within DB.

Parameter

boolean $manip

When TRUE, the next query will always be treated as not returning a result set. When FALSE, the normal behaviour will be used, which will result in DB attempting to automatically detect whether the query will return a result set or not.

Return value

void - nothing is returned

Note

This function can not be called statically.

DB_common::prepare()

DB_common::prepare() – Prepares a SQL statement for later execution

Synopsis

resource prepare ( string $query )

Description

Gets a SQL statement ready so it can be run by execute().

Parameter

string $query

the query to prepare

Return value

resource - the query handle or a DB_Error object on failure

Note

This function can not be called statically.

Example

Using prepare()

<?php
// Once you have a valid DB object named $db...
$sth = $db->prepare('INSERT INTO numbers (number) VALUES (?)');
if (PEAR::isError($sth)) {
    die($sth->getMessage());
}

$res =& $db->execute($sth, 1);
if (PEAR::isError($res)) {
    die($res->getMessage());
}
?>

See

"Intro - Prepare & Execute", "Intro - autoPrepare & autoExecute", execute(), executeMultiple()

DB_common::provides()

DB_common::provides() – Checks if the DBMS supports a particular feature

Synopsis

boolean provides ( string $feature )

Description

Checks if a feature is available for the chosen database type.

Parameter

string $feature

the feature to check

Possible values are:

$feature value	Meaning
prepare	The database does a pre-check of the SQL statement
pconnect	The database supports persistent connections
transactions	The database supports transactions
limit	The database supports LIMITed SELECT statements

Return value

boolean - TRUE if the feature is supported

Note

This function can not be called statically.

The provided information are only hints. Check the documentation of your database system for the real supported features. I.e. MySQL supports transactions, but not for every table type.

Example

Using provides()

<?php
// Once you have a valid DB object named $db...
if ($db->provides('pconnect')) {
    echo "Persistent connections are allowed.\n";
}
?>

DB_common::query()

DB_common::query() – Sends a query to the database

Synopsis

mixed &query ( string $query , mixed $params = array() )

Description

Runs a query

Can be used instead of prepare() and execute(), if you set the $params parameter and your query uses placeholders. See "Intro - Prepare & Execute" for more information on this mode.

Parameter

string $query

the SQL query or the statement to prepare

mixed $params

array, string or numeric data to be added to the prepared statement. Quantity of items passed must match quantity of placeholders in the prepared statement: meaning 1 placeholder for non-array parameters or 1 placeholder per array element.

Return value

mixed - a new DB_result object for queries that return results (such as SELECT queries), DB_OK for queries that manipulate data (such as INSERT queries) or a DB_Error object on failure

Throws

Note

This function can not be called statically.

See

"Intro - Prepare & Execute", limitQuery()

DB_common::quote()

DB_common::quote() – DEPRECATED: Quotes a string

Synopsis

string quote ( string $string )

Description

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

This method has been deprecated. Use quoteSmart() or escapeSimple() instead.

Parameter

string $string

the input string to quote

Return value

string - the quoted string.

Note

This function can not be called statically.

This function is deprecated. That means that future versions of this package may not support it anymore.

Deprecated in release 1.6.0.

See

quoteSmart(), escapeSimple(), quoteIdentifier()

DB_common::quoteIdentifier()

DB_common::quoteIdentifier() – Formats a string so it can be safely used as an identifier

Synopsis

string quoteIdentifier ( string $str )

Description

Format input so it can be safely used as a delimited identifier in a query. Identifiers are objects such as table or column names.

The format returned depends on the database type being used.

Delimited identifiers are known to generally work correctly under the following drivers:

• mssql
• mysql
• mysqli
• oci8
• odbc(access)
• odbc(db2)
• pgsql
• sqlite
• sybase

InterBase doesn't seem to be able to use delimited identifiers via PHP 4. They work fine under PHP 5.

Parameter

string $str

the input to be quoted

Return value

string - the formatted string

Note

This function can not be called statically.

Function available since: Release 1.6.0

Just because you CAN use delimited identifiers doesn't mean you SHOULD use them. In general, they end up causing way more problems than they solve.

Portability is broken by using the following characters inside delimited identifiers:

• backtick (`) -- due to MySQL
• double quote (") -- due to Oracle
• brackets ([ or ]) -- due to Access

Example

Using quoteIdentifier()

<?php
// Once you have a valid DB object named $db...
$sql = 'SELECT ' . $db->quoteIdentifier('company name')
       . ', address FROM clients';

$res =& $db->query($sql);
?>

See

quoteSmart(), escapeSimple()

DB_common::quoteSmart()

DB_common::quoteSmart() – Formats input so it can be safely used as a literal

Synopsis

mixed quoteSmart ( mixed $in )

Description

Format input so it can be safely used as a literal in a query. Literals are values such as strings or numbers which get utilized in places like WHERE, SET and VALUES clauses of SQL statements.

The format returned depends on the PHP data type of input and the database type being used.

Parameter

mixed $in

the input to be quoted

Return value

mixed - the formatted data

The format of the results depends on the input's PHP type:

• input -> returns
• NULL -> the string NULL
• integer or float -> the unquoted number
• boolean -> output depends on the driver in use Most drivers return integers: 1 if true or 0 if false. Some return strings: TRUE if true or FALSE if false. Finally one returns strings: T if true or F if false. Here is a list of each DBMS, the values returned and the suggested column type:
  • dbase -> T/F (Logical)
  • fbase -> TRUE/FALSE (BOOLEAN)
  • ibase -> 1/0 (SMALLINT) [1]
  • ifx -> 1/0 (SMALLINT) [1]
  • msql -> 1/0 (INTEGER)
  • mssql -> 1/0 (TINYINT)
  • mysql -> 1/0 (TINYINT(1))
  • mysqli -> 1/0 (TINYINT(1))
  • oci8 -> 1/0 (NUMBER(1))
  • odbc -> 1/0 (SMALLINT) [1]
  • pgsql -> TRUE/FALSE (BOOLEAN)
  • sqlite -> 1/0 (INTEGER)
  • sybase -> 1/0 (TINYINT)
  [1] Accommodate the lowest common denominator because not all versions of have BOOLEAN.
• other (including strings and numeric strings) -> a string which has been escaped in a DBMS specific way (using escapeSimple()) and then surrounded by single quotes

Note

This function can not be called statically.

Function available since: Release 1.6.0

Example

Using quoteSmart()

<?php
// Once you have a valid DB object named $db...
$name   = "all's well";
$active = true;
$sql    = 'SELECT * FROM clients WHERE name = '
          . $db->quoteSmart($name)
          . ' AND active = '
          . $db->quoteSmart($active);

$res =& $db->query($sql);
?>

See

quoteIdentifier(), escapeSimple()

DB_common::rollback()

DB_common::rollback() – Rolls back the current transaction

Synopsis

mixed rollback ( )

Description

Rolls back the current transaction.

Return value

integer - DB_OK on success or a DB_Error object on failure

Throws

Note

This function can not be called statically.

When using MySQL as your DBMS, transactions can only be used when the tables in question use the InnoDB format.

Example

Using rollback()

<?php
// Once you have a valid DB object named $db...

$db->autoCommit(false);

$db->query('INSERT INTO blah (a) VALUES (11)');

$res =& $db->query('SELECT b FROM blue');
if (DB::isError($res)) {
    echo $res->getMessage() . "\n";
}
while ($res->fetchInto($row, DB_FETCHMODE_ORDERED)) {
    if ($row[0] == 'problem') {
        $db->rollback();
    }
}
$res->free();

$db->query('DROP TABLE blah');
$db->commit();
?>

See

commit(), autoCommit()

DB_common::setFetchMode()

DB_common::setFetchMode() – Sets the default fetch mode

Synopsis

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

Description

Sets the default fetch mode used by fetch*() and get*() methods.

Parameter

integer $fetchmode

DB_FETCHMODE_ORDERED, DB_FETCHMODE_ASSOC or DB_FETCHMODE_OBJECT.

See the Examples section, below, for more information.

string $object_class

This parameter is for use when $fetchmode is set to DB_FETCHMODE_OBJECT.

You can set this parameter to DB_row, which then causes the resulting data to populate a new instance of a DB_row object.

Return value

void - nothing is returned on success or a DB_Error object on failure

Throws

Note

This function can not be called statically.

Example

DB_FETCHMODE_ORDERED (default)

Causes ordered arrays to be returned. The order is taken from the select statement.

<?php
// Once you have a valid DB object named $db...
$db->setFetchMode(DB_FETCHMODE_ORDERED);

$res =& $db->query('SELECT a, b FROM phptest WHERE a = 28');
$row =& $res->fetchRow();

print_r($row);
echo 'Column a is ' . $row[0];
?>

Output:

    
Array
(
    [0] => 28
    [1] => hi
)
Column a is 28
    
   

DB_FETCHMODE_ASSOC

Makes associative arrays, with the column names as the array keys.

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

$res =& $db->query('SELECT a, b FROM phptest WHERE a = 28');
$row =& $res->fetchRow();

print_r($row);
echo 'Column a is ' . $row['a'];
?>

Output:

    
Array
(
    [a] => 28
    [b] => hi
)
Column a is 28
      
   

DB_FETCHMODE_OBJECT

Returns objects with column names as properties.

<?php
// Once you have a valid DB object named $db...
$db->setFetchMode(DB_FETCHMODE_OBJECT);

$res =& $db->query('SELECT a, b FROM phptest WHERE a = 28');
$row =& $res->fetchRow();

print_r($row);
echo 'Column a is ' . $row->a;
?>

Output:

    
stdClass Object
(
    [a] => 28
    [b] => hi
)
Column a is 28
      
   

DB_FETCHMODE_OBJECT and DB_row

If setFetchMode()'s optional $object_class parameter is set to DB_row, DB_row objects are returned.

<?php
// Once you have a valid DB object named $db...
$db->setFetchMode(DB_FETCHMODE_OBJECT, 'DB_row');

$res =& $db->query('SELECT a, b FROM phptest WHERE a = 28');
$row =& $res->fetchRow();

print_r($row);
echo 'Column a is ' . $row->a;
?>

Output:

    
db_row Object
(
    [a] => 28
    [b] => hi
)
Column a is 28
    
   

DB_FETCHMODE_OBJECT with your own object in PHP 4

<?php
// Once you have a valid DB object named $db...

class SomeResult {
    function SomeResult($data) {
        foreach ($data as $key => $value) {
            $this->$key = $data[$key];
        }
    }
}

$db->setFetchMode(DB_FETCHMODE_OBJECT, 'SomeResult');

$res =& $db->query('SELECT a, b FROM phptest WHERE a = 28');
$row =& $res->fetchRow();

print_r($row);
echo 'Column a is ' . $row->a;
?>

Output:

    
SomeResult Object
(
    [a] => 28
    [b] => hi
)
Column a is 28
    
   

DB_FETCHMODE_OBJECT with your own object in PHP 5

<?php
// Once you have a valid DB object named $db...

class SomeResult {
    public $row_data;
    function __construct($data) {
        $this->row_data = $data;
    }
    function __get($variable) {
        return $this->row_data[$variable];
    }
}

$db->setFetchMode(DB_FETCHMODE_OBJECT, 'SomeResult');

$res =& $db->query('SELECT a, b FROM phptest WHERE a = 28');
$row =& $res->fetchRow();

print_r($row);
echo 'Column a is ' . $row->a;
?>

Output:

    
SomeResult Object
(
    [a] => 28
    [b] => hi
)
Column a is 28
    
   

DB_common::setOption()

DB_common::setOption() – Sets run-time configuration options for PEAR DB

Synopsis

integer setOption ( string $option , mixed $value )

Description

Sets run-time configuration options for PEAR DB.

Parameter

string $option

name of the option to set

mixed $value

value to set the option to

Return value

integer - DB_OK on success or a DB_Error object on failure

Throws

Note

This function can not be called statically.

Example

Simple setOption() example

<?php
// Once you have a valid DB object named $db...
$db->setOption('autofree', true);
?>

Portability for lowercasing and trimming

<?php
// Once you have a valid DB object named $db...
$db->setOption('portability',
    DB_PORTABILITY_LOWERCASE | DB_PORTABILITY_RTRIM);
?>

All portability options except trimming

<?php
// Once you have a valid DB object named $db...
$db->setOption('portability',
    DB_PORTABILITY_ALL ^ DB_PORTABILITY_RTRIM);
?>

DB_common::tableInfo()

DB_common::tableInfo() – Gets info about columns in a table or a query result

Synopsis

array tableInfo ( mixed $result , integer $mode = null )

Description

Get information about columns in a table or a query result.

Parameter

mixed $result

DB_result object from a query or a string containing the name of a table

If the name of the table needs to be delimited (ie: the name is a reserved word or has spaces in it), use the quoteIdentifier() method on the table name when passing it.

This can also be a query result resource identifier, but doing so is deprecated.

integer $mode

one of the tableInfo mode constants

Return value

array - an associative array of the table's information or a DB_Error object on failure

The array's contents depends on the $mode parameter.

The names of tables and columns will be lowercased if the DB_PORTABILITY_LOWERCASE portability mode is enabled.

The flags element contains a space separated list of extra information about the column. If the DBMS is able to report a column's default value, the value is passed through rawurlencode() to avoid problems caused by potential spaces in the value.

Most DBMS's only provide the table and flags elements if $result is a table name. Only fbsql and mysql provide full information from queries.

The type element contains the type returned by the DBMS. It varies from DBMS to DBMS.

tableInfo Modes

This section describes the format of the returned array and how it varies depending on which $mode was used when the function was called.

The sample output below is based on this query:

SELECT tblFoo.fldID, tblFoo.fldPhone, tblBar.fldId
  FROM tblFoo
  JOIN tblBar ON tblFoo.fldId = tblBar.fldId;

NULL or 0

[0] => Array (
  [table] => tblFoo
  [name] => fldId
  [type] => int
  [len] => 11
  [flags] => primary_key not_null
)
[1] => Array (
  [table] => tblFoo
  [name] => fldPhone
  [type] => string
  [len] => 20
  [flags] =>
)
[2] => Array (
  [table] => tblBar
  [name] => fldId
  [type] => int
  [len] => 11
  [flags] => primary_key not_null
)

DB_TABLEINFO_ORDER

In addition to the information found in the default output, a notation of the number of columns is provided by the num_fields element while the order element provides an array with the column names as the keys and their location index number (corresponding to the keys in the default output) as the values.

If a result set has identical field names, the last one is used.

[num_fields] => 3
[order] => Array (
  [fldId] => 2
  [fldTrans] => 1
)

DB_TABLEINFO_ORDERTABLE

Similar to DB_TABLEINFO_ORDER but adds more dimensions to the array in which the table names are keys and the field names are sub-keys. This is helpful for queries that join tables which have identical field names.

[num_fields] => 3
[ordertable] => Array (
  [tblFoo] => Array (
      [fldId] => 0
      [fldPhone] => 1
  )
  [tblBar] => Array (
      [fldId] => 2
  )
)

DB_TABLEINFO_FULL

Contains the information from both DB_TABLEINFO_ORDER and DB_TABLEINFO_ORDERTABLE

The tableInfo mode constants are bitwised, so they can be combined using |.

Throws

Note

This function can not be called statically.

tableInfo() is not portable because not all drivers have this method, many DBMS's are unable to determine table names from query results and the metadata returned by each database system varies dramatically.

Example

Finding information about a table

<?php
// Once you have a valid DB object named $db...
$info = $db->tableInfo('tablename');
print_r($info);
?>

Finding information about a query result

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

Prior to version 1.6.0, tableInfo() was a part of the DB_result class, so had to be called like this:

<?php
// Once you have a valid DB object named $db...
$res  =& $db->query('SELECT * FROM tablename');
$info =  $res->tableInfo();  // <---- DEPRECATED
print_r($info);
?>

DB_result

DB_result – DB result set

Description

DB_result contains the result set of a SQL query. An instance of this class will be returned by a number of DB_common methods.

DB_result::fetchInto()

DB_result::fetchInto() – Fetches a row of a result set into a variable

Synopsis

integer fetchInto ( array &$arr , integer $fetchMode = DB_FETCHMODE_DEFAULT , integer $rowNum = null )

Description

Places a row of data from a result set into a variable you provide then moves the result pointer to the next row. The data can be formatted as an array or an object.

Parameter

mixed $arr

reference to a variable to contain the row

integer $fetchMode

the fetch mode to use. The default is DB_FETCHMODE_DEFAULT, which tells this method to use DB's current fetch mode. DB's current default fetch mode can be changed using setFetchMode(). Potential values include:

• DB_FETCHMODE_ORDERED
• DB_FETCHMODE_ASSOC
• DB_FETCHMODE_OBJECT

integer $rowNum

the row number to fetch. Note that 0 returns the first row, 1 returns the second row, etc.

Return value

integer - DB_OK if a row is processed, NULL when the end of the result set is reached or a DB_Error object on failure

Note

This function can not be called statically.

Example

Using fetchInto()

<?php
// Once you have a valid DB object named $db...
$res =& $db->query('SELECT * FROM mytable');
while ($res->fetchInto($row)) {
    // Assuming DB's default fetchmode is
    // DB_FETCHMODE_ORDERED
    echo $row[0] . "\n";
}
?>

See

"Intro - Results", setFetchMode(), fetchRow()

DB_result::fetchRow()

DB_result::fetchRow() – Fetches a row from a result set

Synopsis

mixed &fetchRow ( integer $fetchmode = DB_DEFAULT_MODE , integer $rownum = null )

Description

Returns a row of data from a result set then moves the result pointer to the next row. The data can be formatted as an array or an object.

Parameter

integer $fetchmode

the fetch mode to use. The default is DB_FETCHMODE_DEFAULT, which tells this method to use DB's current fetch mode. DB's current default fetch mode can be changed using setFetchMode(). Potential values include:

• DB_FETCHMODE_ORDERED
• DB_FETCHMODE_ASSOC
• DB_FETCHMODE_OBJECT

integer $rownum

the row number to fetch

Return value

mixed - an array or object containing the row's data, NULL when the end of the result set is reached or a DB_Error object on failure

Note

This function can not be called statically.

Example

Using fetchRow()

<?php
// Once you have a valid DB object named $db...
$res =& $db->query('SELECT * FROM mytable');
while ($row =& $res->fetchRow()) {
    // Assuming DB's default fetchmode is
    // DB_FETCHMODE_ORDERED
    echo $row[0] . "\n";
}
?>

See

"Intro - Results", setFetchMode(), fetchInto()

DB_result::free()

DB_result::free() – Releases a result set

Synopsis

boolean free ( )

Description

Deletes the result set and frees the memory occupied by the result set. Does not delete the DB_result object itself.

Return value

boolean - Returns TRUE on success, FALSE on failure.

Example

Using free()

<?php
// Once you have a valid DB object named $db...
$res =& $db->query('SELECT name, address FROM clients');
while ($row =& $res->fetchRow()) {
    echo $row['name'] . ', ' . $row['address'] . "\n";
}
$res->free();
?>

Note

This function can not be called statically.

DB_result::nextResult()

DB_result::nextResult() – Gets result sets from multiple queries

Synopsis

boolean nextResult ( )

Description

Some database backends supports executing more then one query at the same time. With nextResult() you can go through the result sets.

This function has nothing to do with with executeMultiple().

Return value

boolean - TRUE if DB_result contains the next result set, FALSE if there is no further result set to fetch

Note

This function can not be called statically.

DB_result::numCols()

DB_result::numCols() – Gets number of columns in a result set

Synopsis

integer numCols ( )

Description

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

Return value

integer - number of columns or a DB_Error object on failure

Note

This function can not be called statically.

Example

Using numCols()

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

See

numRows(), affectedRows()

DB_result::numRows()

DB_result::numRows() – Gets number of rows in a result set

Synopsis

integer numRows ( )

Description

Get the number of rows in a result set.

For ibase, ifx and oci8, this method only works if the DB_PORTABILITY_NUMROWS portability option is enabled. In addition, for ibase and ifx, PEAR DB must be at version 1.7.0 or greater.

Does not work for Microsoft Access.

Return value

integer - number of rows or a DB_Error object on failure

Throws

Note

This function can not be called statically.

Example

Using numRows()

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

See

numCols(), affectedRows()

DB_Error

DB_Error – DB Error object

Description

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

The text messages returned by DB_Error::getMessage() are consistent between each DBMS.

The error code integers returned by DB_Error::getCode() are also consistent between each DBMS. The integers returned are based on the DB_ERROR_* constants defined in DB.php.

DB_Error::getDebugInfo() and DB_Error::getUserInfo() return complete native DBMS error reports.

Trapping errors and determining what happened

<?php
require_once 'DB.php';

$db =& DB::connect('pgsql://wronguser:badpw@localhost/thedb');
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 'Standard Message: ' . $db->getMessage() . "\n";
    echo 'Standard Code: ' . $db->getCode() . "\n";
    echo 'DBMS/User Message: ' . $db->getUserInfo() . "\n";
    echo 'DBMS/Debug Message: ' . $db->getDebugInfo() . "\n";
    exit;
}
?>