Tuesday, December 1, 2009

Query a database

In this example, we implement basic queries: insert, update, deletion, and fetching.

Components used in this example
Implementation of the queries
  • The database has one table.
  • We define a set of initial data.
  • We define the set of the available methods and the available fetching modes.

class MyDbQuery
// The database has one table.
private $_columns = array('id''description''status''reported_by''created_on');

// We define a set of initial data.
private $_data = array(
'description' => 'fly''status' => 'buzzing''reported_by' => 'john'),
'description' => 'bee''status' => 'gone''reported_by' => 'jane'),
'description' => 'mosquito''status' => 'dead''reported_by' => 'john'),

// We define the set of the available methods and the available fetching modes.
private $_methods = array('insert''delete''update');
$_fetchMethods = array('fetchAll''fetchAssoc''fetchCol''fetchPairs''fetchRow');
$_fetchModes = array(
Processing a query
  • We get the method, the row details, and the fetching mode from the GET request. Or we get the request to restore the database.
  • We create an empty database in memory.
  • If the user requested to restore the database, we load the initial data.
  • If the user is running a query, we import the existing data into the database if they are available. Otherwise, we load the initial data.
  • We run the query.
  • If the user requested to fetch data, we return the selected rows.
  • If the user requested to insert, update or delete data, we return the number of affected rows.
  • We export the database.
  • If we catch an exception, we return the error message.

    public function process()
// We get the method, the row details, and the fetching mode from the GET request.
        // Or we get the request to restore the database.
list($method$row$mode$restore) = $this->_getParameters();

$data = array();

        try {
// We create an empty database in memory.
$db $this->_create();

            if (
$restore) {
// If the user requested to restore the database, we load the initial data.
$data $this->_import($dbtrue);
$message 'Database restored!';
            } else {
// If the user is running a query, we import the existing data into the database
                // if they are available. Otherwise, we load the initial data.
$data $this->_import($db);

// We run the query.
if (in_array($method$this->_fetchMethods)) {
// If the user requested to fetch data, we return the selected rows.
$message $this->_fetch($db$row$method$mode);
                } else if (
in_array($method$this->_methods)) {
// If the user requested to insert, update or delete data,
                    // we return the number of affected rows.
$call "_$method";
$nb $this->$call($db$row);
$message "$nb row(s) were {$method}ed!";
                } else {
$message 'Please select a method and fill in values as needed!';

// We export the database.
$data $this->_export($db);

        } catch (
Exception $e) {
// If we catch an exception, we return the error message.
$message $e->getMessage();

        return array(
Extraction of the parameters from the GET request
  • We extract the name of the method. We ignore invalid methods.
  • We extract the row details and we ignore invalid columns.
  • We extract the fetching mode. We ignore invalid modes.

    private function _getParameters()
// We extract the name of the method. We ignore invalid methods.
isset($_GET['method']) and $method $_GET['method'] and
in_array($method$this->_methods) or in_array($method$this->_fetchMethods)) or
$method null;

// We extract the row details and we ignore invalid columns.
$row array_intersect_key($_GETarray_flip($this->_columns));
$row array_filter($row);

// We extract the fetching mode. We ignore invalid modes.
isset($_GET['mode']) and $mode $_GET['mode'] and
$this->_fetchModes[$mode]) and $mode $this->_fetchModes[$mode] or
$mode null;

$restore = !empty($_GET['restore']);

        return array(
Creation of the database
  • We create the table named bug. The description is mandatory and must be unique. The other fields have a default value.
  • We return the database connection.

    private function _create()
$db = new Zend_Db_Adapter_Pdo_Sqlite(array('dbname' => ':memory:'));
// We create the table named bug.
        // The description is mandatory and must be unique.
        // The other fields have a default value.
$query 'CREATE TABLE bugs ('
'description TEXT NOT NULL UNIQUE, '
'status TEXT DEFAULT "flying", '
'reported_by TEXT DEFAULT "unknown", '

// We return the database connection.
return $db;
Importing the database
  • We import the data stored inside a cookie if available and valid. Or we use the initial data.
  • We load the table named bug.
  • We return the data. It is necessary to fetch the data from the database in order to get the entries with their default values.

    private function _import($db$restore false)
// We import the data stored inside a cookie if available and valid.
        // Or we use the initial data.
!$restore and isset($_COOKIE['db-query']) and
$data unserialize(stripcslashes($_COOKIE['db-query'])) and is_array($data) or
$data $this->_data;

// We load the table named bug.
foreach($data as $row){
$row array_intersect_key($rowarray_flip($this->_columns));

// We return the data.
        // It is necessary to fetch the data from the database
        // in order to get the entries with their default values.
return $db->fetchAssoc('SELECT * FROM bugs');
Exporting the database
  • We fetch the data.
  • We serialize the data and we store the data inside a cookie for one hour.

    private function _export($db)
// We fetch the data.
$data $db->fetchAssoc('SELECT * FROM bugs');
// We serialize the data and we store the data inside a cookie for one hour.
setcookie('db-query'serialize($data), time() + 3600'/');

Inserting a row in a database table
  • We ignore the query if the entry is empty.
  • We return the number of inserted rows.

    private function _insert($db$row)
$nb 0;
// We ignore the query if the entry is empty.
empty($row) or $nb $db->insert('bugs'$row);

// We return the number of inserted rows.
return $nb;
Updating a row in a database table
  • We ignore the query if the row id is empty.
  • We return the number of updated rows.

    private function _update($db$row)
$nb 0;
// We ignore the query if the row id is empty.
isset($row['id']) and $where['id = ?'] = $row['id'] and
$nb $db->update('bugs'$row$where);

// We return the number of updated rows.
return $nb;
Deleting a row in a database table
  • We return the number of deleted rows.

    private function _delete($db$row)
// We return the number of deleted rows.
return $db->delete('bugs'$this->_setWhere($row));
Fetching rows from a database table
  • We set the fetching mode if requested.
  • We build the query using arrays.
  • We fetch and we return the data.

    private function _fetch($db$row$method$mode)
// We set the fetching mode if requested.
$mode and $db->setFetchMode($mode);

// We build the query using arrays.
$query 'SELECT * from bugs';
$where$bind) = $this->_setWhereForFetch($row);
$where and $query .= " WHERE $where";

// We fetch and we return the data.
return $db->$method($query$bind);
Building of the where clause

    private function _setWhere($row)
$where = array();

$row as $column => $value){
$where["$column = ?"] = $value;

Building of the where clause to fetch data

    private function _setWhereForFetch($row)
$where $this->_setWhere($row);
$bind array_values($where);
$where implode(' AND 'array_keys($where));

        return array(


No comments:

Post a Comment