If you are new to webdev and want to learn how to incorporate charts and graphs in your webpages this article is for you.
The Northwind database is used in this article. You can find the sql file in this repository.
Setup the config folder with the following files and code in it. The code below will allow you to connect to MySQL.
config/config.php
<?php
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', 'yourpassword');
define('DB_NAME', 'northwind');
?>
config/db.php
<?php
// Create Connection
$conn = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);
// Check Connection
if(mysqli_connect_errno()){
// Connection Failed
echo 'Failed to connect to MySQL '. mysqli_connect_errno();
}
?>
After setting up the config directory you can now work on the webpage where you want to display your chart.
Chart No. 1: Pie Chart
Add the Chartjs script inside head tag.
<head>
<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/3.7.1/chart.min.js"></script>
</head>
Prepare canvas tag where you want to display the chart.
<canvas id="chartShippers"></canvas>
Below the canvas tag, let's write PHP code that will fetch the data that we need in our chart.
<?php
require('config/config.php');
require('config/db.php');
$query01 = "SELECT shippers.CompanyName, (Count(*)/(SELECT COUNT(*) FROM northwind.orders) * 100) as Count_Orders FROM northwind.orders, northwind.shippers WHERE shippers.ShipperID=orders.ShipVia GROUP BY ShipVia;";
// Get the result
$result01 = mysqli_query($conn, $query01);
// Put the result in array
if(mysqli_num_rows($result01) > 0){
// array for data
$Count_Orders = array();
// array for labels (x-axis)
$label_piechart = array();
while ($row = mysqli_fetch_array($result01)){
$Count_Orders[] = $row['Count_Orders'];
$label_piechart[] = $row['CompanyName'];
}
// Free result
mysqli_free_result($result01);
// Close the connection
mysqli_close($conn);
}else{
echo "No records matching your query were found.";
}
?>
Create a script tag below the PHP code. There will be three blocks inside the script, setup, config, and render block.
<script>
// <!-- setup block -->
const Count_Orders = <?php echo json_encode($Count_Orders); ?>;
const label_piechart = <?php echo json_encode($label_piechart); ?>;
const data1 = {
labels: label_piechart,
datasets: [{
label: 'My First Dataset',
data: Count_Orders,
backgroundColor: [
'rgb(255, 99, 132)',
'rgb(54, 162, 235)',
'rgb(255,165,0)'
],
hoverOffset: 4
}]
};
// <!-- config block -->
const config = {
type: 'pie',
data: data1,
};
// <!-- render block -->
const chartShippers = new Chart(
document.getElementById('chartShippers'),
config
);
</script>
Chart No. 2 Line Chart
Prepare canvas tag.
<canvas id="chartTop3"></canvas>
PHP code that fetches Top 3 Ordered products.
<?php
require('config/config.php');
require('config/db.php');
$query_top3 = "SELECT ProductName FROM northwind.order_details, northwind.products
WHERE products.ProductID=order_details.ProductID
GROUP BY products.ProductID order by count(*) desc, products.ProductName limit 3";
$result_top3 = mysqli_query($conn, $query_top3);
$products_top3 = array();
while ($row = mysqli_fetch_array($result_top3))
{
$products_top3[] = $row['ProductName'];
}
// Free result
mysqli_free_result($result_top3);
$Top1_Count = array_fill(0,12,0);
$Top2_Count = array_fill(0,12,0);
$Top3_Count = array_fill(0,12,0);
for ($counter=0; $counter<3; $counter++)
{
$query02 = "SELECT EXTRACT(MONTH FROM o.orderdate) as Month_1997, p.ProductName, COUNT(*) as num_order
FROM northwind.order_details od, northwind.orders o, northwind.products p
WHERE o.orderid = od.orderid and p.productid = od.ProductID and o.orderdate LIKE '1997%' and
p.ProductName = '" . $products_top3[$counter] .
"' GROUP BY p.ProductName, Month_1997
ORDER BY Month_1997, p.ProductName;";
$result02 = mysqli_query($conn, $query02);
if(mysqli_num_rows($result02) > 0){
while ($row = mysqli_fetch_array($result02)){
if ($counter==0){
$Top1_Count[$row['Month_1997']] = $row['num_order'];
} elseif ($counter==1){
$Top2_Count[$row['Month_1997']] = $row['num_order'];
} else {
$Top3_Count[$row['Month_1997']] = $row['num_order'];
}
}
}
}
?>
Script for Line Chart
<script>
// <!-- setup block -->
const Top1_Count = <?php echo json_encode($Top1_Count); ?>;
const Top2_Count = <?php echo json_encode($Top2_Count); ?>;
const Top3_Count = <?php echo json_encode($Top3_Count); ?>;
const label_1 = <?php echo json_encode($products_top3[0]); ?>;
const label_2 = <?php echo json_encode($products_top3[1]); ?>;
const label_3 = <?php echo json_encode($products_top3[2]); ?>;
const data2 = {
labels: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
datasets: [{
label: label_1,
data: Top1_Count, //[65, 59, 80, 81, 56, 55, 40, 80, 81, 56, 55, 40],
fill: false,
backgroundColor: 'rgb(255, 99, 132)',
borderColor: 'rgb(255, 99, 132)',
tension: 0.1
},
{
label: label_2,
data: Top2_Count, //[65, 59, 80, 55, 56, 55, 40, 80, 34, 56, 55, 40],
fill: false,
backgroundColor: 'rgb(54, 162, 235)',
borderColor: 'rgb(54, 162, 235)',
tension: 0.1
},
{
label: label_3,
data: Top3_Count, //[65, 45, 80, 81, 90, 55, 23, 80, 81, 56, 55, 40],
fill: false,
backgroundColor: 'rgb(255,165,0)',
borderColor: 'rgb(255,165,0)',
tension: 0.1
}]
};
// <!-- config block -->
const config2 = {
type: 'line',
data: data2,
};
// <!-- render block -->
const chartTop3 = new Chart(
document.getElementById('chartTop3'),
config2
);
</script>
Chart No. 3 Bar Graph (with 2 datasets)
Prepare canvas for the chart.
<canvas id="chartMeatvsSeafood"></canvas>
PHP code that fetches the number of orders per month of Products under Meat/Poultry and Seafood categories.
<?php
require('config/config.php');
require('config/db.php');
$query03 = "SELECT EXTRACT(MONTH FROM o.orderdate) as Month_1997, cat.CategoryName as CategoryName, SUM(od.UnitPrice*od.Quantity*(1-od.Discount)) as sales
FROM northwind.order_details od, northwind.orders o, northwind.products p, northwind.categories cat
WHERE o.orderid = od.orderid and p.productid = od.ProductID AND p.CategoryID=cat.CategoryID and
cat.CategoryName in('Meat/Poultry','Seafood') and o.orderdate LIKE '1997%'
GROUP BY cat.CategoryName, Month_1997
ORDER BY Month_1997, cat.CategoryName;";
// Get the result
$result03 = mysqli_query($conn, $query03);
// Put the result in array
if(mysqli_num_rows($result03) > 0){
$Sales_Meat = array();
$Sales_Seafood = array();
while ($row = mysqli_fetch_array($result03)){
if($row['CategoryName']=='Seafood'){
$Sales_Seafood[] = $row['sales'];
}else{
$Sales_Meat[] = $row['sales'];
}
}
// print_r($Sales_Seafood);
// print_r($Sales_Meat);
// Free result
mysqli_free_result($result03);
// Close the connection
mysqli_close($conn);
}else{
echo "No records matching your query were found.";
}
?>
Script for Bar Chart
<script>
// <!-- setup block -->
const Sales_Meat = <?php echo json_encode($Sales_Meat); ?>;
const Sales_Seafood = <?php echo json_encode($Sales_Seafood); ?>;
const data3 ={
labels: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
datasets: [{
label: 'Meat/Poultry',
data: Sales_Meat,
backgroundColor: [
'rgb(255, 99, 132)'
],
borderColor: [
'rgb(255, 99, 132)'
],
borderWidth: 1
},
{
label: 'SeaFood',
data: Sales_Seafood,
backgroundColor: [
'rgb(54, 162, 235)'
],
borderColor: [
'rgb(54, 162, 235)'
],
borderWidth: 1
}]
};
// <!-- config block -->
const config3 = {
type: 'bar',
data: data3,
options: {
scales: {
y: {
beginAtZero: true
}
}
}
};
// <!-- render block -->
const chartMeatvsSeafood = new Chart(
document.getElementById('chartMeatvsSeafood'),
config3
);
</script>
Chart No. 4 Bar Chart (one dataset)
Prepare canvas.
<canvas id="myChartTopFive"></canvas>
PHP code that fetches the number of orders every month for the Top 5 Ordered products.
<?php
require('config/config.php');
require('config/db.php');
$query04 = "SELECT ProductName, count(*) as order_count FROM northwind.order_details, northwind.products WHERE products.ProductID=order_details.ProductID GROUP BY products.ProductID order by order_count desc, products.Productname limit 5;";
// Get the result
$result04 = mysqli_query($conn, $query04);
// Put the result in array
if(mysqli_num_rows($result04) > 0){
$order_count = array();
$label_barchart = array();
while ($row = mysqli_fetch_array($result04)){
$order_count[] = $row['order_count'];
$label_barchart[] = $row['ProductName'];
}
// print_r($label_barchart);
// Free result
mysqli_free_result($result04);
// Close the connection
mysqli_close($conn);
}else{
echo "No records matching your query were found.";
}
?>
Script for Bar chart
<script>
// <!-- setup block -->
const order_count = <?php echo json_encode($order_count); ?>;
const label_barchart = <?php echo json_encode($label_barchart); ?>;
const data4 ={
labels: label_barchart, //['Red', 'Blue', 'Yellow', 'Green', 'Purple'],
datasets: [{
label: 'Number of Orders',
data: order_count,
backgroundColor: [
'rgba(255, 99, 132, 0.2)',
'rgba(54, 162, 235, 0.2)',
'rgba(255, 206, 86, 0.2)',
'rgba(75, 192, 192, 0.2)',
'rgba(153, 102, 255, 0.2)',
'rgba(255, 159, 64, 0.2)'
],
borderColor: [
'rgba(255, 99, 132, 1)',
'rgba(54, 162, 235, 1)',
'rgba(255, 206, 86, 1)',
'rgba(75, 192, 192, 1)',
'rgba(153, 102, 255, 1)',
'rgba(255, 159, 64, 1)'
],
borderWidth: 1
}]
};
// <!-- config block -->
const config4 = {
type: 'bar',
data: data4,
options: {
scales: {
y: {
beginAtZero: true
}
}
}
};
// <!-- render block -->
const myChartTopFive = new Chart(
document.getElementById('myChartTopFive'),
config4
);
</script>
Top comments (0)