dpd3447 2013-01-13 13:00
浏览 86

创建完整的Google Chart JSON数据表 - 来自MySQL数据 - 使用自定义PHP函数 - 提供的工作示例 - 我是否需要这样做?

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>
  • 写回答

1条回答 默认 最新

  • dounei9043 2013-01-25 10:56
    关注

    I recently had this exact same task and was able to create a function that generates a Google Data Table based on any data returned by a MySQL query. I construct the JSON payload by using only json_encode() - no particular string manipulation. Note, I used mysqli instead of mysql so some of the things below might differ slightly with mysql.

    Here are some less obvious aspects :

    • To determine the "type" of the Google DataTable column, I used the "type" value of the array returned by mysqli_fetch_fields() (http://php.net/manual/en/mysqli-result.fetch-fields.php). I then wrote a small function that matches this mysqli type to its equivalent for Google's DataTable API. So, for example, if the mysqli_fetch_fields() returned :

      [0] => stdClass Object
      (
          [name] => Date
          [orgname] => date
          [table] => c
          [orgtable] => users
          [def] => 
          [db] => main
          [catalog] => def
          [max_length] => 10
          [length] => 10
          [charsetnr] => 63
          [flags] => 20617
          [type] => 10
          [decimals] => 0
      )
      

      You can see the type value is 10 which corresponds to MYSQLI_TYPE_DATE. A complete list of constants for type can be found here: http://php.net/manual/en/mysqli.constants.php

    • The date type did require some manipulation because JSON does not support Javascript Date objects (but Google's API does support "Date(Y,m,d,[h,i,s])" as you pointed out. One thing to note is that the month parameter of your MySQL date should be decreased by 1 as Javascript Date objects start at Month 0. To extract the date, I used this :

      $mysql_date = explode("-", $row[$i]);
      $mysql_date[1] = $mysql_date[1] - 1;
      if ($mysql_date[1] < 0) $mysql_date[1] = 11;
      $mysql_date = implode(",", $mysql_date);
      $value = "Date(" . $mysql_date . ")";
      array_push($c, array("v" => $value));
      

      preg_split works fine but note that it is slower (which is only an issue if you have a very large data set). More on that here: http://micro-optimization.com/explode-vs-preg_split

    • So far, the only string manipulation has been for handling dates, everything else is stored in a PHP array. When all the data is collected, you can call json_encode(). The problem with this is that for columns defined as "number" for the Google Table, json_encode() tends to return numbers surrounded by double-quotes. To force numbers to be treated as such, I used a simple regular expression :

      $json = json_encode($j);
      $json = preg_replace('/"(-?\d+\.?\d*)"/', '$1', $json);
      

    Hope this helps!

    评论

报告相同问题?

悬赏问题

  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题