How to display database records data using Char JS?

mysql

#1

I wanted to display database records data using Chart JS.
Below is the code.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "shgreportingdatabase";

// Create connection
$conn = new mysqli($servername, $username, $password,$dbname);

// Check connection
if ($conn->connect_error) {
    //die("Connection failed: " . $conn->connect_error);
} 
//echo "Connected successfully";


$sql = "SELECT CONCAT(employee.FirstName,' ',employee.LastName) as FullName, count(*) as TotalGroups from groupdetails, employee WHERE groupdetails.EmpId=employee.EmpId group by groupdetails.EmpId";

$result = $conn->query($sql);

while($row=mysqli_fetch_array($result))
{
   $name= $row['FullName'];
   $groups = $row['TotalGroups'];
}



?>


<!DOCTYPE html>
<html>
	<head>
		<title>ChartJS - BarGraph</title>
		<style type="text/css">
			#chart-container {
				width: 500px;
				height: auto;
			}
		</style>
	</head>
	<body>
			<div id="chart-container">
			<canvas id="chart"></canvas>
			</div>
		

		<!-- javascript -->
		
  <script
  src="https://code.jquery.com/jquery-3.3.1.js"
  integrity="sha256-2Kok7MbOyxpgUVvAk/HJ2jigOSYS2auK4Pfzbm7uH60="
  crossorigin="anonymous"></script>
		<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.7.3/Chart.bundle.min.js"></script>


<script>

var emp = [];
var groups = [];
var data = [];

for(var i in data) {
				emp.push(" " +data[i].FullName);
				groups.push(data[i].TotalGroups);
			}

			var chartdata = {
				labels: emp,
				datasets : [
					{
						label: 'SHG Groups',
						backgroundColor: 'rgba(200, 200, 200, 200)',
						borderColor: 'rgba(200, 200, 200, 0.75)',
						hoverBackgroundColor: 'rgba(200, 200, 200, 1)',
						hoverBorderColor: 'rgba(200, 200, 200, 1)',
						data: groups
					}
				]
			};


var ctx = document.getElementById("chart").getContext('2d');

			var chart = new Chart(ctx, {
				type: 'bar',
				data: chartdata
			});

</script>
		
	</body>
</html>

When I run code, I get foll output:

image

How should I resolve this?


#2

I don’t see anywhere where you pass the data that you retrieved with your query into the JS code to draw the graph. I also see this:

while($row=mysqli_fetch_array($result))
{
   $name= $row['FullName'];
   $groups = $row['TotalGroups'];
}

which will loop through the results object, and assign each result to a variable. Then the next time it goes around the loop, it will write over the previous value with the next. I suspect you intended to populate an array for the name and groups information, you need to add [] on the end of each to concatenate the values each time you run through the loop.

Then you need to get the values into your JavaScript code. As the PHP is also responsible for drawing the JS code, that should be a problem, but you will need to code it.

If you look at this bit of code

var data = [];

for(var i in data) {
    emp.push(" " +data[i].FullName);
    groups.push(data[i].TotalGroups);
    }

you can see that nothing will happen here. On one line you declare a new empty array called data, and then straight away you iterate through it in a for() loop, even though there’s nothing in between to populate it.

(I should add I’m not that familiar with JS, so some or all of the above JS-related talk may be incorrect).