# Rendering data into a graph

The output result is perfect to what I wanted, but takes a tad bit too long to pull data then render into a graph. Is there a better way to rewrite this code to speed up the process?

CASE "TestGrid":
$SnapArrayResult = array();$PlayArrayResult = array('A','B','C','D');
echo "<table id='tb' style='width:200px;height:200px;font-family:tahoma;font-size:2pt;visibility:hidden;' cellspacing='0'>\n";
echo "<caption>Data on Alliance</caption>\n";

$SnapResult =$db-> query("SELECT snap FROM scSVR WHERE hex(alliance) IN (hex('A'),hex('B'),hex('C'),hex('D')) GROUP BY snap ORDER BY snap ASC");
while ($SnapRow =$db-> fetch_assoc($SnapResult)){$SnapArrayResult[] = $SnapRow['snap']; } echo "<thead><tr><td></td>\n"; foreach($SnapArrayResult as $SAR){ echo "<th>".DTConvS($SAR)."</th>\n"; }
echo "<tbody>";
foreach($PlayArrayResult as$PAR){
echo "<tr><th scope='row'>".$PAR."</th>"; foreach($SnapArrayResult as $SAR){$PlayerResult = $db-> query("SELECT COALESCE(count(*),0) as pCnt FROM scSVR WHERE hex(alliance) = hex('".$PAR."') AND snap = ".$SAR);$PlayerRow = $db-> fetch_assoc($PlayerResult);
echo "<td>".$PlayerRow['pCnt']."</td>\n"; } echo "</tr>"; } echo "</tbody></table>"; break;  • How large is your data? Do you have index on alliance? try not using sql functions. enable caching. – PLB May 7 '12 at 10:52 • id is the index and will continue to grow daily (1.3 mil rows of data at the moment) May 7 '12 at 15:35 ## 1 Answer Look at the code below. I haven't tried it yet, but the idea is avoid iterating query that will take a long time. So, I just use one query. CASE "TestGrid":$SnapArrayResult = array();
$PlayArrayResult = array('A','B','C','D'); echo "<table id='tb' style='width:200px;height:200px;font-family:tahoma;font-size:2pt;visibility:hidden;' cellspacing='0'>\n"; echo "<caption>Data on Alliance</caption>\n"; echo "<thead><tr><td></td>\n";$SnapResult = $db-> query("SELECT snap,COALESCE(count(*),0) as pCnt,hex(alliance) as alliance FROM scSVR WHERE hex(alliance) IN (hex('A'),hex('B'),hex('C'),hex('D')) GROUP BY snap,alliance ORDER BY snap ASC");$changeSnap = null;
$PlayerRow = array(); while ($SnapRow = $db-> fetch_assoc($SnapResult))
{
if($changeSnap!=$SnapRow['snap'])
{
$SnapArrayResult[] =$SnapRow['snap'];
$changeSnap=$SnapRow['snap'];
$PlayerRow[$changeSnap] = array();
echo "<th>".DTConvS($SAR)."</th>\n"; }$PlayerRow[$changeSnap][$SnapRow['alliance']] = $SnapRow['pCnt']; } echo "</tr></thead>\n"; echo "<tbody>"; foreach($PlayArrayResult as $PAR){ echo "<tr><th scope='row'>".$PAR."</th>";
foreach($SnapArrayResult as$SAR){
echo "<td>".$PlayerRow[$SAR][\$PAR]."</td>\n";
}
echo "</tr>";
}
echo "</tbody></table>";
break;

• wow, i used this code, it REALLY cut down the load time, I really do mean BIG time lol.. ok now the issue arised that the chart is not rendering because it NEEDS to have some value (numeric) in it. For some reason, even with "COALESCE(count(*),0) as pCnt" in it, it gives no value in that particular <td></td>, how would I go about "forcing" to put in 0? May 7 '12 at 16:52
• i THINK i found a way, unless said otherwise... See Edit Above: May 7 '12 at 17:08
• ok 2 lines changed from original code provided by you bitoshi, it now renders everything AND supplies all proper data. Unless I'm doing something wrong, please DO lemme know :) May 7 '12 at 17:11