How can use SQL query to get values from DB in arrays for a line chart

Question

I'm trying to fetch data from DB for a line graph using the below code.

    <?php
        $dataPoints = array( 

            $sql1 = "SELECT * FROM chart_data_column WHERE value = 'now'";
            $result1 = $conn->query($sql1);
            if ($result1->num_rows > 0) {                               
            while($row1 = $result1->fetch_assoc()) { 

         array("y" => 25, "label" => "Sunday"), ?>

             } } else { } 
      );
    ?>
<script>
 window.onload = function () {

   var chart = new CanvasJS.Chart("chartContainer", {
  title: {
  text: ""
      },
  axisY: {
  title: ""
      },
   data: [{
type: "line",
   dataPoints: <?php echo json_encode($dataPoints, JSON_NUMERIC_CHECK); ?>
   }]
   });
  chart.render();
 }
</script>
<script src="https://canvasjs.com/assets/script/canvasjs.min.js"></script>
         <div id="chartContainer" style="height: 370px; width: 100%;"></div>

Using the above code it gives as error as Un-expected Syntax error, expecting ) instead of ; at $dataPoints line

However if i m to remove the sql query, graph plots with static data perfectly.

Any Help is greatly appreciated..

Dharman · Accepted Answer · 2020-05-07 20:56:00Z

1

I have to commend you for keeping PHP code and JavaScript separate. This is a very good idea. However, if you want to fetch all records from MySQL using PHP and mysqli library you do not need to have any loop. You can just fetch everything into an array and then display with json_encode() in JavaScript.

<?php

// import your mysqli connection before

$result1 = $conn->query("SELECT * FROM chart_data_column WHERE value = 'now'");
$dataPoints = $result1->fetch_all(MYSQLI_ASSOC);

?>
<script>
 window.onload = function () {

   var chart = new CanvasJS.Chart("chartContainer", {
  title: {
  text: ""
      },
  axisY: {
  title: ""
      },
   data: [{
type: "line",
   dataPoints: <?= json_encode($dataPoints, JSON_NUMERIC_CHECK); ?>
   }]
   });
  chart.render();
 }
</script>

<?= is short for <?php echo

answered May 7, 2020 at 20:56

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dharman · Accepted Answer · 2020-05-07 20:49:57Z

0

You put the entire query inside the array. You need to separate them. Also, you have "chart_data_column" where the table name should be.

$dataPoints = array();

$sql1 = "SELECT * FROM chart_data_column WHERE value = 'now'";
$result1 = $conn->query($sql1);
if ($result1->num_rows > 0) {
    while ($row = $result1->fetch_assoc()) {
        $dataPoints[] = $row;
    }
}

edited May 7, 2020 at 20:49

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered May 7, 2020 at 19:06

Adam Winter

2,0021 gold badge17 silver badges35 bronze badges

3 Comments

Nav Kiran Over a year ago

Here how do i assign fetched values to x and y.. like array("y" => 25, "label" => "Sunday"), ?> sunday & 25 in the given static data.?

Adam Winter Over a year ago

$row[0] will give you the first returned column value, $row[1] the second etc...

Nav Kiran Over a year ago

here i have 2 column values, that i need to fetch

Collectives™ on Stack Overflow

How can use SQL query to get values from DB in arrays for a line chart

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related