PHP/Mysql Sql results to table..... 1

[willyd61]

Hello, and thanks ahead of time for any help on this.

I wrote a include file that handles the sql results from any db and returns html table to the screen. My problem is speed, and wondering if anyone could take a look at why this may render slowly.... Seems if the results are less then 1000 then its pretty quick, but when its more then that it pretty slow.... Any suggestions?? Here is the code: The array is being sent from fetch_Array from the core program.

<?php
function mytable($sql,$colmod){
$first_row = TRUE;
$mods = explode("|",$colmod);
$table = new html_table;
$table->set_table_parameters(0,0,0,0,0,0,0,"#000000",0);
$stmt = select_data($sql);
while(fetch($stmt))
{
if ($first_row) {
$ncols = NumCols($stmt);
$table->new_row("left","middle","#d7d6d6",0);
for ($i=1;$i<=$ncols;$i++) {
$table->new_cell(ColumnName($stmt,$i),"left",0,0,0,0,0,0,0);
}
}
$ncols = NumCols($stmt);
$table->new_row("left","middle","#dad9d9",0);
for ($i=1;$i<=$ncols;$i++) {
$colname = ColumnName($stmt,$i);
$colval = Result($stmt,$i);
if (preg_match ("/Pending/i", "$colval") || preg_match ("/Disconnected/i", "$colval")) {
foreach($mods as $mod) {
list ($var,$str) = explode("*",$mod);
if ($var == $colname) {
# we must have a mod that matches our column
$colval = ereg_replace("#".$var,$colval,$str);
# the above line should replace our Colname in
# the mode description with colval.
}
}
$table->new_cell($colval,"left",0,0,0,"#FFFFFF","#CCCCCC",0,0);
}
Else {
$table->new_cell($colval,"left",0,0,0,"#dad9d9",0,0,0);
}

}

$first_row = FALSE;


}
$table->show_table();
global $q;
$q=OCIRowCount($stmt);
if(!$q){
//Use this statement to global state no results if no table created
echo "<h4><B>No Results Returned</B></h4>";
}
return $q;
}




# common table routines
class html_cell {
var $align, $valign, $width, $height, $bgcolor, $background, $rowspan, $colspan;
var $cell_content;

function show_cell() {
echo "<td ";
if ($this->align) {echo "ALIGN=".$this->align." ";}
if ($this->valign) {echo "VALIGN=".$this->valign." ";}
if ($this->width) {echo "WIDTH=\"".$this->width."\" ";}
if ($this->height) {echo "HEIGHT=\"".$this->height."\" ";}
if ($this->bgcolor) {echo "BGCOLOR=\"".$this->bgcolor."\" ";}
if ($this->background) {echo "BACKGROUND=\"".$this->background."\" ";}
if ($this->rowspan) {echo "ROWSPAN=\"".$this->rowspan."\" ";}
if ($this->colspan) {echo "COLSPAN=\"".$this->colspan."\" ";}
echo ">".$this->cell_content."</td>\n";
}
}

class html_row {
var $align, $valign, $bgcolor, $background;
var $cell;

function new_cell($cell_content,$align,$valign,$width,$height,$bgcolor,$background,$rowspan,$colspan) {
$this->cell[] = new html_cell;
$newcell = new html_cell;
$newcell = new html_cell;
$newcell->cell_content = $cell_content;
$newcell->align = $align;
$newcell->valign = $valign;
$newcell->width = $width;
$newcell->height = $height;
$newcell->bgcolor = $bgcolor;
$newcell->background = $background;
$newcell->rowspan = $rowspan;
$newcell->colspan = $colspan;
$this->cell[sizeof($this->cell)-1] = $newcell;
}

function show_row() {
echo "<tr ";
if ($this->align) {echo "ALIGN=".$this->align." ";}
if ($this->valign) {echo "VALIGN=".$this->valign." ";}
if ($this->bgcolor) {echo "BGCOLOR=\"".$this->bgcolor."\" ";}
if ($this->background) {echo "BACKGROUND=\"".$this->background."\" ";}
echo ">";
for ($i=0;$i<sizeof($this->cell);$i++) {
$showcell = $this->cell[$i];
$showcell->show_cell();
}
echo "</tr>\n";
}
}

class html_table {
var $border, $cellspacing, $cellpadding, $cols, $width, $height, $bgcolor, $background;
var $row;
var $center;

function new_row($align,$valign,$bgcolor,$background) {
$this->row[] = new html_row;
$newrow = new html_row;
$newrow->align = $align;
$newrow->valign = $valign;
$newrow->bgcolor = $bgcolor;
$newrow->background = $background;
$this->row[sizeof($this->row)-1] = $newrow;

}

function set_table_parameters($center,$border,$cellspacing,$cellpadding,$cols,$width,$height,$bgcolor,$background) {
$this->center = $center;
$this->border = $border;
$this->cellspacing = $cellspacing;
$this->cellpadding = $cellpadding;
$this->cols = $cols;
$this->width = $width;
$this->height = $height;
$this->bgcolor = $bgcolor;
$this->background = $background;
}

function new_cell($cell_content,$align,$valign,$width,$height,$bgcolor,$background,$rowspan,$colspan) {
$actualrow = new html_row;
$actualrow = $this->row[sizeof($this->row)-1];
$actualrow->new_cell($cell_content,$align,$valign,$width,$height,$bgcolor,$background,$rowspan,$colspan);
$this->row[sizeof($this->row)-1] = $actualrow;
}

function show_table() {
if ($this->center) {echo "<CENTER>\n";}
echo "<table ";
if ($this->border) {echo "BORDER=\"".$this->border."\" ";}
if ($this->cellspacing) {echo "CELLSPACING=\"".$this->cellspacing."\" ";}
if ($this->cellpadding) {echo "CELLPADDING=\"".$this->cellpadding."\" ";}
if ($this->cols) {echo "COLS=\"".$this->cols."\" ";}
if ($this->width) {echo "WIDTH=\"".$this->width."\" ";}
if ($this->height) {echo "HEIGHT=\"".$this->height."\" ";}
if ($this->bgcolor) {echo "BGCOLOR=\"".$this->bgcolor."\" ";}
if ($this->background) {echo "BACKGROUND=\"".$this->background."\" ";}
echo ">\n";
for ($i=0;$i<sizeof($this->row);$i++) {
$showrow = $this->row[$i];
$showrow->show_row();
}

echo "</table>\n";
if ($this->center) {echo "</CENTER>\n";}
}
}

?>

 

[bastienk]

Lots of functions. I think you might get some perf gains if you were to let CSS take care of the appearance and just use PHP to process the results and echo out the table...


Bastien

Cat, the other other white meat

 

[willyd61]

Interesting..... That is a thought. Peel out all of the style stuff.

I wonder how much performance I would get out of that...

 

[ingresman]

Have you tried running just the query in a PHP page and finding out how long it takes ?. I don't think that the formating will be a big deal but tables in Internet Explorer can take a time to render, they don't do it sequetualy, it waits until the end so it can work out how wide to make the columns. In IE5 and above you can speed up the rendering by adding a few attributes and a thead tag, I can't remember the forat, try the HTML group for advice (I'll have a look in my book at home) or try the MS site under HTML tables.
Back to the query get the times for 100, 200, 500, 750,1000,1250 etc and plot them, see if the server is slowing down. This will be a good start. You could then time it when i an actualt page and see how much the html side is slowing you down.
Next step will be to add some timings to profile the code. PEAR has a nice class to do this.
What DB are you getting the problem with, is it all queres.
MS SQLserver has a thing called the firehose cursor which returns rows as soon as it can. I think MYSQL waits until the query has finished and then gives it to you.
Some stuff to think about anyway

 

[bastienk]

Another thing to consider is pagination. This way you can limit the number of records per page to a manageble number...and then requery for each new page





Bastien

Cat, the other other white meat

 

[ingresman]

Another thing is you can gzip the page before it goes to the browser (look in the manual). I think bastienk's suggestion is good to page. At 1000+ I bet you will be confusing IE a bit.