Search code examples
javascriptphpgoogle-visualizationiotlinechart

google line chart only displaying one value without any line

so I'm new to PHP and I have put MySQL query in PHP command but then I tried to make a line chart from the data using JavaScript using different files(index.php for JavaScript and test.php for PHP) but when ever I try to run the code it shows null and it only appear one value from the TTIME

THis is my code in index.php file

``<?php

ini_set('memory_limit','512M');
ini_set('display_errors', 1);
error_reporting(E_ALL);

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname = 'DEMO';

$dblink = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);

// Check connection
if (!$dblink) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully<br /`>`";
`
// Perform your database query
$sql = ("Select P.ID, P.NAME, P.OID, MP.POLLID, MP.TTIME, MP.VAL, M.MOID, M.NAME, G.MOID, G.DEVICEID, G.COMMUNICATION FROM PolledData P LEFT join MULTI_POINT_SENSOR_DATA7_9_2023 MP on P.ID=MP.POLLID LEFT join ManagedObject M on P.ID=M.MOID LEFT join GatewayDevice G on M.MOID=G.MOID where MP.VAL IS NOT NULL
  LIMIT 350"); 



$results = mysqli_query($dblink, $sql); 
if (!$results) { 
  exit("Database query failed.");
}
echo 'DB in<br />';

$ndata = [];
$count = 0;
while ($data = mysqli_fetch_array($results)){
         $ndata[$count]['TTIME'] = $TTIME = $data['TTIME'];
         $ndata[$count]['VAL'] = $VAL = $data['VAL'];
          $count++;
          }

print_r($ndata);


?>

and this is my code in test.php file

`<?php include "test.php"; ?>
<html>
  <head>
  <title>index</title>
    <link rel="shortcut icon" href="#">
    
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">
    
      google.charts.load('current', {'packages':['Line']});
      google.charts.setOnLoadCallback(drawChart);

      function drawChart() {
        var data = google.visualization.arrayToDataTable([
          ['TTIME', 'VAL'],
     
          ['<?php echo $TTIME;?>', '<?php echo $VAL;?>']
        
        ]);

        var options = {
        chart: {
        title: 'TTIME and VAL',
        type: "line"
        },
        hAxis: {title: 'TTIME value'},
        vAxis: {title: 'VAL value'},
        width: 700,
        height: 500,
        colors: ['#d95f02']

      };

        var chart = new google.charts.Line(document.getElementById('line_chart'));
chart.draw(data, google.charts.Line.convertOptions(options));
      }
    </script>

  </head>

  <body>

    <div id="line_chart" style="width: 900px; height: 500px"></div>

    <?php
     //close the db connection
    mysqli_close($dblink);
    ?>

  </body>
</html>


  [1]: https://i.sstatic.net/iTRPR.png
Solution

  • You're only displaying one set of values in your chart.

    You gotta loop through your PHP $ndata array in your JavaScript to plot all values on your chart.

    1. index.php: Just keep this part of your loop. Remove extra assignments:
    while ($data = mysqli_fetch_array($results)){
    $ndata[$count]['TTIME'] = $data['TTIME'];
    $ndata[$count]['VAL'] = $data['VAL'];
    $count++;
}
    1. test.php: Modify your JavaScript:
    google.charts.load('current', {'packages':['Line']});
google.charts.setOnLoadCallback(drawChart);

function drawChart() {
    var dataArr = [['TTIME', 'VAL']]; // Column names

    // Loop through PHP array to add all data points
    <?php foreach ($ndata as $data): ?>
        dataArr.push(['<?php echo $data['TTIME'];?>', <?php echo $data['VAL'];?>]);
    <?php endforeach; ?>

    var data = google.visualization.arrayToDataTable(dataArr);
    // ... Rest of your chart settings and drawing code
}

    What's happening? Instead of plotting just one data point, we loop through all $ndata values and add them to the chart.

    index.php

    <?php

ini_set('memory_limit','512M');
ini_set('display_errors', 1);
error_reporting(E_ALL);

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname = 'DEMO';

$dblink = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);

// Check connection
if (!$dblink) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully<br />";

$sql = ("Select P.ID, P.NAME, P.OID, MP.POLLID, MP.TTIME, MP.VAL, M.MOID, M.NAME, G.MOID, G.DEVICEID, G.COMMUNICATION FROM PolledData P LEFT join MULTI_POINT_SENSOR_DATA7_9_2023 MP on P.ID=MP.POLLID LEFT join ManagedObject M on P.ID=M.MOID LEFT join GatewayDevice G on M.MOID=G.MOID where MP.VAL IS NOT NULL LIMIT 350"); 

$results = mysqli_query($dblink, $sql); 
if (!$results) { 
    exit("Database query failed.");
}
echo 'DB in<br />';

$ndata = [];
$count = 0;
while ($data = mysqli_fetch_array($results)){
    $ndata[$count]['TTIME'] = $data['TTIME'];
    $ndata[$count]['VAL'] = $data['VAL'];
    $count++;
}

print_r($ndata);

?>

    test.php

    <?php include "index.php"; ?>
<html>
<head>
    <title>index</title>
    <link rel="shortcut icon" href="#">
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">
        google.charts.load('current', {'packages':['Line']});
        google.charts.setOnLoadCallback(drawChart);

        function drawChart() {
            var dataArr = [['TTIME', 'VAL']];

            <?php foreach ($ndata as $data): ?>
                dataArr.push(['<?php echo $data['TTIME'];?>', <?php echo $data['VAL'];?>]);
            <?php endforeach; ?>

            var data = google.visualization.arrayToDataTable(dataArr);

            var options = {
                chart: {
                    title: 'TTIME and VAL',
                    type: "line"
                },
                hAxis: {title: 'TTIME value'},
                vAxis: {title: 'VAL value'},
                width: 700,
                height: 500,
                colors: ['#d95f02']
            };

            var chart = new google.charts.Line(document.getElementById('line_chart'));
            chart.draw(data, google.charts.Line.convertOptions(options));
        }
    </script>
</head>
<body>
    <div id="line_chart" style="width: 900px; height: 500px"></div>

    <?php
        //close the db connection
        mysqli_close($dblink);
    ?>
</body>
</html>