2
\$\begingroup\$

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 "</tr></thead>\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;
\$\endgroup\$
2
  • \$\begingroup\$ How large is your data? Do you have index on alliance? try not using sql functions. enable caching. \$\endgroup\$
    – PLB
    May 7 '12 at 10:52
  • \$\begingroup\$ id is the index and will continue to grow daily (1.3 mil rows of data at the moment) \$\endgroup\$
    – sgkdnay
    May 7 '12 at 15:35
1
\$\begingroup\$

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;
\$\endgroup\$
3
  • \$\begingroup\$ 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? \$\endgroup\$
    – sgkdnay
    May 7 '12 at 16:52
  • \$\begingroup\$ i THINK i found a way, unless said otherwise... See Edit Above: \$\endgroup\$
    – sgkdnay
    May 7 '12 at 17:08
  • \$\begingroup\$ 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 :) \$\endgroup\$
    – sgkdnay
    May 7 '12 at 17:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.