PHP MySQL Google Chart JSON - 完整示例 [英] PHP MySQL Google Chart JSON - Complete Example

问题描述

我搜索了很多，找到了一个使用 MySQL 表数据作为数据源生成 Google 图表的好例子.我搜索了几天，发现可以使用 PHP 和 MySQL 组合生成 Google 图表(饼图、条形图、列、表)的示例很少.我终于设法让一个例子工作了.

之前从StackOverflow那里得到了很多帮助，所以这次我会返回一些.

我有两个例子；一个使用 Ajax，另一个不使用.今天，我只会展示非 Ajax 的例子.

用法:

<前>要求:PHP、Apache 和 MySQL安装:--- 使用 phpMyAdmin 创建一个数据库并将其命名为chart"--- 使用 phpMyAdmin 创建一个表并将其命名为googlechart"并制作确定表只有两列，因为我使用了两列.然而，如果您愿意，您可以使用超过 2 列，但您必须更改为此编写一点代码--- 指定列名如下:weekly_task"和percentage"--- 在表中插入一些数据--- 对于百分比列只使用一个数字---------------------------------示例数据:表格(googlechart)---------------------------------每周_任务百分比----------- ----------睡眠 30看电影 10工作 40练习 20

PHP-MySQL-JSON-Google 图表示例:

  '每周任务', 'type' => 'string'),数组('标签' => '百分比'，'类型' => '数字'));$rows = array();while($r = mysql_fetch_assoc($sth)) {$temp = 数组();//以下行将用于切片饼图$temp[] = array('v' => (string) $r['Weekly_task']);//每个切片的值$temp[] = array('v' => (int) $r['percentage']);$rows[] = array('c' => $temp);}$table['rows'] = $rows;$jsonTable = json_encode($table);//echo $jsonTable;?><头><!--加载 Ajax API--><script type="text/javascript" src="https://www.google.com/jsapi"></script><script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script><script type="text/javascript">//加载可视化 API 和饼图包.google.load('可视化', '1', {'packages':['corechart']});//设置在加载 Google Visualization API 时运行的回调.google.setOnLoadCallback(drawChart);函数 drawChart() {//从服务器加载的 JSON 数据中创建我们的数据表.var data = new google.visualization.DataTable(<?=$jsonTable?>);变量选项 = {title: '我的每周计划',is3D: '真',宽度:800，高度:600};//实例化并绘制我们的图表，传入一些选项.//不要忘记检查你的 div IDvar chart = new google.visualization.PieChart(document.getElementById('chart_div'));图表绘制(数据，选项)；}<身体><!--这是保存饼图的 div--><div id="chart_div"></div>

PHP-PDO-JSON-MySQL-Google 图表示例:

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);/* 从表格 googlechart 中选择所有每周任务 */$result = $conn->query('SELECT * FROM googlechart');/*---------------------------示例数据:表格(googlechart)--------------------------每周_任务百分比睡眠 30看电影 10工作 40练习 20*/$rows = array();$table = 数组();$table['cols'] = 数组(//图表的标签，这些标签代表列标题./*请注意，一列采用字符串"格式，另一列采用数字"格式因为饼图只需要数字"来计算百分比和字符串将用于切片标题*/array('label' => '每周任务', 'type' => 'string'),数组('标签' => '百分比'，'类型' => '数字'));/* 从 $result 中提取信息 */foreach($result as $r) {$temp = 数组();//以下行将用于切片饼图$temp[] = array('v' => (string) $r['weekly_task']);//每个切片的值$temp[] = array('v' => (int) $r['percentage']);$rows[] = array('c' => $temp);}$table['rows'] = $rows;//将数据转换为JSON格式$jsonTable = json_encode($table);//echo $jsonTable;} catch(PDOException $e) {回声'错误:'.$e->getMessage();}?><头><!--加载 Ajax API--><script type="text/javascript" src="https://www.google.com/jsapi"></script><script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script><script type="text/javascript">//加载可视化 API 和饼图包.google.load('可视化', '1', {'packages':['corechart']});//设置在加载 Google Visualization API 时运行的回调.google.setOnLoadCallback(drawChart);函数 drawChart() {//从服务器加载的 JSON 数据中创建我们的数据表.var data = new google.visualization.DataTable(<?=$jsonTable?>);变量选项 = {title: '我的每周计划',is3D: '真',宽度:800，高度:600};//实例化并绘制我们的图表，传入一些选项.//不要忘记检查你的 div IDvar chart = new google.visualization.PieChart(document.getElementById('chart_div'));图表绘制(数据，选项)；}<身体><!--这是保存饼图的 div--><div id="chart_div"></div>

PHP-MySQLi-JSON-Google 图表示例

query('SELECT * FROM googlechart');/*---------------------------示例数据:表格(googlechart)--------------------------Weekly_Task 百分比睡眠 30看电影 10工作 40练习 20*/$rows = array();$table = 数组();$table['cols'] = 数组(//图表的标签，这些标签代表列标题./*请注意，一列采用字符串"格式，另一列采用数字"格式因为饼图只需要数字"来计算百分比和字符串将用于切片标题*/array('label' => '每周任务', 'type' => 'string'),数组('标签' => '百分比'，'类型' => '数字'));/* 从 $result 中提取信息 */foreach($result as $r) {$temp = 数组();//以下行将用于切片饼图$temp[] = array('v' => (string) $r['weekly_task']);//每个切片的值$temp[] = array('v' => (int) $r['percentage']);$rows[] = array('c' => $temp);}$table['rows'] = $rows;//将数据转换为JSON格式$jsonTable = json_encode($table);//echo $jsonTable;?><头><!--加载 Ajax API--><script type="text/javascript" src="https://www.google.com/jsapi"></script><script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script><script type="text/javascript">//加载可视化 API 和饼图包.google.load('可视化', '1', {'packages':['corechart']});//设置在加载 Google Visualization API 时运行的回调.google.setOnLoadCallback(drawChart);函数 drawChart() {//从服务器加载的 JSON 数据中创建我们的数据表.var data = new google.visualization.DataTable(<?=$jsonTable?>);变量选项 = {title: '我的每周计划',is3D: '真',宽度:800，高度:600};//实例化并绘制我们的图表，传入一些选项.//不要忘记检查你的 div IDvar chart = new google.visualization.PieChart(document.getElementById('chart_div'));图表绘制(数据，选项)；}<身体><!--这是保存饼图的 div--><div id="chart_div"></div>

解决方案

有些人可能会在本地或服务器上遇到此错误:

语法错误 var data = new google.visualization.DataTable(<?=$jsonTable?>);

这意味着他们的环境不支持短标签，解决方案是使用它:

一切正常！

I have searched a lot to find a good example for generating a Google Chart using MySQL table data as the data source. I searched for a couple of days and realised that there are few examples available for generating a Google Chart (pie, bar, column, table) using a combination of PHP and MySQL. I finally managed to get one example working.

I have previously received a lot of help from StackOverflow, so this time I will return some.

I have two examples; one uses Ajax and the other does not. Today, I will only show the non-Ajax example.

Usage:

    Requirements: PHP, Apache and MySQL

    Installation:

      --- Create a database by using phpMyAdmin and name it "chart"
      --- Create a table by using phpMyAdmin and name it "googlechart" and make 
          sure table has only two columns as I have used two columns. However, 
          you can use more than 2 columns if you like but you have to change the 
          code a little bit for that
      --- Specify column names as follows: "weekly_task" and "percentage"
      --- Insert some data into the table
      --- For the percentage column only use a number

          ---------------------------------
          example data: Table (googlechart)
          ---------------------------------

          weekly_task     percentage
          -----------     ----------
          Sleep           30
          Watching Movie  10
          job             40
          Exercise        20    

PHP-MySQL-JSON-Google Chart Example:

    <?php
$con=mysql_connect("localhost","Username","Password") or die("Failed to connect with database!!!!");
mysql_select_db("Database Name", $con); 
// The Chart table contains two fields: weekly_task and percentage
// This example will display a pie chart. If you need other charts such as a Bar chart, you will need to modify the code a little to make it work with bar chart and other charts
$sth = mysql_query("SELECT * FROM chart");

/*
---------------------------
example data: Table (Chart)
--------------------------
weekly_task     percentage
Sleep           30
Watching Movie  40
work            44
*/

//flag is not needed
$flag = true;
$table = array();
$table['cols'] = array(

    // Labels for your chart, these represent the column titles
    // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
    array('label' => 'Weekly Task', 'type' => 'string'),
    array('label' => 'Percentage', 'type' => 'number')

);

$rows = array();
while($r = mysql_fetch_assoc($sth)) {
    $temp = array();
    // the following line will be used to slice the Pie chart
    $temp[] = array('v' => (string) $r['Weekly_task']); 

    // Values of each slice
    $temp[] = array('v' => (int) $r['percentage']); 
    $rows[] = array('c' => $temp);
}

$table['rows'] = $rows;
$jsonTable = json_encode($table);
//echo $jsonTable;
?>

<html>
  <head>
    <!--Load the Ajax API-->
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
    <script type="text/javascript">

    // Load the Visualization API and the piechart package.
    google.load('visualization', '1', {'packages':['corechart']});

    // Set a callback to run when the Google Visualization API is loaded.
    google.setOnLoadCallback(drawChart);

    function drawChart() {

      // Create our data table out of JSON data loaded from server.
      var data = new google.visualization.DataTable(<?=$jsonTable?>);
      var options = {
           title: 'My Weekly Plan',
          is3D: 'true',
          width: 800,
          height: 600
        };
      // Instantiate and draw our chart, passing in some options.
      // Do not forget to check your div ID
      var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
      chart.draw(data, options);
    }
    </script>
  </head>

  <body>
    <!--this is the div that will hold the pie chart-->
    <div id="chart_div"></div>
  </body>
</html>

PHP-PDO-JSON-MySQL-Google Chart Example:

<?php
    /*
    Script  : PHP-PDO-JSON-mysql-googlechart
    Author  : Enam Hossain
    version : 1.0

    */

    /*
    --------------------------------------------------------------------
    Usage:
    --------------------------------------------------------------------

    Requirements: PHP, Apache and MySQL

    Installation:

      --- Create a database by using phpMyAdmin and name it "chart"
      --- Create a table by using phpMyAdmin and name it "googlechart" and make sure table has only two columns as I have used two columns. However, you can use more than 2 columns if you like but you have to change the code a little bit for that
      --- Specify column names as follows: "weekly_task" and "percentage"
      --- Insert some data into the table
      --- For the percentage column only use a number

          ---------------------------------
          example data: Table (googlechart)
          ---------------------------------

          weekly_task     percentage
          -----------     ----------

          Sleep           30
          Watching Movie  10
          job             40
          Exercise        20     


    */

    /* Your Database Name */
    $dbname = 'chart';

    /* Your Database User Name and Passowrd */
    $username = 'root';
    $password = '123456';

    try {
      /* Establish the database connection */
      $conn = new PDO("mysql:host=localhost;dbname=$dbname", $username, $password);
      $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

      /* select all the weekly tasks from the table googlechart */
      $result = $conn->query('SELECT * FROM googlechart');

      /*
          ---------------------------
          example data: Table (googlechart)
          --------------------------
          weekly_task     percentage
          Sleep           30
          Watching Movie  10
          job             40
          Exercise        20       
      */



      $rows = array();
      $table = array();
      $table['cols'] = array(

        // Labels for your chart, these represent the column titles.
        /* 
            note that one column is in "string" format and another one is in "number" format 
            as pie chart only required "numbers" for calculating percentage 
            and string will be used for Slice title
        */

        array('label' => 'Weekly Task', 'type' => 'string'),
        array('label' => 'Percentage', 'type' => 'number')

    );
        /* Extract the information from $result */
        foreach($result as $r) {

          $temp = array();

          // the following line will be used to slice the Pie chart

          $temp[] = array('v' => (string) $r['weekly_task']); 

          // Values of each slice

          $temp[] = array('v' => (int) $r['percentage']); 
          $rows[] = array('c' => $temp);
        }

    $table['rows'] = $rows;

    // convert data into JSON format
    $jsonTable = json_encode($table);
    //echo $jsonTable;
    } catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
    }

    ?>


    <html>
      <head>
        <!--Load the Ajax API-->
        <script type="text/javascript" src="https://www.google.com/jsapi"></script>
        <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
        <script type="text/javascript">

        // Load the Visualization API and the piechart package.
        google.load('visualization', '1', {'packages':['corechart']});

        // Set a callback to run when the Google Visualization API is loaded.
        google.setOnLoadCallback(drawChart);

        function drawChart() {

          // Create our data table out of JSON data loaded from server.
          var data = new google.visualization.DataTable(<?=$jsonTable?>);
          var options = {
               title: 'My Weekly Plan',
              is3D: 'true',
              width: 800,
              height: 600
            };
          // Instantiate and draw our chart, passing in some options.
          // Do not forget to check your div ID
          var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
          chart.draw(data, options);
        }
        </script>
      </head>

      <body>
        <!--this is the div that will hold the pie chart-->
        <div id="chart_div"></div>
      </body>
    </html>

PHP-MySQLi-JSON-Google Chart Example

<?php
/*
Script  : PHP-JSON-MySQLi-GoogleChart
Author  : Enam Hossain
version : 1.0

*/

/*
--------------------------------------------------------------------
Usage:
--------------------------------------------------------------------

Requirements: PHP, Apache and MySQL

Installation:

  --- Create a database by using phpMyAdmin and name it "chart"
  --- Create a table by using phpMyAdmin and name it "googlechart" and make sure table has only two columns as I have used two columns. However, you can use more than 2 columns if you like but you have to change the code a little bit for that
  --- Specify column names as follows: "weekly_task" and "percentage"
  --- Insert some data into the table
  --- For the percentage column only use a number

      ---------------------------------
      example data: Table (googlechart)
      ---------------------------------

      weekly_task     percentage
      -----------     ----------

      Sleep           30
      Watching Movie  10
      job             40
      Exercise        20     


*/

/* Your Database Name */

$DB_NAME = 'chart';

/* Database Host */
$DB_HOST = 'localhost';

/* Your Database User Name and Passowrd */
$DB_USER = 'root';
$DB_PASS = '123456';





  /* Establish the database connection */
  $mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);

  if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
  }

   /* select all the weekly tasks from the table googlechart */
  $result = $mysqli->query('SELECT * FROM googlechart');

  /*
      ---------------------------
      example data: Table (googlechart)
      --------------------------
      Weekly_Task     percentage
      Sleep           30
      Watching Movie  10
      job             40
      Exercise        20       
  */



  $rows = array();
  $table = array();
  $table['cols'] = array(

    // Labels for your chart, these represent the column titles.
    /* 
        note that one column is in "string" format and another one is in "number" format 
        as pie chart only required "numbers" for calculating percentage 
        and string will be used for Slice title
    */

    array('label' => 'Weekly Task', 'type' => 'string'),
    array('label' => 'Percentage', 'type' => 'number')

);
    /* Extract the information from $result */
    foreach($result as $r) {

      $temp = array();

      // The following line will be used to slice the Pie chart

      $temp[] = array('v' => (string) $r['weekly_task']); 

      // Values of the each slice

      $temp[] = array('v' => (int) $r['percentage']); 
      $rows[] = array('c' => $temp);
    }

$table['rows'] = $rows;

// convert data into JSON format
$jsonTable = json_encode($table);
//echo $jsonTable;


?>


<html>
  <head>
    <!--Load the Ajax API-->
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
    <script type="text/javascript">

    // Load the Visualization API and the piechart package.
    google.load('visualization', '1', {'packages':['corechart']});

    // Set a callback to run when the Google Visualization API is loaded.
    google.setOnLoadCallback(drawChart);

    function drawChart() {

      // Create our data table out of JSON data loaded from server.
      var data = new google.visualization.DataTable(<?=$jsonTable?>);
      var options = {
           title: 'My Weekly Plan',
          is3D: 'true',
          width: 800,
          height: 600
        };
      // Instantiate and draw our chart, passing in some options.
      // Do not forget to check your div ID
      var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
      chart.draw(data, options);
    }
    </script>
  </head>

  <body>
    <!--this is the div that will hold the pie chart-->
    <div id="chart_div"></div>
  </body>
</html>

解决方案

Some might encounter this error either locally or on the server:

syntax error var data = new google.visualization.DataTable(<?=$jsonTable?>);

This means that their environment does not support short tags the solution is to use this instead:

<?php echo $jsonTable; ?>

And everything should work fine!

这篇关于PHP MySQL Google Chart JSON - 完整示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！