Chart.js, MySQL & PHP for Beginners

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>

Blog

Chart.js, MySQL & PHP for Beginners

kjvmartinez

Join Our Newsletter. No Spam, Only the good stuff.

Related