06/Feb/2010 - Darren Dignam

Graphing with SQL and ASP

Different ways of displaying your data.

Here are some ways to present your data in a visual way. This is a mixture of ASP and SQL but can be replicated with PHP if you need to. I am also going to assume you are fairly familliar with these technologies but if you need to brush up, then checkout w3schools.
The data source for this posting is going to be the downlaod tracker that is used for the blocsoft freeware downloads.

Tabular Data:

Total Downloads: 52959

<%
mySQL = "SELECT FileName, Clicks FROM TrackerTable"
Set objRS = oConn.Execute(mySQL)
total=0
%>
<table border="1">
<tr><td><h3>File</h3></td><td><h3>Times Downloaded</h3></td></tr>
<%do until objRS.EOF%>
   <tr>
   <%for each x in objRS.Fields%>
      <td><%Response.Write(x.value)%></td>
   <%
   if x.name="Clicks" then total = total + x.value
   next
   objRS.MoveNext%>
   </tr>
<%loop 
objRS.Close
Set objRS = Nothing
%>
</table>
Total Downloads: <%=total %>

Bar Graph:

There is a little more work required to get this effect, but it requires no javascript, and only a single SQL lookup to gather all the values needed. You can build upon this for your own needs - use PHP - or have vertical bars.

The SQL statement is a bit more complicated for this lookup, we have additional fields being generated with totals and percentages calculated too. Take a look:

SELECT name_column, value_column, TotalDownloads, @percent :=100 * ( value_column /  TotalDownloads  ) AS percentage
FROM (
	SELECT FileName AS name_column, Clicks AS value_column
	FROM TrackerTable
)select_values, (
	SELECT SUM( Clicks ) AS TotalDownloads
	FROM TrackerTable
)select_total 

<style type="text/css">
<!--
/* Some Styling Stuff */
#barChart{
	background:#70bff8;
	padding:0 10px 10px;
	margin:10px;
}
#barChart div{
	background:#bbb;
	border-bottom:1px solid #000;
	margin-top:-5px;
}
#barChart div div{
	height:33px;
	background:#fff;
	border-bottom:1px solid #000;border-left:2px solid #000;
}
#barChart span{
	position:relative;
	top:20px;
	left:10px;
}
-->
</style>
<div id="barChart">
<%
mySQL = "SELECT name_column, value_column, TotalDownloads, @percent :=100 * ( value_column /  TotalDownloads  ) AS percentage FROM ( SELECT FileName  AS name_column, Clicks AS value_column FROM TrackerTable )select_values, ( SELECT SUM( Clicks ) AS TotalDownloads FROM TrackerTable )select_accumulating_value"
Set objRS = oConn.Execute(mySQL)

do until objRS.EOF  %>
<span><strong><%=objRS("name_column") %></strong>&#160;(<%=objRS("value_column") %>&#160;-&#160;<%=Round(objRS("percentage"),2) %>%)</span>
<div><div style="width:<%=Round(objRS("percentage")) %>%"></div></div>
<%
objRS.MoveNext
loop 

objRS.Close
Set objRS = Nothing
%>
</div>

SQL Pie Chart:

<script type="text/javascript"> 
var newString = ""; 
var theString = document.getElementById("pie").innerHTML; 
var counter = 0; 
var limit = theString.length;
var tmpChar = "";
var tmpStr = "";
for (counter;counter < limit ;counter++ ) { 
	tmpChar = theString.substring(counter, counter+1);
	switch(tmpChar)
	{
		case "@":
			tmpStr = "<span style='color:#fe2016'>"+tmpChar+"</span>";
			break;
		case "O":
			tmpStr = "<span style='color:#0d00f4'>"+tmpChar+"</span>";
			break;
		case "X":
			tmpStr = "<span style='color:#1fd90b'>"+tmpChar+"</span>";
			break;
		case ",":
			tmpStr = "<span style='color:#c516ca'>/</span>";
			break;
		default:
			tmpStr = tmpChar;
	}
	newString += tmpStr;  
} 
document.getElementById("pie").innerHTML = newString;
</script>

This piechart will not be to everyones taste, but you could tweak my colouring script above to make it look less ascii (with some images perhaps?). But if you are prepared to use javascript to draw graphs on your pages, then checkout these libraries:

• PlotKit
• jQuery Graphing
• More...

© 2009 BlocSoft.com All Rights Reserved. Contact