Skip to content

Instantly share code, notes, and snippets.

@relliv

relliv/chartjs-example.php

Last active December 21, 2020 01:42
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save relliv/5270791ff01bb2a63d22259bb2bdbe06 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save relliv/5270791ff01bb2a63d22259bb2bdbe06 to your computer and use it in GitHub Desktop.
Download ZIP
PDO chart data example, chartjs multiline tooltip text
Raw
chartjs-example.php
<script src="Chart.bundle.js"></script>
<?php
$host = 'localhost';
$username = 'root';
$password = '';
$db_name = 'tes';
$conn = new PDO("mysql:host={$host};dbname={$db_name}", $username, $password);
$conn->exec("set names utf8");
$conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
// trim for sql query, 01 -> 1
$_POST['month1'] = isset($_POST['month1']) ? ltrim($_POST['month1'], '0') : '1';
$_POST['month2'] = isset($_POST['month2']) ? ltrim($_POST['month2'], '0') : '12';
// By month
// only between months
if(isset($_POST['year1']) && $_POST['year1'] == null){
$query = $conn->prepare("SELECT *, MONTHNAME(mydate) AS month_name, YEAR(mydate) AS year, COUNT(*) AS item_count
FROM mytable WHERE MONTH(mydate) BETWEEN :month1 AND :month2 GROUP BY MONTH(mydate)");
$query->bindParam(':month1', $_POST['month1'], PDO::PARAM_INT);
$query->bindParam(':month2', $_POST['month2'], PDO::PARAM_INT);
$query->execute();
$result_months = $query->fetchAll();
}
// between months with selected year
else {
$query = $conn->prepare("SELECT *, MONTHNAME(mydate) AS month_name, YEAR(mydate) AS year, COUNT(*) AS item_count
FROM mytable WHERE MONTH(mydate) BETWEEN :month1 AND :month2 AND YEAR(mydate) = :year1 GROUP BY MONTH(mydate)");
$query->bindParam(':month1', $_POST['month1'], PDO::PARAM_INT);
$query->bindParam(':month2', $_POST['month2'], PDO::PARAM_INT);
$query->bindParam(':year1', $_POST['year1'], PDO::PARAM_INT);
$query->execute();
$result_months = $query->fetchAll();
}
?>
<b>
<center>
<div id="TLap">CHART ITEM</div>
</center>
</b>
<br>
<form method="post">
<div style="font-size: 25px;">
<center><b>BY MONTH</b></center>
</div>
<table>
<tr>
<td>
<select name="year1">
<option value="">--Select year--</option>
<?php
$current_year = date('Y');
for ($x = $current_year; $x >= 2010; $x--) {
echo "<option value=\"{$x}\">{$x}</option>";
}
?>
</select>
</td>
</tr>
</table>
<table>
<tr>
<td>
<select name="month1">
<option value="">--Select Month--</option>
<option value="01">January</option>
<option value="02">February</option>
<option value="03">March</option>
<option value="04">April</option>
<option value="05">May</option>
<option value="06">June</option>
<option value="07">July</option>
<option value="08">August</option>
<option value="09">September</option>
<option value="10">October</option>
<option value="11">November</option>
<option value="12">December</option>
</select>
</td>
<td>
<select name="month2">
<option value="">--Select Month--</option>
<option value="01">January</option>
<option value="02">February</option>
<option value="03">March</option>
<option value="04">April</option>
<option value="05">May</option>
<option value="06">June</option>
<option value="07">July</option>
<option value="08">August</option>
<option value="09">September</option>
<option value="10">October</option>
<option value="11">November</option>
<option value="12">December</option>
</select>
</td>
<td><input id="btn_search" type="submit" value="Search"></td>
</tr>
</table>
<br>
<div id="center_year">
<center>
<?php
echo isset($_POST['year1']) && $_POST['year1'] ?
"<b>year {$_POST['year1']}</b><br>" :
"<b>year ".date("Y")."</b><br>";
if (isset($_POST['month1']) && isset($_POST['month2'])) {
$month1 = date('F', mktime(0, 0, 0, (int)$_POST['month1']));
$month2 = date('F', mktime(0, 0, 0, (int)$_POST['month2']));
echo "<b> Month {$month1} - Month {$month2}</b>";
}
?>
</center>
</div>
<div class="chart_bymonth">
<canvas id="myChart_month" width="100" height="100"></canvas>
</div>
<br>
<br>
<div style="font-size: 25px;">
<center><b>BY YEAR</b></center>
</div>
<table>
<tr>
<td>
<select name="year1">
<option value="">Select year</option>
<?php
$current_year = date('Y');
for ($x = $current_year; $x >= 2010; $x--) {
echo "<option value=\"{$x}\">{$x}</option>";
}
?>
</select>
</td>
<td>-</td>
<td>
<select name="year2">
<option value="">Select year</option>
<?php
$current_year = date('Y');
for ($x = $current_year; $x >= 2010; $x--) {
echo "<option value=\"{$x}\">{$x}</option>";
}
?>
</select>
</td>
<td><input id="btn_search" type="submit" value="Search"></td>
</tr>
</table>
<div id="center_year">
<br>
<center>
<?php
if (isset($_POST['year1']) and $_POST['year1']!=NULL) {
echo "<b>year ".$_POST['year1']." - year ".$_POST['year2']."</b>";
} else {
echo "<b>Chart year</b>";
}
?>
</center>
</div>
<div class="chart_byyear">
<canvas id="myChart_year" width="100" height="100"></canvas>
</div>
</form>
<script name="bymonth">
const byMonthData = [<?php foreach($result_months as $item) echo json_encode($item).','?>];
var ctx = document.getElementById("myChart_month");
var myChart = new Chart(ctx, {
type: 'bar',
data: {
labels: byMonthData.map(function (item) {
return [
[`${item.month_name} ${item.year}`],
[`Item: ${item.item}`]
]
}),
datasets: [{
label: "item",
data: byMonthData.map(function (item) {
return item.item_count
}),
backgroundColor: [
'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)',
'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)'
],
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)',
'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
}]
},
options: {
scales: {
yAxes: [{
ticks: {
beginAtZero: true
}
}]
},
}
});
</script>
<?php
// By year
// between all years
if (isset($_POST['year1']) && $_POST['year2'] == NULL) {
$query = $conn->prepare("SELECT *, YEAR(mydate) AS year, COUNT(*) AS item_count
FROM mytable GROUP BY YEAR(mydate)");
$query->bindParam(':year1', $_POST['year1'], PDO::PARAM_INT);
$query->execute();
$result_years = $query->fetchAll();
}
// only between selected years
else {
$query = $conn->prepare("SELECT *, YEAR(mydate) AS year, COUNT(*) AS item_count
FROM mytable WHERE YEAR(mydate) BETWEEN :year2 AND :year1 GROUP BY YEAR(mydate)");
$query->bindParam(':year1', $_POST['year1'], PDO::PARAM_INT);
$query->bindParam(':year2', $_POST['year2'], PDO::PARAM_INT);
$query->execute();
$result_years = $query->fetchAll();
}
?>
<script name="byyear">
const byYearData = [<?php foreach($result_years as $item) echo json_encode($item).','?>];
var ctx = document.getElementById("myChart_year");
var myChart = new Chart(ctx, {
type: 'bar',
data: {
labels: byYearData.map(function (item) {
return [
[`${item.year}`],
[`Item: ${item.item}`]
]
}),
datasets: [{
label: "item",
data: byYearData.map(function (item) {
return item.item_count
}),
backgroundColor: [
'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)',
'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)'
],
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)',
'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
}]
},
options: {
scales: {
yAxes: [{
ticks: {
beginAtZero: true
}
}]
}
}
});
</script>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment