CODEWITHSUNDEEP
phpmysqljsonchartsgoogle-visualization
user3817685
user3817685

Reputation: 35

Google chart for each row of MySqL table

New PHP user here. I have a database that looks like this:

 id    name    day1    day2    day3      day4       day5 
  1    nam1     5       9       15        50         45
  2    nam2     51      12      54        78         56
  3    nam3     12      145     78        49         58

The database contains thousands of users. Each number in the table represents the amount of daily activities per user. We need a table which looks like this 

 id    name    day1    day2    day3      day4       day5     chart
  1    nam1     5       9       15        50         45
  2    nam2     51      12      54        78         56
  3    nam3     12      145     78        49         58

We want to draw a google line chart in the last column for each user. This is the code to generate the chart:

    <?php

    $result = mysqli_query($c,"SELECT * from users limit 100");

    $row = mysqli_fetch_array($result); 

    $d1=$row['day1']; $d2=$row['day2']; $d3=$row['day3']; $d4=$row['day4']; $d5=$row['day5']; 

    //// that's the data that get loaded into Google Charts(no axis label) ////

    $data="[['','day'],['',".$d1."],['', ".$d2."],['', ".$d3. "],['', ".$d4."],['', ".$d5. "]]";   
     ?>
    <html>
    <head> 
    <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">
     google.load("visualization", "1", {packages:["corechart"]});
     google.setOnLoadCallback(drawChart);
     function drawChart() {
     var data = google.visualization.arrayToDataTable(<?php echo $data ?>);
     var options = {
                      title: 'User Activities',
                          curveType: 'function',
                          width:200,
                          height:150,
                          legend: 'none'

                    };

var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
                    chart.draw(data, options);
                  }
                </script> 
               </head>
            <body>
            <?php echo "<table border='1'>
            <tr>
            <th>id</th>
            <th>name</th>
            <th>charts</th>
            </tr>";

            while($row = mysqli_fetch_array($result)) {

              echo "<tr>";
               echo "<td>" . $row['id'] . "</td>";
                echo "<td>" . $row['name'] . "</td>";
                  echo "<td> 
                <div id=\"chart_div\" style=\"width:200; height:150\"></div></td>";

              echo "</tr>";
            }
            echo "</table>"; ?>
            </body>
            </html>

This code generates only one chart for user id=2. The first user, and the other users are ignored. How do you get a chart for each row? Thanks four help.

Upvotes: 2

Views: 1953

Answers (1)

asgallant
asgallant

Reputation: 26340

The easiest way requires a bit of rearranging your code to make it work:

<html>
    <head>
    </head>
    <body>
        <table border='1'>
            <tr><th>id</th><th>name</th><th>charts</th></tr>
<?php
$result = mysqli_query($c,"SELECT * from users limit 100");
$data = array(array('', 'Day 1', 'Day 2', 'Day 3', 'Day 4', 'Day 5'));
$i = 0;
while($row = mysqli_fetch_array($result)) {
    echo "<tr><td>{$row['id']}</td><td>{$row['name']}</td><td><div id='chart_div_{$i}' style='width:200; height:150'></div></td></tr>";
    $data[] = array('', $row['day1'], $row['day2'], $row['day3'], $row['day4'], $row['day5']);
    $i++;
}
?>
        </table>
        <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">
        google.load("visualization", "1", {packages:["corechart"]});
        google.setOnLoadCallback(drawChart);
        function drawChart() {
            var data = google.visualization.arrayToDataTable(<?php echo json_encode($data, JSON_NUMERIC_CHECK); ?>);
            var options = {
                title: 'User Activities',
                curveType: 'function',
                width: 200,
                height: 150,
                legend: 'none'
            };

            var charts = [];
            var views = [];
            for (var i = 0; i < data.getNumberOfRows(); i++) {
                views.push(new google.visualization.DataView(data));
                views[i].setRows([i]);
                charts.push(new google.visualization.LineChart(document.querySelector('#chart_div_' + i)));
                charts[i].draw(views[i], options);
            }
        }
        </script>
    </body>
</html>

That won't produce very nice LineCharts, however, as you will have 5 lines with 1 point each. If you are looking for a single line that spans 5 days, then this is how you want to set it up:

<html>
    <head>
    </head>
    <body>
        <table border='1'>
            <tr><th>id</th><th>name</th><th>charts</th></tr>
<?php
$result = mysqli_query($c,"SELECT * from users limit 100");
$data = array(
    array('Day'),
    array('Day 1'),
    array('Day 2'),
    array('Day 3'),
    array('Day 4'),
    array('Day 5')
);
$i = 0;
while($row = mysqli_fetch_array($result)) {
    echo "<tr><td>{$row['id']}</td><td>{$row['name']}</td><td><div id='chart_div_{$i}' style='width:200; height:150'></div></td></tr>";
    $data[0][] = "Daily activities for {$row['name']}";
    $data[1][] = $row['day1'];
    $data[2][] = $row['day2'];
    $data[3][] = $row['day3'];
    $data[4][] = $row['day4'];
    $data[5][] = $row['day5'];
    $i++;
}
?>
        </table>
        <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">
        google.load("visualization", "1", {packages:["corechart"]});
        google.setOnLoadCallback(drawChart);
        function drawChart() {
            var data = google.visualization.arrayToDataTable(<?php echo json_encode($data, JSON_NUMERIC_CHECK); ?>);
            var options = {
                title: 'User Activities',
                curveType: 'function',
                width: 200,
                height: 150,
                legend: 'none'
            };

            var charts = [];
            var views = [];
            for (var i = 0; i < data.getNumberOfColumns() - 1; i++) {
                views.push(new google.visualization.DataView(data));
                views[i].setColumns([0, i + 1]);
                charts.push(new google.visualization.LineChart(document.querySelector('#chart_div_' + i)));
                charts[i].draw(views[i], options);
            }
        }
        </script>
    </body>
</html>

Upvotes: 1

Related Questions