Using table relationships

In this example, we implement relationships between tables.

Components used in this example

• Zend_Db
• Zend_Db_Adapter_Pdo_Sqlite
• Zend_Db_Statement_Pdo
• Zend_Db_Table_Abstract

---

MyRelationships MyStatus MyReportedBy MyBug MyDbCreate MyDbFetch MyHtml Entry point HTML | Try it | View source code

Implementation of the queries

class MyRelationships
{

Storing the database connection

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

Processing the query

• We get the object identifier and the max number of rows to display, from the GET request.
• We define the default adapter.
• We get the bug details, the bug status and who reported the bug.
• Or we get the status and the bugs with that status.
• Or we get who reported the bug and the bugs reported by that person.
• Or we get all bugs, all statuses, and all people who report the bugs.
• If we catch an exception, we return the error message.

    public function process()
    {
        // We get the object identifier and the max number of rows to display,
        // from the GET request.
        list($bugId, $statusId, $reportedById, $limit) = $this->_getParameters();

        try {
            // We define the default adapter.
            Zend_Db_Table_Abstract::setDefaultAdapter($this->_db);

            if ($bugId) {
                // We get the bug details, the bug status and who reported the bug.
                $result = $this->_getBugParents($bugId, $limit) ;
            } else if ($statusId) {
                // Or we get the status and the bugs with that status.
                $result = $this->_getBugs('MyStatus', $statusId, $limit);
            } else if ($reportedById) {
                // Or we get who reported the bug and the bugs reported by that person.
                $result = $this->_getBugs('MyReportedBy', $reportedById, $limit);
            } else {
                // Or we get all bugs, all statuses, and all people who report the bugs.
                $result = array(
                    'BUG LIST' => $this->_getTable('MyBug', $limit),
                    'STATUS LIST' => $this->_getTable('MyStatus', $limit),
                    'REPORTED-BY LIST' => $this->_getTable('MyReportedBy', $limit),
                );
            }

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

        return array($bugId, $statusId, $reportedById, $limit, $result);
    }

Extraction of the parameters from the GET request

    private function _getParameters()
    {
        $bugId = isset($_GET['bug'])? $_GET['bug'] : null;
        $statusId = isset($_GET['status'])? $_GET['status'] : null;
        $reportedById = isset($_GET['reported_by'])? $_GET['reported_by'] : null;
        $limit = isset($_GET['limit'])? $_GET['limit'] : null;

        return array($bugId, $statusId, $reportedById, $limit);
    }

Getting a bug with its status and who reported the bug

• We find the bug.
• We find the status.
• We find who reported the bug.

    private function _getBugParents($bugId, $limit)
    {
        // We find the bug.
        $bugTable = new MyBug();
        $bug = $bugTable
            ->find($bugId)
            ->current();

        if ($bug) {
            // We find the status.
            $status = $bug->findParentRow('MyStatus') and
            $status = $status->toArray();

            // We find who reported the bug.
            $reportedBy = $bug->findParentRow('MyReportedBy') and
            $reportedBy = $reportedBy->toArray();

            $result = array(
                'BUG' => $bug->toArray(),
                'STATUS' => $status,
                'REPORTED BY' => $reportedBy,
            );
        } else {
            $result = array();
        }

        return $result;
    }

Getting the list of bugs

• We find the parent
• We find the bugs of that parent.

    private function _getBugs($class, $id, $limit)
    {
        // We find the parent
        $table = new $class;
        $parent = $table
            ->find($id)
            ->current();

        if ($parent) {
            $select = $table->select()
                ->order('name ASC')
                ->limit($limit);

            // We find the bugs of that parent.
            $rows = $parent
                ->findDependentRowset('MyBug', $class, $select)
                ->toArray();
        } else {
            $rows = array();
        }

        return $rows;
    }

Getting the rows of a table

    private function _getTable($class, $limit)
    {
        $table = new $class;

        $select = $table->select()
            ->order('name ASC')
            ->limit($limit);

        return $table->fetchAll($select)->toArray();
    }

}

Implementation of the table status

class MyStatus extends Zend_Db_Table_Abstract
{
    protected $_name = 'status';
    protected $_dependentTables = array('Bug');

}

Implementation of the table reported_by

class MyReportedBy extends Zend_Db_Table_Abstract
{
    protected $_name = 'reported_by';
    protected $_dependentTables = array('Bug');

}

Implementation of the table bug

class MyBug extends Zend_Db_Table_Abstract
{
    protected $_name = 'bug';
    protected $_referenceMap = array(
        'MyStatus' => array(
            'columns' => 'status_id',
            'refTableClass' => 'MyStatus',
            'refColumns' => 'id'
        ),
        'MyReportedBy' => array(
            'columns' => 'reported_by_id',
            'refTableClass' => 'MyReportedBy',
            'refColumns' => 'id'
        ),
    );

}

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->_createBug();
        $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 _createBug()
    {
        // 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', 'd', '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 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.
$dbFetch = new MyDbFetch($db);

// We run the query and we get all the parameters and the result of the query,
// to display in the form.
$relationships = new MyRelationships($db);
list($bugId, $statusId, $reportedById, $limit, $result) = $relationships->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 name="form">
      <table>

        <tr>
          <td></td>
          <td>Bug</td>
          <td>
            <select name="bug"
                    onchange="form.status.selectedIndex=0;form.reported_by.selectedIndex=0;">
              <option></option>
              <?php foreach($dbFetch->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>
          <td>Select to display the corresponding status and reported by (parents).</td>
        </tr>

        <tr>
          <td>OR</td>
          <td>Status</td>
          <td>
            <select name="status"
                    onchange="form.bug.selectedIndex=0;form.reported_by.selectedIndex=0;">
              <option></option>
              <?php foreach($dbFetch->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>
          <td>Select to display the corresponding bugs (dependents).</td>
        </tr>

        <tr>
          <td>OR</td>
          <td>Reported by</td>
          <td>
            <select name="reported_by"
                    onchange="form.status.selectedIndex=0;form.bug.selectedIndex=0;">
              <option></option>
              <?php foreach($dbFetch->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>
          <td>Select to display the corresponding bugs (dependents).</td>
        </tr>

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

        <tr>
          <td>AND</td>
          <td>Limit</td>
          <td>
            <select name="limit">
              <option></option>
              <option <?php MyHtml::printSelected($limit, 1);?>>1</option>
              <option <?php MyHtml::printSelected($limit, 2);?>>2</option>
              <option <?php MyHtml::printSelected($limit, 3);?>>3</option>
              <option <?php MyHtml::printSelected($limit, 4);?>>4</option>
              <option <?php MyHtml::printSelected($limit, 5);?>>5</option>
              <option <?php MyHtml::printSelected($limit, 6);?>>6</option>
              <option <?php MyHtml::printSelected($limit, 7);?>>7</option>
              <option <?php MyHtml::printSelected($limit, 8);?>>8</option>
              <option <?php MyHtml::printSelected($limit, 9);?>>9</option>
              <option <?php MyHtml::printSelected($limit, 10);?>>10</option>
            </select>
          </td>
          <td>Select to limit the number of rows to display within a bugs, statuses or reported by's.</td>
        </tr>

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

        <tr>
          <td></td>
          <td></td>
          <td>
            <input type="submit" value="Submit" />
          </td>
          <td>Bugs, statuses and reported by's are displayed if no selection is made.</td>
        </tr>

      </table>
    </form>

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

  </body>
</html>