Query a database table as an object

In this example, we implement basic queries with the table as an object: insert, update, deletion, and fetching.

Components used in this example

• Zend_Db
• Zend_Db_Adapter_Pdo_Sqlite
• Zend_Db_Table_Abstract

---

MyBugs MyDbTable MyDbAdmin MyHtml Entry point HTML | Try it | View source code

Implementation of the table bugs

class MyBugs extends Zend_Db_Table_Abstract
{
    protected $_name = 'bugs';

}

Implementation of the queries

• We define the set of the available fetching modes.

class MyDbTable
{
    // We define the set of the available fetching modes.
    private $_fetchModes = array(
        'FETCH_ASSOC' => Zend_Db::FETCH_ASSOC,
        'FETCH_NUM' => Zend_Db::FETCH_NUM,
        'FETCH_BOTH' => Zend_Db::FETCH_BOTH,
        'FETCH_COLUMN' => Zend_Db::FETCH_COLUMN,
        'FETCH_OBJ' => Zend_Db::FETCH_OBJ,
    );

    private $_dbAdmin;

Storing the database management object

    public function __construct($dbAdmin)
    {
        $this->_dbAdmin = $dbAdmin;
    }

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.
• 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 define the default adapter.
• If the user requested to fetch data, we return the selected rows.
• If the user requested to insert data, we return the row identifier.
• If the user requested to update data, we return the number of rows updated.
• If the user requested to delete data, we return the number of rows deleted.
• 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 {
            if ($restore) {
                // If the user requested to restore the database, we load the initial data.
                $data = $this->_dbAdmin->import(true);
                $result = '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->_dbAdmin->import();

                // We define the default adapter.
                Zend_Db_Table_Abstract::setDefaultAdapter($this->_dbAdmin->getAdapter());

                switch ($method) {
                    case 'fetchAll':
                    case 'fetchRow':
                        // If the user requested to fetch data, we return the selected rows.
                        $result = $this->_fetch($row, $method, $mode);
                        break;

                    case 'insert';
                        // If the user requested to insert data, we return the row identifier.
                        $id = $this->_insert($row);
                        $result = $id? "Row $id was inserted!" : 'No row was inserted!';
                        break;

                    case 'update';
                        // If the user requested to update data, we return the number of rows updated.
                        $nb = $this->_update($row);
                        $result = "$nb row(s) were updated!";
                        break;

                    case 'delete';
                        // If the user requested to delete data, we return the number of rows deleted.
                        $nb = $this->_delete($row);
                        $result = "$nb row(s) were deleted!";
                        break;

                    default:
                        $result = 'Please select a method and fill in values as needed!';
                }

            }

            // We export the database.
            $data = $this->_dbAdmin->export();

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

        return array($row, $method, $mode, $data, $result);
    }

Extraction of the parameters from the GET request

• We extract the query method.
• We extract the fetching mode. We ignore invalid modes.
• We extract the row details. We ignore invalid columns.

    private function _getParameters()
    {
        // We extract the query method.
        $method = isset($_GET['method'])? $_GET['method'] : null;

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

        // We extract the row details. We ignore invalid columns.
        $row = $this->_dbAdmin->filterColumns($_GET);

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

        return array($method, $row, $mode, $restore);
    }

Inserting a row in a database table

• We insert the row unless it is empty.
• We return the row identifier.

    private function _insert($row)
    {
        $table = new MyBugs();
        // We insert the row unless it is empty.
        $id = empty($row)? null : $table->insert($row);

        // We return the row identifier.
        return $id;
    }

Updating a row in a database table

• We update one row if the identifier is given. Or we update all the rows.
• We return the number of updated rows.

    private function _update($row)
    {
        $table = new MyBugs();
        // We update one row if the identifier is given. Or we update all the rows.
        $where = empty($row['id'])? array() : array('id = ?' => $row['id']);
        $nb = $table->update($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($row)
    {
        $table = new MyBugs();

        // We return the number of deleted rows.
        return $table->delete($this->_setWhere($row));
    }

Fetching rows from a database table

• We build the query using arrays.
• We set the fetching mode if requested.
• We fetch and we return the data.

    private function _fetch($row, $method, $mode)
    {
        $table = new MyBugs();
        $select = $table->select();

        // We build the query using arrays.
        foreach($this->_setWhere($row) as $column => $value) {
            $select->where($column, $value);
        }

        // We set the fetching mode if requested.
        $mode and $table->getAdapter()->setFetchMode($this->_fetchModes[$mode]);

        // We fetch and we return the data.
        return $table->$method($select)->toArray();
    }

Building of the where clause

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

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

        return $where;
    }

}

Implementation of the queries

• We define a set of initial data.

class MyDbAdmin
{
    const cookie = 'db-table';

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

    private $_db;
    private $_columns;

Creation of the database

• We create the table named bugs. The description is mandatory and must be unique. The other fields have a default value.

    public function create()
    {
        $this->_db = new Zend_Db_Adapter_Pdo_Sqlite(array('dbname' => ':memory:'));
        // We create the table named bugs.
        // The description is mandatory and must be unique.
        // The other fields have a default value.
        $query = 'CREATE TABLE bugs ('
            . 'id INTEGER NOT NULL PRIMARY KEY, '
            . 'description TEXT NOT NULL UNIQUE, '
            . 'status TEXT DEFAULT "flying", '
            . 'reported_by TEXT DEFAULT "unknown", '
            . 'created_on TEXT DEFAULT CURRENT_DATE)';
        $this->_db->query($query);
    }

Getting the database adapter object

    public function getAdapter()
    {
        return $this->_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.

    public function import($restore = false)
    {
        // We import the data stored inside a cookie if available and valid.
        // Or we use the initial data.
        !$restore and isset($_COOKIE[self::cookie]) and
        $data = unserialize(stripcslashes($_COOKIE[self::cookie])) and is_array($data) or
        $data = $this->_data;

        // We load the table named bug.
        foreach($data as $row){
            $row = $this->filterColumns($row);
            $this->_db->insert('bugs', $row);
        }

        // 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 $this->_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.

    public function export()
    {
        // We fetch the data.
        $data = $this->_db->fetchAssoc('SELECT * FROM bugs');
        // We serialize the data and we store the data inside a cookie for one hour.
        setcookie(self::cookie, serialize($data), time() + 3600, '/');

        return $data;
    }

Extracting the valid columns from an array of data

    public function filterColumns($row)
    {
        $row = array_intersect_key($row, $this->_getColumns());
        $row = array_filter($row);

        return $row;
    }

Getting the list of columns from the table

    private function _getColumns()
    {
        if (empty($this->_columns)) {
            foreach($this->_db->describeTable('bugs') as $column) {
                $this->_columns[$column['COLUMN_NAME']] = true;
            }
        }

        return $this->_columns;
    }

}

Displaying items

class MyHtml
{

Displaying the title of the page based on the file name.

    public static function printTitle()
    {
        $basename = basename(__FILE__, '.php');
        $title = ucwords(str_replace('-' , ' ', $basename));
        $zfVersion = Zend_Version::VERSION;
        $phpVersion = phpversion();
        echo "ZfEx $title (ZF/$zfVersion PHP/$phpVersion)";
    }

Displaying a string or an array

• If the data is an array, we convert the array into a set of lines.
• We converts special characters into HTML entities.
• We convert new-line characters into line breaks.

    public static function display($mixed)
    {
        // If the data is an array, we convert the array into a set of lines.
        is_array($mixed) and $mixed = implode("\n", $mixed);
        $mixed = stripslashes($mixed);
        // We converts special characters into HTML entities.
        $mixed = htmlspecialchars($mixed, ENT_QUOTES, 'UTF-8');
        // We convert new-line characters into line breaks.
        echo nl2br($mixed);
    }

Displaying the selected option

    public static function printSelected($value, $target)
    {
        $value == $target and print 'selected="selected"';
    }

Colorization of data

• We export the data as valid PHP code.
• We add the PHP tag, we colorize the code, we remove the PHP tag.
• We display the data.

    public static function colorize($mixed)
    {
        // We export the data as valid PHP code.
        $mixed = var_export($mixed, true);
        // We add the PHP tag, we colorize the code, we remove the PHP tag.
        $mixed = '<?' . "php $mixed";
        $mixed = highlight_string($mixed, true);
        $mixed = str_replace('&lt;?php&nbsp;', '', $mixed);
        // We display the data.
        echo "<pre>$mixed</pre>";
    }

}

Processing the query

• We create the database.
• We get the database content and the result of the query, to display in the form.

$dbAdmin = new MyDbAdmin;
// We create the database.
$dbAdmin->create();

$dbTable = new MyDbTable($dbAdmin);
// We get the database content and the result of the query, to display in the form.
list($row, $method, $mode, $data, $result) = $dbTable->process();

<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title><?php MyHtml::printTitle();?></title>
    <style type="text/css">
      body, td {
          font-family: arial, sans-serif;
          font-size: 0.9em;
      }
      pre {
          margin-top: 0;
      }
    </style>
  </head>

  <body>

    <p>EXAMPLE <?php MyHtml::printTitle();?></p>
    <hr />

    <form>
      <table>

        <tr>
          <td>Id</td>
          <td><input type="text" name="id"
                     value="<?php isset($row['id']) and MyHtml::display($row['id']);?>" />
          </td>
          <td>Optional for inserts.
              <br />Mandatory to update a specific row otherwise all rows are updated if blank.
          </td>
        </tr>

        <tr>
          <td>Description</td>
          <td><input type="text" name="description"
                     value="<?php isset($row['description']) and
                                  MyHtml::display($row['description']);?>" />
          </td>
          <td>Mandatory on inserts.</td>
        </tr>

        <tr>
          <td>Status</td>
          <td><input type="text" name="status"
                     value="<?php isset($row['status']) and
                                  MyHtml::display($row['status']);?>" />
          </td>
        </tr>

        <tr>
          <td>Reported by</td>
          <td><input type="text" name="reported_by"
                     value="<?php isset($row['reported_by']) and
                                  MyHtml::display($row['reported_by']);?>" />
          </td>
        </tr>

        <tr>
          <td>Created on</td>
          <td><input type="text" name="created_on"
                     value="<?php isset($row['created_on'])
                                  and MyHtml::display($row['created_on']);?>" />
          </td>
        </tr>

        <tr><td><br /></td></tr>

        <tr>
          <td>Fetch mode</td>
          <td>
            <select name="mode">
              <option></option>
              <option <?php MyHtml::printSelected($mode, 'FETCH_ASSOC');?>>FETCH_ASSOC</option>
              <option <?php MyHtml::printSelected($mode, 'FETCH_NUM');?>>FETCH_NUM</option>
              <option <?php MyHtml::printSelected($mode, 'FETCH_BOTH');?>>FETCH_BOTH</option>
              <option <?php MyHtml::printSelected($mode, 'FETCH_COLUMN');?>>FETCH_COLUMN</option>
              <option <?php MyHtml::printSelected($mode, 'FETCH_OBJ');?>>FETCH_OBJ</option>
            </select>
          </td>
          <td>(Note: the mode does seem to have any effect with ZF 1.9.5 !)</td>
        </tr>

        <tr>
          <td>Method</td>
          <td>
            <select name="method">
              <option <?php MyHtml::printSelected($method, 'fetchAll');?>>fetchAll</option>
              <option <?php MyHtml::printSelected($method, 'fetchRow');?>>fetchRow</option>
              <option <?php MyHtml::printSelected($method, 'insert');?>>insert</option>
              <option <?php MyHtml::printSelected($method, 'update');?>>update</option>
              <option <?php MyHtml::printSelected($method, 'delete');?>>delete</option>
            </select>
          </td>
          <td>All rows are deleted or selected if values are left blank.</td>
        </tr>

        <tr>
          <td></td>
          <td>
            <input type="submit" value="Submit" />
          </td>
        </tr>

        <tr>
          <td></td>
          <td><a href="?restore=1">Restore default values</a></td>
        </tr>

      </table>
    </form>

    <hr />
    QUERY RESULT
    <?php MyHtml::colorize($result);?>

    <hr />
    DATABASE CONTENT
    <?php MyHtml::colorize($data);?>

  </body>
</html>