如何使用php中的chart Js在MySQL中用数据绘制折线图

Question

我想使用MySQL表中的以下数据绘制一个折线图。

日期-12月10日、11日、12日、13日、14日、15日、16日、17日、18日、19日、20日出席人数-65、58、56、78、51、54、69、35、68、43、52

我使用下面的代码进行了两次查询，提取数据并存储在两个数组中，即$data1和$data2

$con = new mysqli($servername, $username, $password, $db);
$myquery1 = "select date from Table_attendance";
$query1 = mysqli_query($con, $myquery1);

if ( ! $query1 ) {
echo mysqli_error();
die;
}

for ($x = 0; $x < mysqli_num_rows($query1); $x++) {
$data1[] = mysqli_fetch_assoc($query1);
}

$my1 = "select attendance from Table_attendance";
$qu1 = mysqli_query($con, $my1);

if ( ! $qu1 ) {
echo mysqli_error();
die;
}

for ($x = 0; $x < mysqli_num_rows($qu1); $x++) {
$data2[] = mysqli_fetch_assoc($qu1);
}

然后，我使用以下代码将两个数组$data1和$data2编码为json格式。

$encod = json_encode($data1);
$encod2 = json_encode($data2);

数组的输出如下。

{"日期"："2021-12-10"}，{"日"："2021年12月11日"}，｛"日期："2021-12-12"｝，2021-12-15"}，{"日期"："2021-12-16"}，{日期：2021-12-19

{出席人数："65"}，{出席次数："58"}，｛出席次数；}，{"出席"："35"}，{"出席"："43"}，{"参加"："52"}]

最后，我尝试将如下编码的值传递到折线图中，但没有成功。

<script src="//code.jquery.com/jquery-1.9.1.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/Chart.js/2.4.0/Chart.min.js"></script>
<canvas id="myChart" style="width:100%;max-width:600px"></canvas>
<script>
var xValues = <?php echo $encod?>;
var yValues = <?php echo $encod2?>;
new Chart("myChart", {
type: "line",
data: {
labels: xValues,
datasets: [{
fill: false,
lineTension: 0,
backgroundColor: "rgba(0,0,255,1.0)",
borderColor: "rgba(0,0,255,0.1)",
data: yValues
}]
},
options: {
title: {display: true, text: 'Custom Chart Title'},
legend: {display: false},




scales: {
yAxes: [{ticks: {min: 6, max:16}}],
}
}
});
</script>

请帮我做这个评估。如果还有比我更好的代码，请好心分享。

Answer 1

您没有正确传递图表对象中的数据。您可以在php或js中转换对象。

下面js中的示例。

const ctx = document.getElementById("myChart").getContext("2d");
const xValues = [
{ date: "2021-12-10" },
{ date: "2021-12-11" },
{ date: "2021-12-12" },
{ date: "2021-12-13" },
{ date: "2021-12-14" },
{ date: "2021-12-15" },
{ date: "2021-12-16" },
{ date: "2021-12-17" },
{ date: "2021-12-18" },
{ date: "2021-12-19" },
{ date: "2021-12-20" },
];
const yValues = [
{ attendance: "65" },
{ attendance: "58" },
{ attendance: "56" },
{ attendance: "78" },
{ attendance: "51" },
{ attendance: "54" },
{ attendance: "69" },
{ attendance: "35" },
{ attendance: "68" },
{ attendance: "43" },
{ attendance: "52" },
];
const x = xValues.map(item => item.date);
const y = yValues.map(item => item.attendance);
new Chart(ctx, {
type: "line",
data: {
labels: x,
datasets: [
{
label: "Dataset 1",
backgroundColor: "rgba(0,0,255,1.0)",
borderColor: "rgba(0,0,255,0.1)",
data: y,
},
],
},
options: {
responsive: true,
plugins: {
legend: {
position: "top",
},
title: {
display: true,
text: "Chart.js Line Chart",
},
},
},
});

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<canvas id="myChart" width="400" height="400"></canvas>

EDIT完整的php示例

<?php
$con = new mysqli($servername, $username, $password, $db);
$myquery1 = "select date from Table_attendance";
$result1 = mysqli_query($con, $myquery1);
$rowsDate = mysqli_fetch_all($result1, MYSQLI_ASSOC);
$xValues = array_map(function ($item) {
return $item['date'];
}, $rowsDate);

$myquery2 = "select attendance from Table_attendance";
$result2 = mysqli_query($con, $myquery2);
$rowsAtd = mysqli_fetch_all($result2, MYSQLI_ASSOC);
$yValues = array_map(function ($item) {
return $item['attendance'];
}, $rowsAtd);
?>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<canvas id="myChart" width="400" height="400"></canvas>
<script>
const x = <?php echo json_encode($xValues) ?>;
const y = <?php echo json_encode($yValues) ?>;
new Chart(ctx, {
type: "line",
data: {
labels: x,
datasets: [{
label: "Dataset 1",
backgroundColor: "rgba(0,0,255,1.0)",
borderColor: "rgba(0,0,255,0.1)",
data: y,
}, ],
},
options: {
responsive: true,
plugins: {
legend: {
position: "top",
},
title: {
display: true,
text: "Chart.js Line Chart",
},
},
},
});
</script>

Answer 2

如回答中所述，问题在于编码。事实上，应该传递的数组应该是var xValues = [1,2,3,4,5,6]格式。但我得到的是var xValues = [{1},{2},{3},{4},{5},{6}]格式。无论如何，如果将来需要的话，我会发布我的整个代码。非常感谢那些试图帮助我的人。

完整代码如下

$con = new mysqli($servername, $username, $password, $db);
// getting date column from Table_attendance table 
$myquery1 = "select date from Table_attendance";
$query1 = mysqli_query($con, $myquery1);

if ( ! $query1 ) {
echo mysqli_error();
die;
}
$a1 ="";
for ($x = 0; $x < mysqli_num_rows($query1); $x++) {
$data1 = mysqli_fetch_assoc($query1);
if($a1 != ""){
$a1 = $a1.",'".$data1['date']."'"; 
}
else {
$a1 = "'".$data1['date']."'";
}
}

// getting attendance column from Table_attendance table 
$my1 = "select attendance from Table_attendance";
$qu1 = mysqli_query($con, $my1);

if ( ! $qu1 ) {
echo mysqli_error();
die;
}
$a2 ="";
for ($x = 0; $x < mysqli_num_rows($qu1); $x++) {
$data2 = mysqli_fetch_assoc($qu1);
if($a2 != ""){
$a2 = $a2.",".$data2['attendance']; 
}
else {
$a2 = $data2['attendance'];
}
}
?>
<?php //ploting the graph  ?>
<script src="//code.jquery.com/jquery-1.9.1.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/Chart.js/2.4.0/Chart.min.js"></script>
<canvas id="myChart" style="width:100%;max-width:600px"></canvas>
<script>
var xValues = <?php echo '['.$a1.']'; ?>;
var yValues = <?php echo '['.$a2.']'; ?>;
new Chart("myChart", {
type: "line",
data: {
labels: xValues,
datasets: [{
fill: false,
lineTension: 0,
backgroundColor: "rgba(0,0,255,1.0)",
borderColor: "rgba(0,0,255,0.1)",
data: yValues
}]
},
options: {
title: {display: true, text: 'Total attendance'},
legend: {display: false},




scales: {
yAxes: [{ticks: {min: 10, max:100}}],
}
}
});
</script>