MDB2_Extended Class Reference

Inheritance diagram for MDB2_Extended:

Collaboration diagram for MDB2_Extended:

Public Member Functions
	autoPrepare ($table, $table_fields, $mode=MDB2_AUTOQUERY_INSERT, $where=false, $types=null, $result_types=MDB2_PREPARE_MANIP)
	Generate an insert, update or delete query and call prepare() on it. More...
&	autoExecute ($table, $fields_values, $mode=MDB2_AUTOQUERY_INSERT, $where=false, $types=null, $result_class=true, $result_types=MDB2_PREPARE_MANIP)
	Generate an insert, update or delete query and call prepare() and execute() on it. More...
	buildManipSQL ($table, $table_fields, $mode, $where=false)
	Make automaticaly an sql query for prepare() More...
&	limitQuery ($query, $types, $limit, $offset=0, $result_class=true, $result_wrap_class=false)
	Generates a limited query. More...
	execParam ($query, $params=array(), $param_types=null)
	Execute a parameterized DML statement. More...
	getOne ($query, $type=null, $params=array(), $param_types=null, $colnum=0)
	Fetch the first column of the first row of data returned from a query. More...
	getRow ($query, $types=null, $params=array(), $param_types=null, $fetchmode=MDB2_FETCHMODE_DEFAULT)
	Fetch the first row of data returned from a query. More...
	getCol ($query, $type=null, $params=array(), $param_types=null, $colnum=0)
	Fetch a single column from a result set and return it as an indexed array. More...
	getAll ($query, $types=null, $params=array(), $param_types=null, $fetchmode=MDB2_FETCHMODE_DEFAULT, $rekey=false, $force_array=false, $group=false)
	Fetch all the rows returned from a query. More...
	getAssoc ($query, $types=null, $params=array(), $param_types=null, $fetchmode=MDB2_FETCHMODE_DEFAULT, $force_array=false, $group=false)
	Fetch the entire result set of a query and return it as an associative array using the first column as the key. More...
	executeMultiple (&$stmt, $params=null)
	This function does several execute() calls on the same statement handle. More...
	getBeforeID ($table, $field=null, $ondemand=true, $quote=true)
	Returns the next free id of a sequence if the RDBMS does not support auto increment. More...
	getAfterID ($id, $table, $field=null)
	Returns the autoincrement ID if supported or $id. More...
Public Member Functions inherited from MDB2_Module_Common
	__construct ($db_index)
	Constructor. More...
	MDB2_Module_Common ($db_index)
	PHP 4 Constructor. More...
&	getDBInstance ()
	Get the instance of MDB2 associated with the module instance. More...

Additional Inherited Members
Data Fields inherited from MDB2_Module_Common
	$db_index

Detailed Description

Definition at line 68 of file Extended.php.

Member Function Documentation

autoExecute()

& MDB2_Extended::autoExecute	(		$table,
			$fields_values,
			$mode = MDB2_AUTOQUERY_INSERT,
			$where = false,
			$types = null,
			$result_class = true,
			$result_types = MDB2_PREPARE_MANIP
	)

Generate an insert, update or delete query and call prepare() and execute() on it.

Parameters

string	name of the table
array	assoc ($key=>$value) where $key is a field name and $value its value
int	type of query to build MDB2_AUTOQUERY_INSERT MDB2_AUTOQUERY_UPDATE MDB2_AUTOQUERY_DELETE MDB2_AUTOQUERY_SELECT
string	(in case of update and delete queries, this string will be put after the sql WHERE statement)
array	that contains the types of the placeholders
string	which specifies which result class to use
mixed	array that contains the types of the columns in the result set or MDB2_PREPARE_RESULT, if set to MDB2_PREPARE_MANIP the query is handled as a manipulation query

Returns

bool|MDB2_Error true on success, a MDB2 error on failure

See also

buildManipSQL

autoPrepare User interface

Definition at line 131 of file Extended.php.

References $params, $query, $result, array, autoPrepare(), buildManipSQL(), MDB2_Module_Common\getDBInstance(), PEAR\isError(), and MDB2_AUTOQUERY_SELECT.

    {
        $fields_values = (array)$fields_values;
        if ($mode == MDB2_AUTOQUERY_SELECT) {
            if (is_array($result_types)) {
                $keys = array_keys($result_types);
            } elseif (!empty($fields_values)) {
                $keys = $fields_values;
            } else {
                $keys = array();
            }
        } else {
            $keys = array_keys($fields_values);
        }
        $params = array_values($fields_values);
        if (empty($params)) {
            $query = $this->buildManipSQL($table, $keys, $mode, $where);

            $db =& $this->getDBInstance();
            if (PEAR::isError($db)) {
                return $db;
            }
            if ($mode == MDB2_AUTOQUERY_SELECT) {
                $result =& $db->query($query, $result_types, $result_class);
            } else {
                $result = $db->exec($query);
            }
        } else {
            $stmt = $this->autoPrepare($table, $keys, $mode, $where, $types, $result_types);
            if (PEAR::isError($stmt)) {
                return $stmt;
            }
            $result =& $stmt->execute($params, $result_class);
            $stmt->free();
        }
        return $result;
    }

Here is the call graph for this function:

autoPrepare()

MDB2_Extended::autoPrepare	(		$table,
			$table_fields,
			$mode = MDB2_AUTOQUERY_INSERT,
			$where = false,
			$types = null,
			$result_types = MDB2_PREPARE_MANIP
	)

Generate an insert, update or delete query and call prepare() on it.

Parameters

string	table
array	the fields names
int	type of query to build MDB2_AUTOQUERY_INSERT MDB2_AUTOQUERY_UPDATE MDB2_AUTOQUERY_DELETE MDB2_AUTOQUERY_SELECT
string	(in case of update and delete queries, this string will be put after the sql WHERE statement)
array	that contains the types of the placeholders
mixed	array that contains the types of the columns in the result set or MDB2_PREPARE_RESULT, if set to MDB2_PREPARE_MANIP the query is handled as a manipulation query

Returns

resource handle for the query

See also

buildManipSQL User interface

Definition at line 92 of file Extended.php.

References $query, buildManipSQL(), MDB2_Module_Common\getDBInstance(), and PEAR\isError().

Referenced by autoExecute().

    {
        $query = $this->buildManipSQL($table, $table_fields, $mode, $where);
        if (PEAR::isError($query)) {
            return $query;
        }
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }
        return $db->prepare($query, $types, $result_types);
    }

Here is the call graph for this function:

Here is the caller graph for this function:

buildManipSQL()

MDB2_Extended::buildManipSQL	(		$table,
			$table_fields,
			$mode,
			$where = false
	)

Make automaticaly an sql query for prepare()

Example : buildManipSQL('table_sql', array('field1', 'field2', 'field3'), MDB2_AUTOQUERY_INSERT) will return the string : INSERT INTO table_sql (field1,field2,field3) VALUES (?,?,?) NB : - This belongs more to a SQL Builder class, but this is a simple facility

• Be carefull ! If you don't give a $where param with an UPDATE/DELETE query, all the records of the table will be updated/deleted !

Parameters

string	name of the table
ordered	array containing the fields names
int	type of query to build MDB2_AUTOQUERY_INSERT MDB2_AUTOQUERY_UPDATE MDB2_AUTOQUERY_DELETE MDB2_AUTOQUERY_SELECT
string	(in case of update and delete queries, this string will be put after the sql WHERE statement)

Returns

string sql query for prepare() public

Definition at line 194 of file Extended.php.

References MDB2_Module_Common\getDBInstance(), PEAR\isError(), MDB2_AUTOQUERY_DELETE, MDB2_AUTOQUERY_INSERT, MDB2_AUTOQUERY_SELECT, MDB2_AUTOQUERY_UPDATE, MDB2_ERROR_NEED_MORE_DATA, and MDB2_ERROR_SYNTAX.

Referenced by autoExecute(), and autoPrepare().

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        if ($db->options['quote_identifier']) {
            $table = $db->quoteIdentifier($table);
        }

        if (!empty($table_fields) && $db->options['quote_identifier']) {
            foreach ($table_fields as $key => $field) {
                $table_fields[$key] = $db->quoteIdentifier($field);
            }
        }

        if ($where !== false && !is_null($where)) {
            if (is_array($where)) {
                $where = implode(' AND ', $where);
            }
            $where = ' WHERE '.$where;
        }

        switch ($mode) {
        case MDB2_AUTOQUERY_INSERT:
            if (empty($table_fields)) {
                return $db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null,
                'Insert requires table fields', __FUNCTION__);
            }
            $cols = implode(', ', $table_fields);
            $values = '?'.str_repeat(', ?', (count($table_fields) - 1));
            return 'INSERT INTO '.$table.' ('.$cols.') VALUES ('.$values.')';
            break;
        case MDB2_AUTOQUERY_UPDATE:
            if (empty($table_fields)) {
                return $db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null,
                'Update requires table fields', __FUNCTION__);
            }
            $set = implode(' = ?, ', $table_fields).' = ?';
            $sql = 'UPDATE '.$table.' SET '.$set.$where;
            return $sql;
            break;
        case MDB2_AUTOQUERY_DELETE:
            $sql = 'DELETE FROM '.$table.$where;
            return $sql;
            break;
        case MDB2_AUTOQUERY_SELECT:
            $cols = !empty($table_fields) ? implode(', ', $table_fields) : '*';
            $sql = 'SELECT '.$cols.' FROM '.$table.$where;
            return $sql;
            break;
        }
        return $db->raiseError(MDB2_ERROR_SYNTAX, null, null,
                'Non existant mode', __FUNCTION__);
    }

Here is the call graph for this function:

Here is the caller graph for this function:

execParam()

MDB2_Extended::execParam	(		$query,
			$params = array(),
			$param_types = null
	)

Execute a parameterized DML statement.

Parameters

string	the SQL query
array	if supplied, prepare/execute will be used with this array as execute parameters
array	that contains the types of the values defined in $params

Returns

int|MDB2_Error affected rows on success, a MDB2 error on failure public

Definition at line 297 of file Extended.php.

References $params, $query, $result, MDB2_Module_Common\getDBInstance(), PEAR\isError(), and MDB2_PREPARE_MANIP.

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        settype($params, 'array');
        if (empty($params)) {
            return $db->exec($query);
        }

        $stmt = $db->prepare($query, $param_types, MDB2_PREPARE_MANIP);
        if (PEAR::isError($stmt)) {
            return $stmt;
        }

        $result = $stmt->execute($params);
        if (PEAR::isError($result)) {
            return $result;
        }

        $stmt->free();
        return $result;
    }

Here is the call graph for this function:

executeMultiple()

MDB2_Extended::executeMultiple	(	&	$stmt,
			$params = null
	)

This function does several execute() calls on the same statement handle.

$params must be an array indexed numerically from 0, one execute call is done for every 'row' in the array.

If an error occurs during execute(), executeMultiple() does not execute the unfinished rows, but rather returns that error.

Parameters

resource	query handle from prepare()
array	numeric array containing the data to insert into the query

Returns

bool|MDB2_Error true on success, a MDB2 error on failure public

See also

prepare(), execute()

Definition at line 641 of file Extended.php.

References $params, $result, PEAR\isError(), and MDB2_OK.

    {
        for ($i = 0, $j = count($params); $i < $j; $i++) {
            $result = $stmt->execute($params[$i]);
            if (PEAR::isError($result)) {
                return $result;
            }
        }
        return MDB2_OK;
    }

Here is the call graph for this function:

getAfterID()

MDB2_Extended::getAfterID	(		$id,
			$table,
			$field = null
	)

Returns the autoincrement ID if supported or $id.

Parameters

mixed	value as returned by getBeforeId()
string	name of the table into which a new row was inserted
string	name of the field into which a new row was inserted

Returns

int|MDB2_Error id on success, a MDB2 error on failure public

Definition at line 700 of file Extended.php.

References MDB2_Module_Common\getDBInstance(), and PEAR\isError().

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        if ($db->supports('auto_increment') !== true) {
            return $id;
        }
        return $db->lastInsertID($table, $field);
    }

Here is the call graph for this function:

getAll()

MDB2_Extended::getAll	(		$query,
			$types = null,
			$params = array(),
			$param_types = null,
			$fetchmode = MDB2_FETCHMODE_DEFAULT,
			$rekey = false,
			$force_array = false,
			$group = false
	)

Fetch all the rows returned from a query.

Parameters

	string	the SQL query
	array	that contains the types of the columns in the result set
	array	if supplied, prepare/execute will be used with this array as execute parameters
	array	that contains the types of the values defined in $params
	int	the fetch mode to use
	bool	if set to true, the $all will have the first column as its first dimension
bool	$force_array	used only when the query returns exactly two columns. If true, the values of the returned array will be one-element arrays instead of scalars.
bool	$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.

Returns

array|MDB2_Error data on success, a MDB2 error on failure public

Definition at line 488 of file Extended.php.

References $params, $query, $result, MDB2_Module_Common\getDBInstance(), PEAR\isError(), and MDB2\isResultCommon().

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        settype($params, 'array');
        if (empty($params)) {
            return $db->queryAll($query, $types, $fetchmode, $rekey, $force_array, $group);
        }

        $stmt = $db->prepare($query, $param_types, $types);
        if (PEAR::isError($stmt)) {
            return $stmt;
        }

        $result = $stmt->execute($params);
        if (!MDB2::isResultCommon($result)) {
            return $result;
        }

        $all = $result->fetchAll($fetchmode, $rekey, $force_array, $group);
        $stmt->free();
        $result->free();
        return $all;
    }

Here is the call graph for this function:

getAssoc()

MDB2_Extended::getAssoc	(		$query,
			$types = null,
			$params = array(),
			$param_types = null,
			$fetchmode = MDB2_FETCHMODE_DEFAULT,
			$force_array = false,
			$group = false
	)

Fetch the entire result set of a query and return it as an associative array using the first column as the key.

If the result set contains more than two columns, the value will be an array of the values from column 2-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). A MDB2 error code is returned on errors. If the result set contains fewer than two columns, a MDB2_ERROR_TRUNCATED error is returned.

For example, if the table 'mytable' contains:

  ID      TEXT       DATE
--------------------------------
  1       'one'      944679408
  2       'two'      944679408
  3       'three'    944679408

Then the call getAssoc('SELECT id,text FROM mytable') returns:

   array(
     '1' => 'one',
     '2' => 'two',
     '3' => 'three',
   )

...while the call getAssoc('SELECT id,text,date FROM mytable') returns:

   array(
     '1' => array('one', '944679408'),
     '2' => array('two', '944679408'),
     '3' => array('three', '944679408')
   )

If the more than one row occurs with the same value in the first column, the last row overwrites all previous ones by default. Use the $group parameter if you don't want to overwrite like this. Example:

getAssoc('SELECT category,id,name FROM mytable', null, null
          MDB2_FETCHMODE_ASSOC, false, true) returns:
   array(
     '1' => array(array('id' => '4', 'name' => 'number four'),
                  array('id' => '6', 'name' => 'number six')
            ),
     '9' => array(array('id' => '4', 'name' => 'number four'),
                  array('id' => '6', 'name' => 'number six')
            )
   )

Keep in mind that database functions in PHP usually return string values for results regardless of the database's internal type.

Parameters

	string	the SQL query
	array	that contains the types of the columns in the result set
	array	if supplied, prepare/execute will be used with this array as execute parameters
	array	that contains the types of the values defined in $params
bool	$force_array	used only when the query returns exactly two columns. If TRUE, the values of the returned array will be one-element arrays instead of scalars.
bool	$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.

Returns

array|MDB2_Error data on success, a MDB2 error on failure public

Definition at line 594 of file Extended.php.

References $params, $query, $result, MDB2_Module_Common\getDBInstance(), PEAR\isError(), and MDB2\isResultCommon().

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        settype($params, 'array');
        if (empty($params)) {
            return $db->queryAll($query, $types, $fetchmode, true, $force_array, $group);
        }

        $stmt = $db->prepare($query, $param_types, $types);
        if (PEAR::isError($stmt)) {
            return $stmt;
        }

        $result = $stmt->execute($params);
        if (!MDB2::isResultCommon($result)) {
            return $result;
        }

        $all = $result->fetchAll($fetchmode, true, $force_array, $group);
        $stmt->free();
        $result->free();
        return $all;
    }

Here is the call graph for this function:

getBeforeID()

MDB2_Extended::getBeforeID	(		$table,
			$field = null,
			$ondemand = true,
			$quote = true
	)

Returns the next free id of a sequence if the RDBMS does not support auto increment.

Parameters

string	name of the table into which a new row was inserted
string	name of the field into which a new row was inserted
bool	when true the sequence is automatic created, if it not exists
bool	if the returned value should be quoted

Returns

int|MDB2_Error id on success, a MDB2 error on failure public

Definition at line 667 of file Extended.php.

References MDB2_Module_Common\getDBInstance(), and PEAR\isError().

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        if ($db->supports('auto_increment') !== true) {
            $seq = $table.(empty($field) ? '' : '_'.$field);
            $id = $db->nextID($seq, $ondemand);
            if (!$quote || PEAR::isError($id)) {
                return $id;
            }
            return $db->quote($id, 'integer');
        } elseif (!$quote) {
            return null;
        }
        return 'NULL';
    }

Here is the call graph for this function:

getCol()

MDB2_Extended::getCol	(		$query,
			$type = null,
			$params = array(),
			$param_types = null,
			$colnum = 0
	)

Fetch a single column from a result set and return it as an indexed array.

Parameters

string	the SQL query
string	that contains the type of the column in the result set
array	if supplied, prepare/execute will be used with this array as execute parameters
array	that contains the types of the values defined in $params
int|string	which column to return

Returns

array|MDB2_Error data on success, a MDB2 error on failure public

Definition at line 433 of file Extended.php.

References $params, $query, $result, MDB2_Module_Common\getDBInstance(), PEAR\isError(), and MDB2\isResultCommon().

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        settype($params, 'array');
        settype($type, 'array');
        if (empty($params)) {
            return $db->queryCol($query, $type, $colnum);
        }

        $stmt = $db->prepare($query, $param_types, $type);
        if (PEAR::isError($stmt)) {
            return $stmt;
        }

        $result = $stmt->execute($params);
        if (!MDB2::isResultCommon($result)) {
            return $result;
        }

        $col = $result->fetchCol($colnum);
        $stmt->free();
        $result->free();
        return $col;
    }

Here is the call graph for this function:

getOne()

MDB2_Extended::getOne	(		$query,
			$type = null,
			$params = array(),
			$param_types = null,
			$colnum = 0
	)

Fetch the first column of the first row of data returned from a query.

Takes care of doing the query and freeing the results when finished.

Parameters

string	the SQL query
string	that contains the type of the column in the result set
array	if supplied, prepare/execute will be used with this array as execute parameters
array	that contains the types of the values defined in $params
int|string	which column to return

Returns

scalar|MDB2_Error data on success, a MDB2 error on failure public

Definition at line 340 of file Extended.php.

References $params, $query, $result, MDB2_Module_Common\getDBInstance(), PEAR\isError(), and MDB2\isResultCommon().

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        settype($params, 'array');
        settype($type, 'array');
        if (empty($params)) {
            return $db->queryOne($query, $type, $colnum);
        }

        $stmt = $db->prepare($query, $param_types, $type);
        if (PEAR::isError($stmt)) {
            return $stmt;
        }

        $result = $stmt->execute($params);
        if (!MDB2::isResultCommon($result)) {
            return $result;
        }

        $one = $result->fetchOne($colnum);
        $stmt->free();
        $result->free();
        return $one;
    }

Here is the call graph for this function:

getRow()

MDB2_Extended::getRow	(		$query,
			$types = null,
			$params = array(),
			$param_types = null,
			$fetchmode = MDB2_FETCHMODE_DEFAULT
	)

Fetch the first row of data returned from a query.

Takes care of doing the query and freeing the results when finished.

Parameters

string	the SQL query
array	that contains the types of the columns in the result set
array	if supplied, prepare/execute will be used with this array as execute parameters
array	that contains the types of the values defined in $params
int	the fetch mode to use

Returns

array|MDB2_Error data on success, a MDB2 error on failure public

Definition at line 387 of file Extended.php.

References $params, $query, $result, $row, MDB2_Module_Common\getDBInstance(), PEAR\isError(), and MDB2\isResultCommon().

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        settype($params, 'array');
        if (empty($params)) {
            return $db->queryRow($query, $types, $fetchmode);
        }

        $stmt = $db->prepare($query, $param_types, $types);
        if (PEAR::isError($stmt)) {
            return $stmt;
        }

        $result = $stmt->execute($params);
        if (!MDB2::isResultCommon($result)) {
            return $result;
        }

        $row = $result->fetchRow($fetchmode);
        $stmt->free();
        $result->free();
        return $row;
    }

Here is the call graph for this function:

limitQuery()

& MDB2_Extended::limitQuery	(		$query,
			$types,
			$limit,
			$offset = 0,
			$result_class = true,
			$result_wrap_class = false
	)

Generates a limited query.

Parameters

string	query
array	that contains the types of the columns in the result set
integer	the numbers of rows to fetch
integer	the row to start to fetching
string	which specifies which result class to use
mixed	string which specifies which class to wrap results in

Returns

MDB2_Result|MDB2_Error result set on success, a MDB2 error on failure public

Definition at line 267 of file Extended.php.

References $query, $result, MDB2_Module_Common\getDBInstance(), and PEAR\isError().

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        $result = $db->setLimit($limit, $offset);
        if (PEAR::isError($result)) {
            return $result;
        }
        $result =& $db->query($query, $types, $result_class, $result_wrap_class);
        return $result;
    }

Here is the call graph for this function:

---

The documentation for this class was generated from the following file:

• Services/Database/lib/PEAR/MDB2/Extended.php