我有一个表(名称recoperare_laborator(，其中包含以下列：subject_id；上传者id；class_id；和时间戳。我想从所有表中每天计算所有文件，所以我做了这个代码：
$query = $this->db->query('SELECT subject_id ,uploader_id, class_id, COUNT(*) FROM recuperare_laborator GROUP BY class_id');
foreach ($query->result_array() as $row)
{
echo "<tr>";
echo "<td>" . $row['class_id'] . "</td>";
echo "<td>" . $row['COUNT(*)'] . "</td>";
echo "</tr>";
}
这段代码只适用于每个类的计数器。我是如何计算一天内的所有项目的？我需要看：
Day 1 100 count
Day 2 101 count
...
有了这些信息，我想制作一个动态图表。我试图制作，但我不知道如何实现代码。
<script>
var ctx = document.getElementById('myChart');
var myChart = new Chart(ctx, {
type: 'line',
data: {
labels: ['1','2'], //here i need day 1,day 2...
datasets: [{
data: [ '10','20'], // here i need count from day 1, count from day 2
backgroundColor: [
'rgba(67, 148, 255, 0.2)',
],
borderColor: [
'rgba(67, 148, 255, 1)',
],
borderWidth: 1
}]
},
});
</script>
结果必须是：图像图表
带行计数的图片
@myEDU，
您需要通过DATE(timestamp)分组来更改查询
根据您的要求，这里有SQL Fiddle。
我在单个控制器中添加了与php的动态集成，包括视图、数据操作以及DB调用，Codinger遵循MVC模式，您需要将其拆分为MVC模式。
折线图需要数组和标签中一个月的数据我们通过阵列
$datasetarr和$datasetlbl提供服务分别地
Chart.js DOC
动态集成
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
}
静态图表
$(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>如果你想用MySQL查询获取特定月份的数据，并想避免用PHP代码处理数据，你可以使用以下查询。在这里，您只需要将月份和年份传递给不同位置的查询。在这个查询中，我正在获取2020年5月的数据。添加在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;