--- a/displayBubbletree.php
+++ b/displayBubbletree.php
@@ -1,4 +1,6 @@
-
+<?php
+include_once ("lib/common.inc.php");
+?>
 <!DOCTYPE html> 
 <html xmlns="http://www.w3.org/1999/xhtml"> 
 <head> 
@@ -17,60 +19,79 @@
 	<script type="text/javascript"> 
        
 		$(function() {
-		<?php
-include_once ("lib/common.inc.php");
-$unspscresult = mysql_query("select * from UNSPSCcategories;");
-while ($row = mysql_fetch_assoc($unspscresult)) {
-	$unspsc[$row['UNSPSC']] = $row['Title'];
+<?php
+include("lib/Color.php");
+$color = new Lux_Color();
+
+
+$unspscresult= $conn->prepare('select * from "UNSPSCcategories";');
+$unspscresult->execute();
+foreach ($unspscresult->fetchAll() as $row) {
+  $unspsc[$row['UNSPSC']] = $row['Title'];
+
 }
 $total = 0;
-$catsresult = mysql_query("SELECT LEFT( categoryUNSPSC, 1 ) as cat ,
-SUM( value ) as value
-FROM `contractnotice`
-WHERE childCN = 0
-GROUP BY cat ;");
+
+$catsresult = $conn->prepare('SELECT distinct substr( "categoryUNSPSC"::text, 0, 3 ) as cat 
+FROM contractnotice WHERE "categoryUNSPSC" IS NOT NULL group by "categoryUNSPSC";;');
+$catsresult->execute();
+foreach ($catsresult->fetchAll() as $row) {
+	$cats[] = $row['cat'];
+}
+$cattwosresult = $conn->prepare('SELECT distinct substr( "categoryUNSPSC"::text, 0, 5 ) as cat
+FROM contractnotice
+WHERE "categoryUNSPSC" IS NOT NULL
+GROUP BY  "categoryUNSPSC"  order by cat ;');
+$cattwosresult->execute();
+		foreach ($cattwosresult->fetchAll() as $tworow) { 
+			$cattwos[substr($tworow['cat'],0,2)][] = $tworow['cat'];
+		}
+				
+	
+
+$catthreesresult = $conn->prepare('SELECT substr( "categoryUNSPSC"::text, 0, 7 ) as cat , SUM( "value" ) as value
+FROM contractnotice
+WHERE "childCN" is null and "categoryUNSPSC" IS NOT NULL
+GROUP BY cat order by cat ;');
+$catthreesresult->execute();
+		foreach ($catthreesresult->fetchAll() as $threerow) { 
+			$catthrees[substr($threerow['cat'],0,4)][] = $threerow;
+		}
 $nodes = Array();
-while ($row = mysql_fetch_assoc($catsresult)) {
-	$catName = $unspsc[$row['cat'] . "0000000"] . $row['cat'];
-	if ($row['cat'] == "") $catName = "null";
+foreach ($cats as $catNum) {
+$catColor = $color->hsv2hex(Array($catNum/10, .7, abs(($catNum*(1/10))-.5) + .5));
+	$catName = substr($unspsc[$catNum . "000000"],0,18);
 	$subnodes = Array();
-	$cattwosresult = mysql_query("SELECT LEFT( categoryUNSPSC, 2 ) as cat ,
-SUM( value ) as value
-FROM `contractnotice`
-WHERE childCN = 0 and LEFT( categoryUNSPSC, 1 ) = '{$row['cat']}'
-GROUP BY cat ;");
-	while ($tworow = mysql_fetch_assoc($cattwosresult)) {
-		$subcatName = $unspsc[$tworow['cat'] . "000000"] . $tworow['cat'];
-		if ($tworow['cat'] == "") $subcatName = "null";
+	$catValue = 0;
+	foreach ($cattwos[$catNum] as $twoCatNum) {
+		$subcatName = $unspsc[$twoCatNum. "0000"];
 		$subsubnodes = Array();
-		$catthreesresult = mysql_query("SELECT LEFT( categoryUNSPSC, 3 ) as cat ,
-SUM( value ) as value
-FROM `contractnotice`
-WHERE childCN = 0 and LEFT( categoryUNSPSC, 2 ) = '{$tworow['cat']}'
-GROUP BY cat ;");
-		while ($threerow = mysql_fetch_assoc($catthreesresult)) {
-			$subsubcatName = $unspsc[$threerow['cat'] . "00000"] . $threerow['cat'];
-			if ($threerow['cat'] == "") $subsubcatName = "null";
+		$subCatValue = 1;
+		$subCatColor = $color->hsv2hex(Array($catNum/10, rand(1,10)/10, abs(($catNum*(1/10))-.5) + .5));
+		foreach ($catthrees[$twoCatNum] as $threerow) {
+			/*$subsubcatName = $unspsc[$threerow['cat'] . "00"];
 			$subsubnodes[] = Array(
 				"label" => $subsubcatName,
 				"amount" => $threerow['value'],
-				"color" => "#000000"
-			);
+				"color" => "#".$subCatColor
+			);*/
+			$subCatValue += $threerow['value'];
 		}
-		$subnodes[] = Array(
+		/*$subnodes[] = Array(
 			"label" => $subcatName,
-			"amount" => $tworow['value'],
-			"color" => "#000000",
+			"amount" => $subCatValue,
+			"color" => "#".$subCatColor,
 			"children" => $subsubnodes
-		);
+		);*/
+		$catValue += $subCatValue;
 	}
 	$nodes[] = Array(
 		"label" => $catName,
-		"amount" => $row['value'],
-		"color" => "#000000",
+		"amount" => $catValue,
+		"color" => "#".$catColor,
 		"children" => $subnodes
 	);
-	$total+= $row['value'];
+	$total += $catValue;
 }
 $data = Array(
 	"label" => "Australian Federal Government Contract Spending",
@@ -78,7 +99,7 @@
 	"color" => "#000000",
 	"children" => $nodes
 );
-echo "var data =eval('('+'" . json_encode($data) . "'+')');";
+echo "var data = JSON.parse('" . str_replace(",",",",json_encode($data)) . "');";
 ?>
 
 			new BubbleTree({

