Rev 37 | Rev 41 | 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( 'DBQuery.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;
}
/**
* Builds an HTML select out of a query, or data
*
* $parameters may contain the following keys
*
* Must have one of the following. If both are present, 'query' is
* ignored
* 'query' - An SQL query to run. Should return 2 columns; first is value, second id display
* 'data' - An array of key/value pair. Key is used for value, value is display
*
* 'name' - HTML name for the select. If this is not set, only the options will be returned
* 'class' - used for class='whatever' in select
* 'selected' - key in 'data' or result of 'query' which will be selected
* 'label' - used as <label>. If this is not set, no <label> is generated
* 'nullok' - adds a entry at top with value of -1 and display of three dashes (---)
*
*
* @parameters string $parameters an array of data to decide what/how to process
* @returns string an HTML select entity
*/
function htmlSelect ( $parameters ) {
//print "<pre>" . print_r( $parameters, true ) . "</pre>"; die;
if ( empty( $parameters['data'] ) ) {
if ( empty( $paramters['query'] ) )
return '';
$save = $this->useAssociativeArray;
$useAssociativeArray = false; // temporarily turn off associative arrays
$this->query( $parameters['query'] );
if ( $this->rowsAffected ) { // we returned at least one row
foreach ($this->returnData as $rowNumber => $value ) {
$parameters['data'][$value[0]] = $value[1];
}
}
}
$selectedKey = isset( $parameters['selected'] ) ? $parameters['selected'] : '';
$options = array();
if ( isset( $parameters['nullok'] ) ) {
$options[] = '<option value="-1">---</option>';
}
foreach ( $parameters['data'] as $key => $value ) {
$options[] = sprintf(
'<option value="%s"%s>%s</option>',
$key,
$key == $selectedKey ? ' selected' : '',
$value
);
} // foreach
$return = implode( "\n", $options );
if ( isset( $parameters['name'] ) ) {
$return = "<select name='$parameters[name]'>\n$return\n</select>";
if ( isset( $parameters['label'] ) ) {
$return = "<label>$parameters[label]\n$return\n</label>\n";
}
}
return $return;
}
/**
* Create an HTML checkbox block from a query, or array of data passed in
*
* if $parameters['data'] is set, no further query is used. $parameters['data']
* should contain an array of data. Each row in that array should be another
* array with keys
* 'id' -- 'id' and 'name' of the checkbox
* 'name' -- display name of the checkbox
* 'checked' -- boolean (or 0/1) saying whether this has a check
*
* of $parameters['data'] is not set and $parameters['query'] is set
* the query will be run. It MUST contain the column names listed above.
*
* If $parameters['template'] is set, that template will be used. If it
* is not set, a template will be defaulted to.
*
* If $paramters['arrayName'] is set, the form of the template will be
* name='arrayname[name]'
*
* Processing will then continue
*/
function htmlCheckBoxes ( $parameters ) {
if ( empty( $parameters['template'] ) ) { // they did not send us a template
if ( empty( $parameters['arrayName'] ) ) {
$parameters['template'] = "<input type='checkbox' id='~~id~~' name='~~id~~' ~~checked~~>\n<label for='~~id~~'>~~name~~</label>\n";
} else {
$parameters['template'] = "<input type='checkbox' id='$parameters[arrayName]~~id~~' name='$parameters[arrayName][~~id~~]' ~~checked~~>\n<label for='~~id~~'>~~name~~</label>\n";
}
}
//print "<pre>" . print_r($parameters, true) . "</pre>"; die;
if ( empty( $parameters['data'] ) ) {
if ( empty( $parameters['query'] ) )
return '';
$save = $this->useAssociativeArray;
$useAssociativeArray = false; // temporarily turn off associative arrays
$this->doSQL( $parameters['query'] );
if ( $this->rowsAffected ) { // we returned at least one row
foreach ($this->returnData as $rowNumber => $value ) {
$parameters['data'][] = $value;
}
}
}
//print "<pre>" . print_r($parameters, true) . "</pre>"; die;
foreach ( $parameters['data'] as $key => $value ) {
$replacement = array(
'id' => $value['id'],
'name' => $value['name'],
'checked' => $value['checked'] ? 'checked' : ''
);
$html[] = $this->templateReplace($parameters['template'],$replacement);
}
return implode( '', $html );
}
/**
* Replaces instances of replacement strings in string
*
* Designed to allow the caller to build a string with things to be
* replaced, for example
* <input type='text' name='~~name~~'>
* All instances of ~~name~~ will be replaced
*/
public function templateReplace ( $template, $replacmentStrings, $delimiter = '~~' ) {
//print "<pre>" . print_r($replacmentStrings, true) . "</pre>";
//print "<pre>" . print_r($template, true) . "</pre>";
foreach ( $replacmentStrings as $key => $replace ) {
$search = '/' . $delimiter . $key . $delimiter . '/';
//print "<pre>" . print_r($search, true) . "</pre>"; die;
$template = preg_replace( $search, $replace, $template );
}
return $template;
}
/*
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
?>