php - PHP | 来自 SQL 数据库的饼图

Question

到目前为止，我一直在尝试创建显示特定值计数的饼图。我的表有产品行，每个产品都有服务合同日期。我需要的是显示在 contract_date 列中的年份计数。

例如这张表：

Product A   11-Nov-2019
Product B   21-Sep-2019
Product C   18-Oct-2020
Product D   23-Oct-2019
Product E   12-Jul-2019
Product F   10-Jun-2021
Product G   15-Jan-2020
Product H   24-Jan-2021
Product I   23-Oct-2019
Product J   21-Sep-2019

所以饼图应该显示：

我写了这段代码，但我无法正确执行计数功能

<?php
//include the library
include "libchart/libchart/classes/libchart.php";

//new pie chart instance
$chart = new PieChart( 500, 300 );

//data set instance
$dataSet = new XYDataSet();

//actual data
//get data from the database

//include database connection
include 'db_connect.php';

//query all records from the database
$query = "SELECT * FROM my_database";

//execute the query
$result = $mysqli->query( $query );

//get number of rows returned
$num_results = $result->num_rows;

if( $num_results > 0){

    while( $row = $result->fetch_assoc() ){
        extract($row);
        $Year = explode('-', $Service_contract);
        $dataSet->addPoint(new Point("{$Year[0]}", $Year[0]));
    }

    //finalize dataset
    $chart->setDataSet($dataSet);

    //set chart title
    $chart->setTitle("Expiry contracts year 2019-2023");
    
    //render as an image and store under "generated" folder
    $chart->render("libchart/demo/generated/1.png");

    //pull the generated chart where it was stored
    echo "<img alt='Pie chart'  src='/myfolder/libchart/demo/generated/1.png' style='border: 1px solid gray;'/>";

}else{
    echo "No programming languages found in the database.";
}
?>

此代码的结果显示如下：

应该怎么做才能解决问题？如何计算值而不是重复显示？我应该如何使用计数功能？

请指教

提前致谢！

标签： php

Answer 1

好的，所以我这样解决了（感谢@PatrickQ）

<?php
.....
....
if( $num_results > 0){

$result0 = mysqli_query($mysqli,"SELECT count(*) as count0 FROM my_database where YEAR(Service_contract) = '2019'");
$result1 = mysqli_query($mysqli,"SELECT count(*) as count1 FROM my_database where YEAR(Service_contract) = '2020'");
$result2 = mysqli_query($mysqli,"SELECT count(*) as count2 FROM my_database where YEAR(Service_contract) = '2021'");
$result3 = mysqli_query($mysqli,"SELECT count(*) as count3 FROM my_database where YEAR(Service_contract) = '2022'");
$result4 = mysqli_query($mysqli,"SELECT count(*) as count4 FROM my_database where YEAR(Service_contract) = '2023'");
$result0 = $result0->fetch_object();
$result1 = $result1->fetch_object();
$result2 = $result2->fetch_object();
$result3 = $result3->fetch_object();
$result4 = $result4->fetch_object();
$dataSet = new XYDataSet();
$dataSet->addPoint(new Point("2019", $result0->count0));
$dataSet->addPoint(new Point("2020", $result1->count1));
$dataSet->addPoint(new Point("2021", $result2->count2));
$dataSet->addPoint(new Point("2022", $result3->count3));
$dataSet->addPoint(new Point("2023", $result4->count4));

    //finalize dataset
    $chart->setDataSet($dataSet);

    //set chart title
    $chart->setTitle("Expiry contracts year 2019-2023");

    //render as an image and store under "generated" folder
    $chart->render("libchart/demo/generated/1.png");

    //pull the generated chart where it was stored
    echo "<img alt='Pie chart'  src='/myfolder/libchart/demo/generated/1.png' style='border: 1px solid gray;'/>";

}else{
    echo "No programming languages found in the database.";
}
?>

这是我所期待的结果：

我知道这段代码不是动态的，需要不时对其进行编辑，但现在这满足了我的需求。

谢谢 ：）