I created a separate class/component that takes a CDbDataReader object from a CCommand.query method and converts it into a table. I did not want to use the traditional approach of using ActiveRecord for the sole reason that it uses too much memory and too much time. I also wanted the output to be changeable from the outside. So I added events that provides the handler with many properties.
At first, all of the table output was immediately streamed using echo statements, so nothing was stored in memory. But then, I realized the value of caching and of providing the accumulated HTML to the event handlers, so I changed the echo statements to string appending syntax. I then changed it again to give it the option of either streaming the HTML using echo or allowing it to be accumulated into one HTML string.
But I am not happy with what I have so far. Being a lone programmer, I only have myself to think with and brainstorm ideas. I only learn from myself. So I am reaching out to you all to help me out. I want to make this my opportunity to give back, for Yii and all of the wonderful extensions. So before officially publishing this, can you review it and give me feedback.
Thanks.
<?php
// TODO: columns, labels, and matchup are a mess. Too many points of entry.
// TODO: Add ability to add columns
/**
* Convert2table class
* @property-write callable $onOutputColgroup This event is executed before the return of Colgroup's HTML Content
* @property-write callable $onBeforeTableCell Event executed before adding new table cell in outputRow
* @property-write callable $onAfterTableCell Event executed after adding new table cell in outputRow
* @property-write callable $onRowOutput Event executed before appending the row output
* @property-write callable $onBeforeOutputRow Event executed before row is ouputted
* @property-write callable $onAfterOutputRow Event executed after row is ouputted
* @property-write callable $onBeforeNextResult Event executed before data cursor goes to the next row
* @property-write callable $onAfterNextResult Event executed after data cursor goes to the next row
*/
class Convert2table extends CComponent {
protected $table;
// ---- Required values ---- //
protected $columns=null;
protected $column_titles=null;
protected $query;
// ---- Table options ---- //
protected $tableprefix=true;
public $rowClass;
protected $matchup;
protected $_callableColumnTitles;
public $stream=false;
public function __construct($query,$columns=null,$column_titles=null){
if(get_class($query)=='CDbDataReader'){
$this->query = $query;
}
$this->setColumns($columns);
$this->setColumnlabels($column_titles);
$this->_callableColumnTitles=function ($t) {return ucwords(strtolower(str_replace('_',' ',$t)));};
if( $query /*is associative array*/);
}
public function __toString(){
return $this->getTable();
}
// -- Getter and Setters -- //
/**
* Getter method to run the query with the options and get HTML table
* @return string HTML string of the table query
*/
public function getTable(){
if($this->table) return $this->table;
return $this->table=$this->outputTable($this->query,$this->columns,$this->column_titles);
}
/**
* Optional method to specify which columns to print. If columns are not
* specified, then all available columns in the query will print.
* @param array $value Array of SQL columns that are going to be outputted
*/
public function setColumns($value){
if(is_array($value)) $this->columns = $value;
}
/**
* Specify the column labels/titles/headings that will be used in the header
* @param mixed $value Input value can be either an array of titles or a
* callable function that will transform each column name to a label.
*/
public function setColumnlabels($value){
if(is_callable($value)){$this->_callableColumnTitles=$value;}
elseif(is_array($value)) {$this->column_titles=$value;}
}
/**
* Labels for every column is stored in this property as an associative array.
* If columns are not specified already, then keys in the matchup are used.
* @param array $value Associative array of column => heading.
*/
public function setLabelmatchup($value){
if(is_array($value)) $this->matchup=$value;
if(!$this->columns) $this->columns=array_keys($value);
// if(!$this->column_titles) $this->column_titles=array_values($value);
}
/**
* Option to specify whether HTML string should be closed in a table tag.
* @param boolean $value True/False
*/
public function setPrefixtable($value){
$this->tableprefix = (boolean) $value;
}
// -- EVENT Callers -- //
/**
* This event is executed before the return of Colgroup's HTML Content
* @param string $html REF HTML string of Colgroup
*/
protected function onOutputColgroup($event) {$this->raiseEvent(__FUNCTION__,$event);}
/**
* Event executed before adding new table cell in outputRow
* @param string $html REF HTML Text thus far of the row
* @param array $data REF Row in form of an associative array
* @param string $column REF Column of current cell
* @param string $celldata REF Value of Current Cell
* @param string $subClass REF Cell's CSS class value
* @TODO: try adding the current row index
*/
protected function onBeforeTableCell($event) {$this->raiseEvent(__FUNCTION__,$event);}
/**
* Event executed after adding new table cell in outputRow
* @param string $html REF HTML Text thus far of the row
* @param array $data REF Row in form of an associative array
* @param string $column REF Column of current cell
* @param string $celldata REF Value of Current Cell
* @param string $subClass REF Cell's CSS class value
* @TODO: try adding the current row index
*/
protected function onAfterTableCell($event) {$this->raiseEvent(__FUNCTION__,$event);}
/**
* Event executed before appending the row output
* @param string $row REF HTML of the row to be returned
* @TODO: Try adding the current row index
*/
protected function onRowOutput($event) {$this->raiseEvent(__FUNCTION__,$event);}
/**
* Event executed before row is ouputted
* @param int $RowNumber the current index of the row
* @param string $html REF HTML thus far before appending new row
* @param array $matchup REF Associatve array of columns' labels/titles
* @param array $columns The columns whose values will be outputted in the next row
* @param array $row Row data to be used in the output of the row
*/
protected function onBeforeOutputRow($event) {$this->raiseEvent(__FUNCTION__,$event);}
/**
* Event executed after row is ouputted
* @param int $RowNumber The current index of the row
* @param string $html REF the HTML thus far accumulated after the new row's output
* @param array $matchup REF Associatve array of columns' labels/titles
* @param array $columns The columns whose values were outputted in the previous row
* @param array $row Row data to be used in the output of the row
*/
protected function onAfterOutputRow($event) {$this->raiseEvent(__FUNCTION__,$event);}
/**
* Event executed before data cursor goes to the next row
* @param int $RowNumber Current row index
* @param array $matchup REF Associative array of columns' labels/titles/
* @param array $columns REF Columns that will be in the output
* @param array $row REF Row data in the form of an associative array
* @param string $html REF HTML thus far
*/
protected function onBeforeNextResult($event) {$this->raiseEvent(__FUNCTION__,$event);}
/**
* Event executed after data cursor goes to the next row
* @param int $RowNumber Previous row index
* @param array $matchup REF Associative array of columns' labels/titles/
* @param array $columns REF Columns that will be in the output
* @param array $row REF Row data in the form of an associative array
* @param string $html REF HTML thus far
*/
protected function onAfterNextResult($event) {$this->raiseEvent(__FUNCTION__,$event);}
/**
* Outputs header for method groupedTable
*/
public function outputColgroup($columns) { // echo colgroup and column heading
$a='';
$a.= "<colgroup>\n";
for($t=reset($columns);$t;$t=next($columns)){ // echo col for each column
$a.="\t<col class='$t'>\n";
}
$a.= "</colgroup>\n";
$this->onOutputColgroup(new CEvent($this, array(
'colgroup'=>&$a,
'html'=>&$a)));
return $a;
}
/**
* Outputs a data set using data for method outputTable
*/
public function outputRow($tag,$subtag,array $data,array $keys = null, $rowID=null, $rowClass=null, $subClass=null){
if(!$keys) $keys = array_keys($data);
if($subClass && is_string($subClass)) {
$b=$subClass;
unset($subClass);
foreach($keys as $key=>$value){
$subClass[$value]="$b $value";
}
unset($<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='B)' />;
}
if(!$rowClass && $this->rowClass)$rowClass=$this->rowClass;
$a= '';
for($key=reset($keys);$key;$key = next($keys)){
$eventVariables = new CEvent($this,
array('html'=>&$a,
'column'=>&$key,
'data'=>&$data,
'celldata'=>&$data[$key],
'subClass'=>&$subClass[$key]));
$this->onBeforeTableCell($eventVariables);
$a.= "\t<$subtag";
$a.= ($subClass) ? ("")<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/sad.gif' class='bbc_emoticon' alt=':(' />" class = '$subClass[$key]'");
$a.= ">" . $data[$key] . "</$subtag>\n";
$this->onAfterTableCell($eventVariables);
}
$a = "<$tag"
. (($rowID) ? (" id='$rowID'") : (""))
. (($rowClass) ? (" class ='$rowClass'") : (""))
. ">\n" . $a . "</$tag>\n";
$this->onRowOutput(new CEvent($this, array('row'=>&$a)));
return $a;
}
/**
*
*/
public function outputTable(CDbDataReader $query, array $columns=null, array $column_titles=null){
if(!$columns) $columns=array_keys($rowResult=$query->read()); // create column filter
if(!$rowResult) $rowResult=$query->read(); // call up first result
if(!$rowResult) return;
if(!$column_titles) $column_titles = array_map($this->_callableColumnTitles,$columns);
// pair column titles with corresponding columns
if($this->matchup){$matchup=$this->matchup;}
elseif(count($columns)==count($column_titles)){$matchup = array_combine($columns,$column_titles);}
else{reset($columns);reset($column_titles);
do{$matchup[current($columns)]=current($column_titles);}
while(next($columns) && next($column_titles));}
static $i=0;
// ------------------- START of Table ----------------------- //
$this->_echo('');
if($this->tableprefix) $this->_echo("<table>\n");
if($i==0) $this->_echo($this->outputColgroup($columns,$matchup));
if($i==0) $this->_echo($this->outputRow('thead','th',$matchup,$columns));
do {
$i++;
// output row
$eventVariables=new CEvent($this,
array('RowNumber'=>$i,
'content'=>$this->_echo(),
'html'=>$this->_echo(),
'row'=>&$rowResult,
'matchup'=>&$matchup,
'columns'=>$columns));
$this->onBeforeOutputRow($eventVariables);
$this->_echo($this->outputRow('tr','td',$rowResult,$columns,"row$i"));
$this->onAfterOutputRow($eventVariables);
// next row
$eventVariables=new CEvent($this,array(
'RowNumber'=>$i,
'matchup'=>&$matchup,
'columns'=>$columns,
'row'=>&$rowResult,
'html'=>$this->_echo(),
'content'=>$this->_echo()));
$this->onBeforeNextResult($eventVariables);
$rowResult=$query->read();
$this->onAfterNextResult($eventVariables);
unset($eventVariables);
} while($rowResult); // check if next row is void
if($this->tableprefix) $a=$this->_echo("</table>\n");
// ------------------- END of Table ----------------------- //
return $a;
}
public function &_echo($text=null){
if(!$this->stream){
if($text && is_string($text)) {
static $a='';
$a.=$text;
}
return $a;
} else {
echo $text;
return;
}
}
}