use mysql query data to form Google Visualization Line Chart?

General PHP Help
#1

Hello,

I am trying to embed a php my sql query into a google visualization line chart. For instance, I have a web page that gathers unemployment statistics for the state of Indiana. Users restrict this data by selecting values from drop down menus.
[php]
$gYear = $_POST[“year”];
$gYear2 = $_POST[“year2”]; // These represent year intervals and a specific month
$gMonth = $_POST[“month”];
[/php]

I would like to transfer the values from my mysql query into javascript code so I create a nice graph for users to see. Here is my mysql code:
[php]
$sql = “SELECT $column, Year, Month FROM unemployed WHERE year BETWEEN ? AND ? and month= ?”;

$stmt = $conn->stmt_init();
$stmt->prepare($sql);
$stmt->bind_param(‘sss’, $gYear, $gYear2, $gMonth);
$stmt->bind_result($Column, $year, $month);
$stmt->execute();
$stmt->store_result();
$numRows = $stmt->num_rows
[/php]
I create a table for users to see the data:
[php]

<?php while ($stmt->fetch()) { ?> <?php echo $Column; ?> <?php echo $year; ?> <?php echo $month; ?> <?php } ?> <?php } ?> [/php][table] [tr][td]

[/td][/tr]
[/table]
The table would look something like this:

Indiana Year Month

189,556 1991 February
190,536 1992 February
185,959 1993 February

This looks fine, but I would like to include a line graph of the data using javascript. Here is the javascript code I have so far:

<script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = google.visualization.arrayToDataTable([
          ['Year',  'Month'], // Month would need to be the month that is selected from my dropdown menu. 
          ['2004',       400],
          ['2005',        460],  
          ['2006',        "100"], // This should be the data that is being retrieved from my query. 
          ['2007',      540]
        ]);

        var options = {
          title: 'Unemployed Statistics'
        };

        var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>

I am just not sure how to get my query data into this part of the code:

var data = google.visualization.arrayToDataTable([
          ['Year',  'Month'], // Month would need to be the month that is selected from my dropdown menu. 
          ['2004',       400], // Years would be based on the year intervals being selected in my query.
          ['2005',        460],  
          ['2006',        "100"], // This should be the data that is being retrieved from my query. 
          ['2007',      540]
]);

I really am lost when it comes to this because I am not very familiar with javascript. First of all, can what I want with the line chart even be done? If so, where do I begin? I am sorry for all the code, I just am completely lost with this one. Any help would be greatly appreciated.

#2

You could just loop through the rows and echo directly into inline javascript. Something like this:
[php]// create a string representation of the array values we’re going to insert into js
$chartString = ‘’;
while ($stmt->fetch()) {
$chartString .= “[’$year’, ‘$month’, ‘$Column’],”;
}

?>

[/php]

The better solution would be to create an API. Could just be a simple php file that generates the chart data array, json encodes and echoes it. Then you can do an Ajax request from java script and fetch it directly (really simple with Jquery).

#3

Okay I am sorry, but I am not familiar at all with Ajax and creating an API. How would I go about creating the php file that stores the data array?

Sponsor our Newsletter | Privacy Policy | Terms of Service