Question 268272 in Mysql Question posted in
The official MySQL documentation can be found here.

Display Line chart with data retrieved from MySQL Database

I am using a template built on Bootstrap 4 & 5. There is a line chart already featured, with hardcoded values being displayed.

Hardcoded values:

 <div class="sparkline" data-type="line" data-spot-Radius="0" data-offset="90" data-width="100%" data-height="50px"
                        data-line-Width="1" data-line-Color="#604a7b" data-fill-Color="#a092b0">1,4,2,3,6,2</div>
</div>

And it looks like this visually:

Now, I have a MySQL database and I want to retrieve the data to display it on the line chart.

I removed the hardcoded values and added the MySQL query. Please tell me what I’m doing wrong because the retrieved data is not being displayed. My DB connection is ok.

<div class="sparkline" data-type="line" data-spot-Radius="0" data-offset="90" data-width="100%" data-height="50px"
                        data-line-Width="1" data-line-Color="#604a7b"  data-fill-Color="#a092b0">
<?php
$sql2 = "SELECT water_level as water_level FROM `tbl_water_level` WHERE `date` > SUBDATE( NOW(), INTERVAL 2 HOUR)";
$result2 = mysqli_query($conn, $sql2);
$data = '';
while($data=mysqli_fetch_array($result2)){
echo $data["water_level"]; 
}
?>
</div>

I have created another file data.php to verify that the query is working as it should.

<?php
include("database.php");
?>
<?php
$sql2 = "SELECT `water_level` as water_level FROM `tbl_water_level` WHERE `date` > SUBDATE( NOW(), INTERVAL 2 HOUR)";
$result2 = mysqli_query($conn, $sql2);

$data = array();
foreach ($result2 as $row) {
  $data[] = $row;
}

echo json_encode($data);
?>

Below is the output when I run the file data.php and the values are what I have in my database row:

[{"water_level":"40"},{"water_level":"42"}]
Tags:

2

Answers


  1. 0

    It seems like there might be a small mistake in your PHP code. Instead of echo ‘.$row["water_level"].’;, you should use echo $data["water_level"];.

    <div class="sparkline" data-type="line" data-spot-Radius="0" data-offset="90" data-width="100%" data-height="50px"
                        data-line-Width="1" data-line-Color="#604a7b"  data-fill-Color="#a092b0">
<?php
$sql2 = "SELECT water_level as water_level FROM `tbl_water_level` WHERE `date` > SUBDATE( NOW(), INTERVAL 2 HOUR)";
$result2 = mysqli_query($conn, $sql2);

while($data = mysqli_fetch_array($result2)){
    echo $data["water_level"]; 
}
?>
</div>

    Additionally, ensure that your MySQL query is indeed returning some data. You might want to add some error handling in case the query fails or if there are no results.

    🙂

    Login or Signup to reply.
  2. 0

    Reading the data into an array first and printing the values, seperated by a comma, could resolve the problem. Try to use this code:

    <?php
$sql2 = "SELECT water_level as water_level FROM `tbl_water_level` WHERE `date` > SUBDATE( NOW(), INTERVAL 2 HOUR)";
$result2 = mysqli_query($conn, $sql2);
$levels = [];
while($row=mysqli_fetch_array($result2)){
  $levels[] = $row['water_level'];
}
echo implode(',', $levels);
?>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search