How to display database records data using Chart js?


(Jamess) #1

Hi, I am using yii2 basic application template. 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 the code, I get the foll output:

image

How should I resolve and achieve the target?


(Mehdi Achour) #2

Hi @SoftwareAccount,

It seems like you didn’t even try, as your PHP variable $groups is nowhere to be found… You should at least try something then ask for help if it fails, explaining what you tried, etc.

Otherwise I feel like you want gain much knowledge if we simply do your homework for you :slight_smile:

By the way, if you’re using Yii2, you really shouldn’t be using mysqli_xxx directly, but instead use ActiveRecord.


(Jamess) #3

I have used $groups in while loop.


(Mehdi Achour) #4

I meant no where to be find in the javascript… Try json_encode() ing it in the javascript variable, see what’s happen.


(Jamess) #5

Hi, I have a code using jquery. I have files namely:

  1. bargraph.html
  2. data.php
  3. app.js
  4. index.php

The contents for the above files are as follows.

  1. bargraph.html
<!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="mycanvas"></canvas>
			</div>
		

		<!-- javascript -->
		<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
		<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.7.1/Chart.min.js"></script>
		<script type="text/javascript" src="js/app.js"></script>
	</body>
</html>
  1. data.php
<?php
header('Content-Type: application/json');
$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);

if ($result!=null) {
    // output data of each row
   $data = array();
	foreach ($result as $row) {
	$data[] = $row;
	}
} 
$conn->close();

print json_encode($data);
  1. app.js
$(document).ready(function(){
	$.ajax({
		url: "http://localhost/basic/chartexample/data.php",
		method: "GET",
			dataType: "json",
		success: function(data) {
			console.log(data);
			var emp = [];
			var groups = [];

			for(var i in data) {
				emp.push(" " +data[i].FirstName);
				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("mycanvas");

			var barGraph = new Chart(ctx, {
				type: 'bar',
				data: chartdata
			});
		},
		error: function(data) {
			console.log(data);
		}
	});
});
  1. index.php
<script>
	$(document).ready(function(){
	$.ajax({
		url: "http://localhost/basic/chartexample/data.php",
		method: "GET",
			dataType: "json",
			success: function(data) {
			console.log(data);
			var emp = [];
			var groups = [];

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


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

var myChart = new Chart(ctx, {
    type: 'bar',
    data: {
        labels: emp,
        datasets: [{
            label: 'SHG Groups Profiles',
            data: groups,
            backgroundColor: 'skyblue',
                //'rgb(0, 255, 255,)',
				
                      
            borderWidth: 1,
			
        }]
    },
	options: {
        scales: {
            yAxes: [{
                ticks: {
                    beginAtZero:true,
				    stepSize: 1
					
                },

         
				
            }],
				xAxes: [{
            barPercentage: 0.4
        }]
        },

    }
    
});

},
	
	   });
});
			

		
</script>

it works perfectly on localhost but on VPS server I created subdomain and changed the document root to public_html/basic/web

Here the graph is not displayed at all?


(Softark) #6

This might work for your local environment, but will not on your production server.