Easy Chart.js Bar charts with PHP and MySQL

A shortcut method to quickly creating dynamic Chart.js bar charts using PHP and data from a MySQL database. This blog post is for a single Y-axis bar chart.

Gist link.

The idea is to set PHP variables at the top of the page for configuration and customization to then easily mass-produce bar charts with minimal editing.

This bar chart was made with the code below

php mysql chart js bar chart enerate
chart.js bar chart made with PHP and MySQL data

Unseen is the MySQL database but it is self-explanatory with the data being fetched for the id = 202331 and season type = 2 as this means regular season data.

Function yAxisValues() will Y-axis data with parameters being passed in as to what data (column) to fetch and return.

<?php
//Config options for chart data
$player_id = 202331;//Paul George player id
$season_type = 2;//2 is regular season, 3 is playoffs

//Design / Display options:
$bootstrap_css_url = 'https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.6.0/css/bootstrap.min.css';
$chartjs_js_url = 'https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.9.4/Chart.min.js';

$background_color = '#7bbbbb';//Page background color
$wrapper_color = '#126e6e';//Chart wrapper div background color

$page_title = "Paul George career FG chart";//HTML page title

$display_chart_title = true;//Display the text below:
$chart_title = "Paul George career field goal attempts per season";//Charts title
$chart_title_font_size = 16;//Charts title font size
$chart_title_font_color = '#c3f2fa';//Charts title color
$maintain_aspect_chart_title = true;//Keep aspect ratio for chart title

$display_legend = true;//Show legend
$legend_font_color = '#e2e8f1';//Legend font color
$legend_font_size = 16;//The legend font size

$bar1_name = 'FGA';//Color key/legend name
$bar1_color = '#6eeaa1';//Bar color
$bar1_border_color = '#2eae63';//Border color on legend color
$bar1_id = 'FGA';//Id

$x_axis_title = 'season (2020-21 up to 23rd March 2021)';//X axis values key
$x_axis_title_color = '#c4daf3';//X axis title color

$left_y_axis_title = 'Field goal attempts';//Left Y axis values key
$left_y_axis_title_color = '#c4daf3';//Left Y axis title color

$max_step_left = 1650;//Highest value on left Y axis (can be 'auto')
$step_size_left = 75;//Grid line step size left Y axis (can be 'auto')
$suggested_min_left = 0;//The minimum number to start at

$x_axis_grid_lines_display = false;//Show vertical grid lines
$x_axis_grid_lines_color = '';//Vertical grid line color

$y_axis_left_grid_lines_display = true;//Show Horizontal grid lines
$y_axis_left_grid_lines_color = '#628b9b';//Horizontal grid line color
$y_axis_left_tick_color = '#a7c5eb';//The field goals attempts

$x_axis_tick_color = '#a7c5eb';//The seasons
?>
<html lang="en">
<head>
    <title><?php echo $page_title; ?></title>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta http-equiv="content-type" content="text/html;charset=UTF-8">
    <link rel="stylesheet" href="<?php echo $bootstrap_css_url; ?>"/>
    <style>
        body {
            background-color: <?php echo $background_color;?>;
        }

        .wrapper {
            background-color: <?php echo $wrapper_color;?>;
            padding: 0.8rem;
            border-radius: 0.2rem;
            box-shadow: 0 10px 20px rgba(0, 0, 0, 0.19), 0 6px 6px rgba(0, 0, 0, 0.23);
        }

        .card {
            border: none;
            background-color: transparent;
        }
    </style>
</head>
<?php

function db_connect()
{
    $host = '127.0.0.1';
    $db_name = 'DATABASE';
    $db_user = 'USERNAME';
    $db_password = 'PASSWORD';
    $db = "mysql:host=$host;dbname=$db_name;charset=utf8mb4";
    $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
    return new PDO($db, $db_user, $db_password, $options);
}

function xAxisValues(int $value)
{//X axis is horizontal
    $db = db_connect();
    $select = $db->prepare("SELECT season FROM player_base_yby WHERE `pid` = ? GROUP BY season;");
    $select->execute([$value]);
    $data = array();
    while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
        $data[] = $row['season'];
    }
    echo json_encode($data);
}

function yAxisValues(string $value_type, int $value)
{//Y axis is verticals
    $db = db_connect();
    if ($value_type === 'FGM') {
        $select = $db->prepare("SELECT fgm as d FROM player_base_yby WHERE `pid` = ? GROUP BY season;");
    } elseif ($value_type === 'FGA') {
        $select = $db->prepare("SELECT fga as d FROM player_base_yby WHERE `pid` = ? GROUP BY season;");
    } elseif ($value_type === 'FGP') {
        $select = $db->prepare("SELECT (fg_pct * 100) as d FROM player_base_yby WHERE `pid` = ? GROUP BY season;");
    }
    $select->execute([$value]);
    $data = array();
    while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
        $data[] = $row['d'];
    }
    echo json_encode($data);
}

?>
<body>
<div class='container'>
    <div class="row text-center">
        <div class='col-12'>
            <div class="card">
                <div class="wrapper">
                    <canvas id="shot-distance-chart"></canvas>
                </div>
            </div>
        </div>
    </div>
</div>
<script src="<?php echo $chartjs_js_url; ?>"></script>
<script type="application/javascript">
    new Chart(document.getElementById("shot-distance-chart"), {
        "type": "bar",
        "data": {
            "labels": <?php xAxisValues($player_id);?>,
            "datasets": [{
                label: '<?php echo $bar1_name;?>', fill: true, lineTension: 0.1,
                backgroundColor: '<?php echo $bar1_color;?>',
                borderColor: '<?php echo $bar1_border_color;?>',
                data: <?php yAxisValues('FGA', $player_id);?>,
                yAxisID: '<?php echo $bar1_id;?>'
            }]
        },
        options: {
            elements: {point: {radius: 1}},
            legend: {
                display: '<?php echo $display_legend;?>',
                labels: {
                    fontColor: '<?php echo $legend_font_color;?>',
                    fontSize: <?php echo $legend_font_size;?>
                }
            },
            title: {
                display: '<?php echo $display_chart_title?>',
                text: '<?php echo $chart_title?>',
                fontSize: <?php echo $chart_title_font_size?>,
                maintainAspectRatio: '<?php echo $maintain_aspect_chart_title?>',
                fontColor: '<?php echo $chart_title_font_color?>'
            },
            responsive: true,
            scales: {
                xAxes: [{
                    display: true,
                    scaleLabel: {
                        display: true,
                        labelString: '<?php echo $x_axis_title;?>',
                        fontColor: '<?php echo $x_axis_title_color;?>'
                    },
                    ticks: {fontColor: '<?php echo $x_axis_tick_color;?>'},
                    gridLines: {
                        display: '<?php echo $x_axis_grid_lines_display;?>',
                        color: '<?php echo $x_axis_grid_lines_color;?>'
                    }
                }],
                yAxes: [{
                    type: "linear", display: !0, position: "left", id: '<?php echo $bar1_id;?>',
                    scaleLabel: {
                        display: true,
                        labelString: '<?php echo $left_y_axis_title;?>',
                        fontColor: '<?php echo $left_y_axis_title_color;?>'
                    },
                    ticks: {
                        fontColor: '<?php echo $y_axis_left_tick_color;?>',
                        suggestedMin: <?php echo $suggested_min_left;?>,
                        suggestedMax: <?php echo $max_step_left;?>,
                        stepSize: <?php echo $step_size_left;?>
                    },
                    gridLines: {
                        display: '<?php echo $y_axis_left_grid_lines_display;?>',
                        color: '<?php echo $y_axis_left_grid_lines_color;?>'
                    }
                }]
            }
        }
    })
</script>
</body>
</html>