I am using code published on evolt.org to extract data from mysql database. The code:
<?php
header("Content-Type: application/vnd.ms-excel");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
//connect to and select database
if(!($dbconnect = mysql_pconnect("127.0.0.1", "joe", "jane"))){
print("Failed to connect to database!\n");
exit();
}
if(!mysql_select_db("test", $dbconnect)){
print("Failed to select database!\n");
exit();
}
//query for toy list
$qtoy = "SELECT DISTINCT os from master order by os";
if(!($dbtoy = mysql_query($qtoy, $dbconnect))){
print("MySQL reports: " . mysql_error() . "\n");
exit();
}
//query for crosstab
$qx = "SELECT risk ";
while($rowx = mysql_fetch_object($dbtoy)){
$qx .= ", count(IF(os = '$rowx->os',os,0)) AS osr";
}
$qx .= ", count(host) AS \"Total Hosts\" ";
$qx .= "FROM master ";
$qx .= "GROUP BY risk";
//print($qx);
if(!($dbx = mysql_query($qx, $dbconnect))){
print("MySQL reports: " . mysql_error() . "\n");
exit();
}
?>
<table border="1">
<tr>
<td bgcolor="#FFFFCC"></td>
<?php
mysql_data_seek($dbtoy, 0);
while($rowx = mysql_fetch_object($dbtoy)){
print("<td bgcolor=\"#FFFFCC\">");
print("$rowx->os");
print("</td>");
}
?>
<td bgcolor="#00FFFF"><strong>Total Risks</strong></td>
</tr>
<?php
while($dbrow = mysql_fetch_row($dbx)){
print("<tr>");
$col_num = 0;
foreach($dbrow as $key=>$value){
if($dbrow[$col_num] > 0){
print("<td>$dbrow[$col_num]</td>");
}
else {
print("<td> </td>");
}
$col_num++;
}
print("</tr>\n");
}
//total the columns
print("<tr bgcolor=\"#CCCCCC\">");
print("<td><strong>Total Risks</strong></td>");
$alpha = b;
$numeric = 2;
$rows = mysql_num_rows($dbx)+1;
for($i=1; $i < mysql_num_fields($dbx); $i++){
print("<td><strong>=sum($alpha$numeric:$alpha$rows)</strong></td>");
$alpha++;
}
print("</tr>\n");
?>
</table>
The output table gives the following output:
Microsoft Windows 2000 Advanced Server Microsoft Windows 2000 Server Microsoft Windows NT Microsoft Windows NT Server Microsoft Windows NT Server Enterprise Microsoft Windows NT Server Terminal Server Microsoft Windows Server 2003 Enterprise Microsoft Windows Server 2003 Standard Total Risks
2 4572 4572 4572 4572 4572 4572 4572 4572 4572 4572
3 3022 3022 3022 3022 3022 3022 3022 3022 3022 3022
6 4617 4617 4617 4617 4617 4617 4617 4617 4617 4617
7 3627 3627 3627 3627 3627 3627 3627 3627 3627 3627
Total Risks =sum(b2:b5) =sum(c2:c5) =sum(d2:d5) =sum(e2:e5) =sum(f2:f5) =sum(g2:g5) =sum(h2:h5) =sum(i2:i5) =sum(j2:j5) =sum(k2:k5)
I expect to see different count for each row, but does not seem to happen.
Thank you
SS
