In the previous post I showed how ITL can be used to generate HTML tables from MySQL selects. Now I have cleaned the prototype and added the ability to customize the html table rows. The intention with generating HTML tables in ITL is not to give the ability to customize all aspects of an HTML table, but to easily create decent tables. This is not easy to do since it is complex to format HTML. The present ITL HTML generating capabilities are primarily for adding HTML tables to mails, still it is complex to format HTML so it looks nice. Therefore I decided to do the formatting with PHP code, this is not the simplest way to add HTML formatting, but it gives the flexibility I find necessary. I added the ability to modify the row tag <tr> by <htmlrow>php code </htmlrow>, the SQL result table is exposed to this code row by row as the cells are exposed to <htmlcell>php code</htmlcell>.
I expose the entire SQL table to the htmlrow function and an index pointing out the current row in the table. In the htmlcell function I expose the entire table row with an index pointing at the current cell. This gives freedom to format depending on values of other rows and cells.
If we look at the new version of the job ‘noCellColors’:
You see I added an <hmlrow> tag to get a (row-) striped table, and a maroon row if the first cell’s value is ‘en’, like this:
The second job (from the post) ‘cellColor’ now looks like this:
Here I added some ‘cell logic’ formatting background colors depending on the value of the second cell in each row, giving this html table:
Maybe not so pretty, but it illustrates the ability to format depending on values.
I use the eval function to import the PHP code from the job XML script. I think it’s a bit clumsy and suspect there is better ways to do it, but I could not find a better way. What I found was this „If eval() is the answer, you're almost certainly asking the wrong question.“ Rasmus Lerdorf.
Maybe PHP is the wrong language, if eval() is the answer.
I would have better use of practical advice than a quibbling quote, but I didn’t find any so I use eval(). Anyway for the moment I’m happy with the HTML formatting so far, but I suspect this is not the final code.
I end this post with the sql converter code creating these amazing tables.
<?php
/**
* SQL result converter - dynamically included in function {@link execSql()}
*
* This code converts a mysqli select result into a HTML table.
* For documentation see {@link sqlconverter_default.php}
*
*
* <sqlconverter name='sqlconverter_HTML03.php' target='table' totline='yes'>
* <htmlrow><![CDATA[
* var_dump($cnt);
* if($rno % 2 == 0) $tag = '<tr class="ett">';
* else $tag = '<tr style="background: maroon; color: white;">';
* if ($tbl[$rno]['0'] == 'en') $tag = '<tr style="background: maroon; color: white;">';
* ]]></htmlrow>
* <htmlcell><![CDATA[
* if ($cno == 1){
* if ($row[$cno] < 2) $tag = "<td style="background: red; color: black;border:1px solid #D5D5D5; padding:15px;">";
* else if ($row[$cno] < 10) $tag = '<td style="background: blue; color: white;">';
* else $tag = "<td class='green'>";
* }
* ]]></htmlcell>
* </sqlconverter>
*
* The parms:
* 1 totline=yes|no determines if a total line is inserted at the end
* 2 emptytable=yes|no determines if the an empty table is written to disk or not
* 3 width=width of table
*
* htmlrow($tbl,$rno,$meta)
* $tbl array, $rno=row number, $meta array
* return $trtag
*
* htmlcell($row,$cno,$meta)
* $row array, $cno=position in row (0=1st cell), $meta array
* return $tdtag
*
* @author Lasse Johansson <lars.a.johansson@se.atlascopco.com>
* @version 1.0.0
* @package adac
* @subpackage sqlconverter
* @param string $sqltarget filename where we should save the file.
* @param object $result mysqli SELECT result set
*/
$mysqltarget = $sqltarget;
$sqltarget = $temparr = 'html0';
/** Include the default converter to do a basic conversion of the result table */
include("$sqlconverterDir".'sqlconverter_fetch_row.php');
$sqltarget = $mysqltarget;
if(is_numeric(substr($sqltarget, -1,1))) {
$sqltarget = "$sqltarget";
} else {
clearstatcache();
for ($x=0; 1==1; $x++){
if (!file_exists("$sqltarget$x")){
$sqltarget = "$sqltarget$x";
break;
}
}
}
$sqlarray = unserialize(file_get_contents("$temparr"));
$arrMeta = unserialize(file_get_contents("$temparr".'meta_.TXTArray'));
$filePfx = '.ARRAY';
$metafile = $sqltarget.'meta_';
if(is_numeric(substr($sqltarget, -1,1))) {
$metafile = "$metafile$filePfx";
$sqltarget = "$sqltarget";
} else {
clearstatcache();
for ($x=0; 1==1; $x++){
if (!file_exists("$metafile$x$filePfx")){
$metafile = "$metafile$x$filePfx";
$sqltarget = "$sqltarget$x";
break;
}
}
}
$sqllog->logit('Enter',"sqlconverter writing output to $metafile and $sqltarget");
if (array_key_exists('table',$xmlconverter)) {
$xmltable = $xmlconverter['table'][0];
if (array_key_exists('width',$xmltable))
$xmltablewidth = is_string($xmltable['width']) ? $xmltable['width'] : $xmltable['width'][0]['value'];
}
$rf =
'$rowfunc = function($tbl,$rno,$meta) {$tag = "<tr>";'.$xmlconverter['htmlrow'][0]['value'].'return "$tag";};';
eval($rf);
$cf =
'$cellfunc = function($row,$cno,$meta) {$tag = "<td>";'.$xmlconverter['htmlcell'][0]['value'].'return "$tag{$row[$cno]}</td>";};';
eval($cf);
if (isset($xmltablewidth)) $Table.= "<table id='mysql' width=$xmltablewidth>";
else $Table.= "<table id='mysql' style='table-layout: fixed; width: 100%;'>";
//Header Row with Field Names
$NumFields = $result->field_count;
$Table.= "<tr>";
foreach($arrMeta as $field) {
if ( $field->type == 253) $Table.= "<th align='right'>".$field->name."</th>";
else $Table.= "<th>" . $field->name . "</th>";
}
$Table.= "</tr>";
foreach($sqlarray as $rno => $Row){
$Table.= $rowfunc($sqlarray,$rno,$arrMeta);
foreach($Row as $key => $value){
$Table.= $cellfunc($Row,$key,$arrMeta);
}
$Table.= "</tr>";
}
if ($xmlconverter['totline'] != 'no'){
$Table.= "<tr class='tot'><td colspan='$NumFields'>Query returned " . $result->num_rows . " rows</td></tr>";
}
$Table.= "</table>";
if (!($xmlconverter['zerorows'] == 'no' and $RowCt == 0)){
file_put_contents("$sqltarget",$Table);
}
unset($arrmeta,$Table,$rowfunc,$cellfunc,$rf,$cf);
$sqllog->logit('Note',"Exit sqlconverter");
|
No comments:
Post a Comment