0👍
✅
@myEDU,
You need to alter your query with grouping by DATE(timestamp)
Here the SQL Fiddle as per your requirement.
I’m adding dynamic integration with php here in single controller including view,data manipulation as well as DB call, Codigniter follows MVC pattern and you need to split this to a MVC pattern.
Line chart expects data for one month in array and label too and that
we are serving through arrays$datasetarrand$datasetlbl
respectively.
Dynamic Integration
public function index()
{
?>
<!DOCTYPE html>
<html>
<head>
<title>Chart</title>
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.0/jquery.min.js"></script>
<script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.5.0/Chart.min.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<?php
$datasetarr = array(null);
$datasetlbl = array(0);
$month = date("m");
$mname = date("F");
$start = $end = strtotime(date("Y-m-01"));
$this->load->database();
$query = $this->db->query('SELECT COUNT(id) AS cnt, DATE(`timestamp`) AS dt FROM `recuperare_laborator` AS t1 GROUP BY dt HAVING MONTH(dt) = "'.$month.'" ORDER BY dt ASC');
$res = $query->result_array();
if($res){
$count_dt = array_column($res, 'dt');
$count_array = array_column($res, 'cnt');
$maxdt = max($count_dt);
//need to pass the month no in which you need.For eg: 05. Here i'm passing current month.
while ( $start <= $end ) {
$cdate = date('Y-m-d', $end);
$key = array_search($cdate, $count_dt);
if($key !== false){
$datasetarr[] = (int)$res[$key]['cnt'];
}else{
$datasetarr[] = 0;
}
$datasetlbl[] = date('d', $end);
$end = strtotime("+1 day", $end);
if( max($count_dt) < date('Y-m-d', $end) || $month != date('m', $end)){
break;
}
}
$datasetlbl[] = date('d', $end);
$datasetarr[] = null;
?>
<table class="table table-striped">
<thead>
<tr>
<td>count</td>
<td>date</td>
</tr>
</thead>
<tbody>
<?php
foreach ($res as $row)
{
echo "<tr>";
echo "<td>" . $row['cnt'] . "</td>";
echo "<td>" . $row['dt'] . "</td>";
echo "</tr>";
}
?>
</tbody>
</table>
<canvas id="line-chart" width="800" height="450"></canvas>
<?php
}
?>
</div>
</div>
</div>
<script type="text/javascript">
$(function(){
<?php if($res){ ?>
new Chart(document.getElementById("line-chart"), {
type: 'line',
data: {
labels: <?php echo json_encode($datasetlbl); ?>,
datasets: [{
data: <?php echo json_encode($datasetarr); ?>,
label: "<?php echo $mname; ?>",
borderColor: "#515fcf",
backgroundColor: "#515fcf",
fill: false
}
]
},
options: {
title: {
display: true,
text: 'Activity report 01 - <?php echo date("d", $end); ?>'
},
elements: {
point:{
radius: 0,
hoverRadius: 5
}
},
scales : {
xAxes : [ {
display: true,
scaleLabel: {
display: true,
labelString: 'Day'
},
gridLines : {
display : false
}
} ],
yAxes: [{
display: true,
scaleLabel: {
display: true,
labelString: 'Number of files submitted'
},
ticks: {
max: <?php echo (ceil((max($count_array)) / 10) * 10)+10; ?>,
min: 0,
stepSize: 10
}
}]
}
}
});
<?php } ?>
});
</script>
</body>
</html>
<?php
}
Static Chart
$(function() {
new Chart(document.getElementById("line-chart"), {
type: 'line', data: {
labels: [0, "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", "15", "16", "17"], datasets: [ {
data: [null, 21, 1, 0, 0, 0, 8, 0, 24, 0, 0, 0, 0, 0, 0, 0, 1, null], label: "May", borderColor: "#515fcf", backgroundColor: "#515fcf", fill: false
}
]
}
, options: {
title: {
display: true, text: 'Activity report 01 - 17'
}
, elements: {
point: {
radius: 0, hoverRadius: 5
}
}
, scales: {
xAxes: [ {
display: true, scaleLabel: {
display: true, labelString: 'Day'
}
, gridLines: {
display: false
}
}
], yAxes: [ {
display: true, scaleLabel: {
display: true, labelString: 'Number of files submitted'
}
, ticks: {
max: 30, min: 0, stepSize: 10
}
}
]
}
}
}
);
}
);<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.0/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.5.0/Chart.min.js"></script>
<div class="container">
<div class="row">
<div class="col-md-12">
<table class="table table-striped">
<thead>
<tr>
<td>count</td>
<td>date</td>
</tr>
</thead>
<tbody>
<tr>
<td>8</td>
<td>2020-05-01</td>
</tr>
<tr>
<td>1</td>
<td>2020-05-02</td>
</tr>
<tr>
<td>8</td>
<td>2020-05-06</td>
</tr>
<tr>
<td>24</td>
<td>2020-05-08</td>
</tr>
<tr>
<td>1</td>
<td>2020-05-16</td>
</tr>
</tbody>
</table>
<canvas id="line-chart" width="800" height="450"></canvas>
</div>
</div>
</div>If you want to fetch particular months data with MySQL query and want to avoids data manipulation process with PHP code, you can use the following query. Here you just need to pass month and year to the query in different places.In this query im fetching data for 2020 May. Added in Fiddle too
SELECT t1.*, COUNT(t2.id) AS cnt FROM (SELECT dt
FROM
(
SELECT
MAKEDATE('2020',1) +
INTERVAL ('05'-1) MONTH +
INTERVAL daynum DAY dt
FROM
(
SELECT t*10+u daynum
FROM
(SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
(SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) B
ORDER BY daynum
) AA
) AAA
WHERE MONTH(dt) = '05' ORDER BY dt) AS t1 LEFT JOIN `recuperare_laborator` AS t2 ON dt = DATE(`timestamp`) GROUP BY dt HAVING MONTH(dt) = '05' ORDER BY dt ASC;
Source:stackexchange.com