PHPBuilder - Show Cross-Tabular Data From Mysql Table

RSS Twitter

Show Cross-Tabular Data From Mysql Table

by: Erh-Wen,Kuo
October 31, 2001

There are many chances that I need to do some analysis from table,of course it's easy to make in Microsoft Access. But I want to use PHP & Mysql to finish this.
Below is my function:

function show_crosstab_result($db="",$table="",$col_name="",$row_name="",$value_name="")
{ $crosstab_query="Select $row_name"; mysql_connect('localhost'); mysql_select_db("$db"); $query="Select distinct $col_name from $table order by $col_name"; $result=mysql_query($query); if(empty($value_name)) { while(list($col)=mysql_fetch_array($result)) { $crosstab_query.=",sum(if($col_name=\"$col\",1,0)) as \"$col\""; } } else { while(list($col)=mysql_fetch_array($result)) { $crosstab_query.=",sum(if($col_name=\"$col\",$value_name,0)) as \"$col\""; } } $crosstab_query.=" from $table group by $row_name"; print $crosstab_query; $tab_result=mysql_query($crosstab_query); $num_fields=mysql_num_fields($tab_result); print "<table border=1>"; print " <tr>"; for($i=0;$i<$num_fields;$i++) { print " <th>".mysql_field_name($tab_result,$i)."</th>"; } print "</tr>"; while($row=mysql_fetch_array($tab_result,MYSQL_ASSOC)) { echo"<tr>"; while(list($key,$value)=each($row)) { if(ereg("[a-zA-Z]",$value)) { echo "<td align=left>".$value."</td>"; } else { echo "<td align=right>".$value."</td>"; } } echo"</tr>"; } print "</table>"; }

Comment and Contribute

Your comment has been submitted and is pending approval.




(Maximum characters: 1200). You have characters left.