Select table rows

In this example, we select table rows.

Components used in this example

• Zend_Db
• Zend_Db_Adapter_Pdo_Sqlite
• Zend_Db_Select
• Zend_Db_Statement_Pdo

---

MyDbSelect MyDbCreate MyDbFetch MyHtml Entry point HTML | Try it | View source code

Preparing and executing the select statement

• We define the set of the available methods to join tables.

class MyDbSelect
{
    // We define the set of the available methods to join tables.
    public $joins = array('joinInner', 'joinLeft', 'joinRight', 'joinFull');
    private $_db;

Storing the database connection

    public function __construct($db)
    {
        $this->_db = $db;
    }

Processing the query

• We get the list of selected columns, the column values, and the selected method, from the GET request.
• We build the list of columns to select.
• We build the select query.
• We build the where clause.
• We execute the query.
• If we catch an exception, we return the error message.

    public function process()
    {
        // We get the list of selected columns, the column values,
        // and the selected method, from the GET request.
        list($isBug, $isStatus, $isReportedBy, $isCreatedOn,
            $bugId, $statusId, $reportedById, $createdOn, $join) = $this->_getParameters();

        try {
            // We build the list of columns to select.
            list($bugCols, $statusCols, $reportedByCols) =
                $this->_selectColumns($isBug, $isStatus, $isReportedBy, $isCreatedOn);

            // We build the select query.
            $select = new Zend_Db_Select($this->_db);
            $select
                ->distinct()
                ->from(array('b' => 'bug'), $bugCols)
                ->$join(array('s' => 'status'), 'b.status_id = s.id', $statusCols)
                ->$join(array('r' => 'reported_by'), 'b.reported_by_id = r.id', $reportedByCols);

            //We build the where clause.
            $bugId and $select->where('b.id = ?', $bugId);
            $statusId and $select->where('s.id = ?', $statusId);
            $reportedById and $select->where('r.id = ?', $reportedById);

            // We execute the query.
            $stmt = $this->_db->query($select);
            $result = $stmt->fetchAll();

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

        return array(
            $isBug, $isStatus, $isReportedBy, $isCreatedOn,
            $bugId, $statusId, $reportedById, $createdOn, $join,
            $result, $select->__toString());
    }

Extraction of the parameters from the GET request

• We get the names of the columns to select.
• We get the values of the columns to select.
• We extract the name of the method. We ignore invalid methods, the default method is an inner join.

    private function _getParameters()
    {
        // We get the names of the columns to select.
        $isBug = isset($_GET['is-bug'])? $_GET['is-bug'] : null;
        $isStatus = isset($_GET['is-status'])? $_GET['is-status'] : null;
        $isReportedBy = isset($_GET['is-reported_by'])? $_GET['is-reported_by'] : null;
        $isCreatedOn = isset($_GET['is-created_on'])? $_GET['is-created_on'] : null;

        // We get the values of the columns to select.
        $bugId = isset($_GET['bug'])? $_GET['bug'] : null;
        $statusId = isset($_GET['status'])? $_GET['status'] : null;
        $reportedById = isset($_GET['reported_by'])? $_GET['reported_by'] : null;
        $createdOn = isset($_GET['created_on'])? $_GET['created_on'] : null;

        // We extract the name of the method.
        // We ignore invalid methods, the default method is an inner join.
        isset($_GET['join']) and $join = $_GET['join'] and
        in_array($join, $this->joins) or $join = 'joinInner';

        return array($isBug, $isStatus, $isReportedBy, $isCreatedOn,
            $bugId, $statusId, $reportedById, $createdOn, $join);
    }

Building the list of columns to select

• All columns will be selected by default.
• For each table, we select one or two specific columns. Or we select all columns if no column from any table is selected. Otherwise we select no column. Columns are renamed if needed so they can be fetched.

    private function _selectColumns($isBug, $isStatus, $isReportedBy, $isCreatedOn)
    {
        // All columns will be selected by default.
        $all = !($isBug or $isStatus or $isReportedBy or $isCreatedOn);

        // For each table, we select one or two specific columns.
        // Or we select all columns if no column from any table is selected.
        // Otherwise we select no column.
        // Columns are renamed if needed so they can be fetched.

        $isBug and $bugCols['Bug'] = 'name';
        $isCreatedOn and $bugCols['Created on'] = 'created_on';
        isset($bugCols) or
        $all and $bugCols = array('b_id' => 'id', 'b_name' => 'name',
            'status_id', 'reported_by_id', 'created_on') or
        $bugCols = array();

        $isStatus and $statusCols['Status'] = 'name' or
        $all and $statusCols = array('s_id' => 'id', 's_name' => 'name') or
        $statusCols = array();

        $isReportedBy and $reportedByCols['Reported by'] = 'name' or
        $all and $reportedByCols = array('r_id' => 'id', 'r_name' => 'name') or
        $reportedByCols = array();

        return array($bugCols, $statusCols, $reportedByCols);
    }

}

Creation of the database

class MyDbCreate
{
    private $_db;

Creation of all tables

• We create the database in memory.
• We create each table.
• We return the database connection.

    public function process()
    {
        // We create the database in memory.
        $this->_db = new Zend_Db_Adapter_Pdo_Sqlite(array('dbname' => ':memory:'));
        // We create each table.
        $this->_createBugs();
        $this->_createStatus();
        $this->_createReportedBy();

        // We return the database connection.
        return $this->_db;
    }

Creation of the table bug

• status_id is a foreign key to the table status. reported_by_id is a foreign key to the table reported_by.
• We add 10 rows to the table.

    private function _createBugs()
    {
        // status_id is a foreign key to the table status.
        // reported_by_id is a foreign key to the table reported_by.
        $query = 'CREATE TABLE bug ('
            . 'id INTEGER PRIMARY KEY, name NOT NULL UNIQUE, '
            . 'status_id, reported_by_id, created_on)';
        $this->_db->query($query);

        // We add 10 rows to the table.
        $sql = "INSERT INTO bug (name, status_id, reported_by_id, created_on) VALUES (?, ?, ?, ?)";
        $stmt = new Zend_Db_Statement_Pdo($this->_db, $sql);
        $stmt->execute(array('fly', 'f', 'a', '2009'));
        $stmt->execute(array('bee', 'h', 'j', '2008'));
        $stmt->execute(array('mosquito', 'a', 'p', '2007'));
        $stmt->execute(array('ant', 'c', 'z', '2006'));
        $stmt->execute(array('spider', 'c', null, '2009'));
        $stmt->execute(array('wasps', null, 'j', '2008'));
        $stmt->execute(array('mite', 'd', 'p', '2007'));
        $stmt->execute(array('beetle', 'f', 'z', '2006'));
        $stmt->execute(array('earwig', 'c', 'a', '2009'));
        $stmt->execute(array('tick', 'h', 'j', '2008'));
    }

Creation of the table status

    private function _createStatus()
    {
        $query = 'CREATE TABLE status (id PRIMARY KEY, name NOT NULL UNIQUE)';
        $this->_db->query($query);

        $sql = "INSERT INTO status (id, name) VALUES (?, ?)";
        $stmt = new Zend_Db_Statement_Pdo($this->_db, $sql);
        $stmt->execute(array('c', 'crawling'));
        $stmt->execute(array('d', 'dead'));
        $stmt->execute(array('f', 'flying'));
        $stmt->execute(array('g', 'gone'));
        $stmt->execute(array('h', 'home'));
    }

Creation of the table reported_by

    private function _createReportedBy()
    {
        $query = 'CREATE TABLE reported_by (id PRIMARY KEY, name NOT NULL UNIQUE)';
        $this->_db->query($query);

        $sql = "INSERT INTO reported_by (id, name) VALUES (?, ?)";
        $stmt = new Zend_Db_Statement_Pdo($this->_db, $sql);
        $stmt->execute(array('a', 'ann'));
        $stmt->execute(array('j', 'john'));
        $stmt->execute(array('l', 'lisa'));
        $stmt->execute(array('p', 'peter'));
        $stmt->execute(array('z', 'zena'));
    }

}

Fetching table rows

class MyDbFetch
{
    private $_db;

Storing the database connection

    public function __construct($db)
    {
        $this->_db = $db;
    }

Fetching the column name

    public function fetchNames($table)
    {
        $select = $this->_db->select()
            ->distinct()
            ->from($table, array('id', 'name'))
            ->order('name');
        $stmt = $this->_db->query($select);

        return $stmt->fetchAll(Zend_Db::FETCH_OBJ);
    }

Fetching one column

    public function fetchColumn($table, $column)
    {
        $select = $this->_db->select()
            ->distinct()
            ->from($table, $column)
            ->order($column);
        $stmt = $this->_db->query($select);

        return $stmt->fetchAll(Zend_Db::FETCH_COLUMN, 0);
    }

}

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 check mark

    public static function printChecked($value)
    {
        empty($value) or print 'checked="checked"';
    }

Displaying the selected option

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

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);
    }

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 prepare to fetch the values of table columns, to display in the form.
• We run the query and we get all the parameters and the result of the query, to display in the form.

// We create the database.
$myDbCreate = new MyDbCreate;
$db = $myDbCreate->process();

// We prepare to fetch the values of table columns, to display in the form.
$myDbFetch = new MyDbFetch($db);

// We run the query and we get all the parameters and the result of the query,
// to display in the form.
$myDbSelect = new MyDbSelect($db);
list($isBug, $isStatus, $isReportedBy, $isCreatedOn,
    $bugId, $statusId, $reportedById, $createdOn, $join,
    $result, $sql) = $myDbSelect->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>Bug</td>
          <td>
            <input type="checkbox" name="is-bug" value="1"
                   <?php MyHtml::printChecked($isBug);?> />
          </td>
          <td>
            <select name="bug">
              <option></option>
              <?php foreach($myDbFetch->fetchNames('bug') as $bug) :?>
                  <option value="<?php echo $bug->id;?>"
                          <?php MyHtml::printSelected($bugId, $bug->id);?>>
                    <?php echo $bug->name;?>
                  </option>
              <?php endforeach;?>
            </select>
          </td>
        </tr>

        <tr>
          <td>Status</td>
          <td>
            <input type="checkbox" name="is-status" value="1"
                   <?php MyHtml::printChecked($isStatus);?> />
          </td>
          <td>
            <select name="status">
              <option></option>
              <?php foreach($myDbFetch->fetchNames('status') as $status) :?>
                  <option value="<?php echo $status->id;?>"
                          <?php MyHtml::printSelected($statusId, $status->id);?>>
                    <?php echo $status->name;?>
                  </option>
              <?php endforeach;?>
            </select>
          </td>
        </tr>

        <tr>
          <td>Reported by</td>
          <td>
            <input type="checkbox" name="is-reported_by" value="1"
                   <?php MyHtml::printChecked($isReportedBy);?> />
          </td>
          <td>
            <select name="reported_by">
              <option></option>
              <?php foreach($myDbFetch->fetchNames('reported_by') as $reportedBy) :?>
                  <option value="<?php echo $reportedBy->id;?>"
                          <?php MyHtml::printSelected($reportedById, $reportedBy->id);?>>
                    <?php echo $reportedBy->name;?>
                  </option>
              <?php endforeach;?>
            </select>
          </td>
        </tr>

        <tr>
          <td>Created on</td>
          <td>
            <input type="checkbox" name="is-created_on" value="1"
                   <?php MyHtml::printChecked($isCreatedOn);?> />
          </td>
          <td>
            <select name="created_on">
              <option></option>
              <?php foreach($myDbFetch->fetchColumn('bug', 'created_on') as $value) :?>
                  <option <?php MyHtml::printSelected($createdOn, $value);?>>
                    <?php echo $value;?>
                  </option>
              <?php endforeach;?>
            </select>
          </td>
        </tr>

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

        <tr>
          <td>Join</td>
          <td></td>
          <td>
            <select name="join">
              <?php foreach($myDbSelect->joins as $value) :?>
                  <option value="<?php echo $value;?>"
                          <?php MyHtml::printSelected($join, $value);?>>
                    <?php echo substr($value, 4) . ' Join';?>
                  </option>
              <?php endforeach;?>
            </select>
          </td>
          <td>All columns (*) are selected by default.</td>
        </tr>

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

      </table>
    </form>

    <hr />
    SQL STATEMENT
    <br /> <br />
    <?php MyHtml::display($sql);?>

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

  </body>
</html>