Tuesday, December 8, 2009

Select table rows


In this example, we select table rows.

Components used in this example
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);
    }

}

No comments:

Post a Comment