I am working on a personal project - using php to fetch data from MySQL, then encode that to JSON format (including columns) before loading it into a google data table via Ajax for visualisation.
Project went well, but I did the JSON encoding by pure string manipulation in php. Making several data sources for charts with a just a piece of SQL and a php function.
The reason I felt I needed this is for 1 bad reason, and 2 real reasons -
- I'm pretty new at Javascript
- JSON_ENCODE (php function) seemed to produce the wrong formatting required for date values.
- It output date (and date times) as: "2013-01-01 01:00:00"
- where google's documentation seemed to require: "Date(2013,1,1,0,0,0)"
- And JSON_Encode didn't produce output for the columns required - including assigning data types based on the MySQL result data type.
Question:
In context to the date format and column output for JSON (including data types based off result) - could I have solved these a lot easier than pure string manipulation?
I've posted my code below - while hideous - its working like a charm. If for some reason I have actually done some good, the community is welcome to use it.
It's 3 php files (though the last should probably be html), the first file is just filled with custom functions to be used in the 2nd page.
The 2nd php file dumps the data from My_SQL (echo DATA;) in JSON/GoogleChart format.
The last file/page uses ajax to take the data from the 2nd page, and insert it into an annoated timeline. Jquery setup is on the 3rd page, MySQL connection on the first and your SQL goes on the 2nd page.
<?php
// First Page/File: mysql_gc_functions.php
function mysql_loadresult($sql, $type){
// $sql - a string containing SQL to run against database
// $type - 0 for standard array load, 1 for JSON formatted string
$debug = false;
$result = mysql_query($sql) or die(mysql_error());
$numfields = mysql_num_fields($result);
$numrows = mysql_num_rows($result);
$br = '</br>';
$arr; // Array to Load Data Into where first index will contain headers
// Where x is the column and y is the row
// Row '0' contains Header Names
$datatypes; // Array to Load in Data Types
// Standard Array Loading
// Load Field Names
for ($x=0; $x < $numfields; $x++){
$arr[$x][0] = mysql_field_name($result,$x);
$datatypes[$x] = mysql_field_type($result,$x);
if($debug == true){echo 'data type for ' . $arr[$x][0] . ': ' . $datatypes[$x] . '</br>';}
}
$y = 0; // Row Index
while ($data = mysql_fetch_array($result, MYSQL_NUM)) {// For Each Row
$y++;
for ($x=0; $x<$numfields; $x++){ // For Each Field
$arr[$x][$y] = $data[$x]; // Load Query into php array
}
}
// JSON formatted string
if ($type == 1) {
/* JSON Format Example
{
"cols": [
{"label":"Topping","type":"string"},
{"label":"Slices","type":"number"}
],
"rows": [
{"c":[{"v":"Mushrooms"},{"v":3},
{"c":[{"v":"Onions"},{"v":1}]},
{"c":[{"v":"Olives"},{"v":1}]},
{"c":[{"v":"Zucchini"},{"v":1}]},
{"c":[{"v":"Pepperoni"},{"v":2}]}
]
}
mysql data type / google field type / Json value example:
date: date: "Date(2012,12,1)"
datetime: datetime: "Date(2012,12,1,23,59,59)" <- Y,M,D,H,M,S - can do milliseconds as well (but not built)
number: int/real: 123456
everything else: string: "EXAMPLE TEXT"
*/
$sColJason = '{"cols":[';
$eColJason = '],';
$ColExprS = '{"label":"';
$ColExprE = '","type":"XXX"}';
$colbuild = '';
// Build Column Structure for JSON
for ($x=0;$x<$numfields;$x++){
if ($datatypes[$x] == 'date'){
$tvar = str_replace('XXX', 'date', $ColExprE);
} else if ($datatypes[$x] == 'int' OR $datatypes[$x] == 'real'){
$tvar = str_replace('XXX','number',$ColExprE);
} else if ($datatypes[$x] == 'datetime'){
$tvar = str_replace('XXX','datetime',$ColExprE);
} else {
$tvar = str_replace('XXX','string',$ColExprE);
}
$str = $ColExprS . $arr[$x][0] . $tvar;
if ($x != ($numfields - 1)){
$str = $str . ",";
}
$colbuild = $colbuild . $str;
}
if($debug == true){echo $sColJason . $colbuild . $eColJason . '</br>';}
$sRowJason = '"rows": [';
$eRowJason = ']}';
$RowExpr = '{"v":"XXX"}';
$RowStart = '{"c":[';
$RowEnd = ']}';
$RowBuild = '';
// Build Row Structure for JSON
for ($y = 1; $y<$numrows;$y++){
// build each column of row (field of record)
$RowFieldBuild = '';
for ($x=0;$x<$numfields;$x++){
$var = $arr[$x][$y];
// assess how the data type needs to be treated
if ($datatypes[$x] == 'date'){
$var = mysql_googledate($var);
} else if ($datatypes[$x] == 'int' OR $datatypes[$x] == 'real'){
$var = $var;
} else if ($datatypes[$x] == 'datetime') {
$var = mysql_googleDtTm($var);
} else {
$var = '"' . $var . '"';
}
// concatenate the row string with populated values
$RowFieldBuild = $RowFieldBuild . str_replace('"XXX"',$var,$RowExpr);
if ($x != ($numfields-1)){
$RowFieldBuild = $RowFieldBuild . ',';
}
}
// Encapsulate the Record
$RowBuild = $RowBuild . $RowStart . $RowFieldBuild . $RowEnd;
if($y != ($numrows-1)){
$RowBuild = $RowBuild . ',';
}
}
// Encapsulate Entire Row Data
$RowBuild = $sRowJason . $RowBuild . $eRowJason;
if($debug == true){echo $RowBuild . '</br>';}
return ($sColJason . $colbuild . $eColJason . $RowBuild);
} else { // Return Standard Array of Table Data
return $arr;
}
}
function mysql_googledate($var){
$arr = preg_split("/-/",$var);
$month = intval($arr[1]);
$newStr = '"Date(' . $arr[0] . ',' . $month . ',' . $arr[2] . ')"';
return $newStr;
}
function mysql_googleDtTm($var){
$arr = preg_split("/-/",$var);
$tmArr = preg_split("/:/",$arr[2]);
$cm = ',';
$yr = intval($arr[0]);
$mon = intval($arr[1]);
$day = intval(substr($arr[2],0,2));
$hr = intval(substr($tmArr[0],-2));
$min = intval($tmArr[1]);
$sec = intval($tmArr[2]);
$newStr = '"Date(' . $yr . $cm . $mon . $cm . $day . $cm . $hr . $cm . $min . $cm . $sec . ')"';
return $newStr;
}
function mysql_localcon(){
$con = mysql_connect("localhost","root","1234");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
return $con;
}
?>
<?php
// Second Page/File: data_ATL.php
Include('mysql_gc_functions.php');
$sql = 'SELECT DATE_FIELD, VALUE_FIELD FROM DATABASE.TABLE';
$con=mysql_localcon();
$dataJason = mysql_loadresult($sql, 1);
echo $dataJason;
?>
// THIS IS THE FINAL (3rd) OUTPUT FILE/PAGE - DISPLAYS ANNOTATED TIME LINE
<html>
<head>
<script type='text/javascript' src='http://www.google.com/jsapi'></script>
<script type="text/javascript" src="jquery.js"></script>
<script type='text/javascript'>
google.load('visualization', '1', {'packages':['annotatedtimeline']});
google.setOnLoadCallback(drawChart);
function drawChart() {
var jsonData = $.ajax({
url: "data_ATL.php",
dataType:"json",
async: false
}).responseText;
var data = new google.visualization.DataTable(jsonData);
var chart = new google.visualization.AnnotatedTimeLine(document.getElementById('chart_div'));
chart.draw(data,
{
displayAnnotations: true,
annotationsWidth: 20,
scaleType: 'maximized'
}
);
}
</script>
</head>
<body>
<div id='chart_div' style='width: 700px; height: 240px;'></div>
</body>
</html>