Rev 1 | Rev 37 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
<?php
/*
This class simply extends DBQuery to allow output to be converted to HTML
*/
require_once( 'DBQueryHTML.class.php' );
class DBQueryHTML extends DBQuery {
/*
Function takes an SQL statement and converts it to an HTML table.
Return Value: HTML table representation of the query
Parameters:
$sql A valid SQL query to run
$format An optional array of format strings (suitable for printf) for each column (empty strings ignored)
$makeTableDef If True, the resulting HTML has the <table></table> tags; otherwise starts with header
$append Arbitrary string that is appended as a single column to each row
NOTE ON $append
$append may optionally contain variables of the form %colname%, which will be replaced
with values from the current row. $colname is taken from the META data from the query, thus
the query select foo from bar would have a meta of header of foo for column 0.
$append is searched for strings of form %foo% in that case, and the current value of column
foo replaces the tag %foo%.
%foo% is ignored if foo is not a valid column name in a query.
thus, a query such as select zip,city,state from zip_codes and a string of Hello %city% I'm glad
you are in %State% will not replace the second as State is not a column of this query (it is case
sensitive). Also, beware of items like select zip,concat(state,', ', city) from zip_codes. It is
much better to rewrite that as select zip,concat(state,', ', city) mytown from zip_codes.
If you don't know what all that means, get a book on SQL
*/
function queryToTable ( $format = '', $makeTableDef = true, $append='', $headers = '' ) {
print "\n\n<pre>$this->query</pre>\n\n";
$tdTextDefinition = '<td valign=' . HTML_QUOTE_CHAR . 'top' . HTML_QUOTE_CHAR . '>';
$tdNumberDefinition = '<td valign=' . HTML_QUOTE_CHAR .'top' . HTML_QUOTE_CHAR . 'align=' . HTML_QUOTE_CHAR . 'right' . HTML_QUOTE_CHAR . '>';
$html = '';
$rows = array();
$save = $this->useAssociativeArray;
$this->useAssociativeArray = false; // temporarily turn off associative arrays
$this->run();
if ( $this->rowsAffected ) { // we returned at least one row
// Build the searchFor array for $append
$searchFor = array();
foreach ( $this->columnMetaData as $field ) {
$searchFor[] = '%' . $field['name'] . '%';
}
/* special condition where only one row is returned. In that case, $this->returnData is not an array
of associations, but a simple association. In this case, we need to convert it
*/
if (count($this->returnData[0]) == 1) { // convert from association to single row array of associations
$temp = array();
foreach ($this->returnData as $column => $value) {
$temp[0][$column] = $value;
} // foreach
$this->returnData = $temp;
}
if (count($format) > 0 ) { // we have some formats, so let's do it the hard, slow way
for ( $row = 0; $row < count($this->returnData); $row++) {
$rows[$row] = '';
for ( $column = 0; $column < count($this->returnData[$row]); $column++ ) {
$rows[$row] .= strlen($format[$column]) && isset($this->returnData[$row][$column])> 0 ?
( $tdNumberDefinition . sprintf($format[$column],$this->returnData[$row][$column]) . '</td>')
: ($tdTextDefinition . $this->returnData[$row][$column] . '</td>');
} // for $column
/*
let's append some stuff to the row if it exists.
We will take the array of SearchFor containing the column names
and the array of values from this row, then look for matches and replace them
with the correct values. Note, the column names in $append should have percent
signs appended and prepended, thus a column name of joe would be %joe% in %append
*/
if (strlen($append) > 0) { // let's append some stuff to the row
$rows[$row] .= $tdTextDefinition . str_replace ( $searchFor, $this->returnData[$row], $append ) . '</td>';
}
} // for $row
} else { // no formatting, so we just slam the stuff together
for ( $row = 0; $row < count($this->returnData); $row++) {
$currentValue = $tdTextDefinition . implode( '</td>' . $tdTextDefinition, $this->returnData[$row]) . '</td>';
if (strlen($append) > 0) { // see explaination in if part of this structure
$currentValue .= $tdTextDefinition . str_replace ( $searchFor, $this->returnData[$row], $append ) . '</td>';
}
$rows[] = $currentValue;
}
}
// ok, let's get the field headers from the table
$html .= '<tr>';
if ( $headers ) { // user has passed in some header fields for us to use
$html .= '<th>' . implode( '</th><th>', $headers ) . '</th>';
} else { // no headers, so we just get them from the returned column names
foreach ( $this->columnMetaData as $field ) {
$html .= '<th>' . $field['name'] . '</th>';
}
} // if headers .. else
$html .= '</tr>';
// we now have all the info, so let's make it into a table
$html .= '<tr>' . implode('</tr><tr>', $rows ) . '</tr>';
} else {
$html = '<tr><td>No rows returned by the query</td></tr>';
}
if ( $makeTableDef ) { // they want the <table> def, so let's give it to them
$html = '<table border=' . HTML_QUOTE_CHAR . '1' . HTML_QUOTE_CHAR . '>' . $html . '</table>';
} // if ( $makeTableDef
$this->useAssociativeArray = $save;
//return '<pre>' . print_r ($this,true) . '</pre>';
return $html;
}
/*
function executes a query, then returns an array containing only the values of the first
field in the return as the keys to an associative array. NOTE: any duplicates will be
discarded. This is useful when you simply want to know if a value exists, ie if you
are building an html select from a query, and a separate query needs to hold the
values that are currently selected
*/
function valuesToKeys () {
$returnValue = array();
$save = $this->useAssociativeArray;
$useAssociativeArray = false; // temporarily turn off associative arrays
$this->run();
if ( $this->rowsAffected ) { // we returned at least one row
foreach ( $info as $key => $value ) { // only thing I know to do is iterate
$returnValue[$this->returnData[0]] = true;
}
} // if
$useAssociativeArray = $save;
return $returnValue;
}
/* this function will take the result of an SQL query that returns at least two columns.
The first column becomes the keys in an associative array, and the second column
becomes the value.
Note, because it is an associative array, duplicates of the first column will only contain
the last value.
*/
function ColumnColumnArray () {
$returnValue = array();
$save = $this->useAssociativeArray;
$useAssociativeArray = false; // temporarily turn off associative arrays
$this->run();
if ( $this->rowsAffected ) { // we returned at least one row
foreach ( $info as $key => $value ) { // only thing I know to do is iterate
$returnValue[$this->returnData[0]] = $this->returnData[1];
}
} // if
$useAssociativeArray = $save;
return $returnValue;
}
/* the first returned column becomes the value, the second becomes the display element
if $selectedFieldsQuery is begins with 'select', items matching that query will be selected
if $selectedFieldsQuery is any other value, it is assumed to be the value of the first column
*/
function htmlSelect ( $selectedFieldsQuery = '' ) {
$html = '';
$selectedFields = array();
if (strlen( $selectedFieldsQuery )) {
if ( preg_match ( '/^\s*select/i', $selectedFieldsQuery ) ) { // They passed in a query
if (strlen($selectedFieldsQuery) ) {
$subSet = new DBQuery($selectedFieldsQuery);
$subSet->run();
$selectedFields = $subSet->sqlValuesToKeys();
}
} else { // assume the passed in a value
$selectedFields[$selectedFieldsQuery] = 1;
}
}
$save = $this->useAssociativeArray;
$useAssociativeArray = false; // temporarily turn off associative arrays
$this->run();
if ( $this->rowsAffected ) { // we returned at least one row
foreach ($this->returnData as $rowNumber => $value ) {
$html .= '<option value="' . $value[0] . '"';
if ( $selectedFields[$value[0]] ) {
$html .= ' selected';
}
$html .= '>' . $value[1] . '</option>';
}
}
$useAssociativeArray = $save;
return $html;
}
/* function will take a query and turn it into a series of check boxes. It must contain
two columns, with the first becoming the the name of the checkbox
and the second becoming the displayed value. an optional third column will be used
to match if the $checkedValuesQuery is used.
if $checkedValuesQuery is not empty, it will be run to see what boxes need to be
checked by being compared to the third column of the $sql query.
$htmlBefore will be placed before each check box, and $htmlAfter will be placed after
each checkbox.
if $tableColumns is set to a number, the checkboxes will be embedded in a group of
<tr></tr>, each containing table columns of $tableColumns width. In this case, $htmlBefore
will have <td> prepended and $htmlAfter will have </td> appended, meaning any passed
values will be INSIDE of the td. NOTE: the <table></table> tags are NOT put in.
NOTE: currently, using the table stuff will leave a dangling row with 0 elements if
the number of elements equal the number of columns.
*/
function htmlCheckBoxes ( $checkedValuesQuery = '', $htmlBefore = '', $htmlAfter = '', $table_columns='' ) {
$html = '';
if ($table_columns) {
$htmlBefore = '<td>' . $htmlBefore;
$htmlAfter .= '</td>';
$html .= '<tr>';
}
$numColumns = 0;
$checkBoxes = queryDatabaseExtended( $sql,0 );
$selectedFields = array();
if (strlen($checkedValuesQuery) ) {
$subSet = new DBQuery($selectedFieldsQuery);
$subSet->run();
$selectedFields = $subSet->sqlValuesToKeys();
}
$save = $this->useAssociativeArray;
$useAssociativeArray = false; // temporarily turn off associative arrays
foreach ($this->returnData as $row => $values) {
if ($table_columns && ++$numColumns == $table_columns) {
$html .= '</tr><tr>';
$numColumns = 0;
}
//objectDebugScreen($row);
$html .= $htmlBefore . '<input type="checkbox" name="' . $values[0] . '"';
if ( $selectedFields[$values[2]] ) {
$html .= ' checked';
}
$html .= '>' . $values[1] . $htmlAfter;
//<INPUT type="checkbox" checked name="temp">
} // foreach
$html .= '</tr>';
$useAssociativeArray = $save;
return $html;
}
/*
function CSV ( $sql ) {
$rows = array();
$CSV = '';
$info = array();
if ( $result = queryDatabaseExtended($sql,0) ) {
$meta = $result['meta'];
//objectDebugScreen($result);
$info = $result['data'];
unset ($result);
$headers = array();
foreach ( $meta as $field ) {
$headers[] = $field['name'];
} // foreach
$rows[] = implode("\t", $headers);
// special condition where only one row is returned. In that case, $info is not an array
// of associations, but a simple association. In this case, we need to convert it
if (count($info[0]) == 1) { // convert from association to single row array of associations
$temp = array();
foreach ($info as $column => $value) {
$temp[0][$column] = $value;
} // foreach
$info = $temp;
}
for ( $row = 0; $row < count($info); $row++) {
$rows[] = implode("\t", $info[$row]);
}
// we now have all the info, so let's make it into a table
$CSV .= implode("\n", $rows );
} // if ( $info = queryDatabase($sql,0) )
// print '<pre>'; print_r ($info); print '</pre>';
return $CSV;
}
*/
} // class DBQueryHTML
?>