創建一個帶有2個資料庫表的ChartJS

我正在嘗試創建一個圖表（ChartJS），它結合了我的資料庫的 2 個表。

案子：

資料庫

• tableObject
  -- user_id
  -- object_id
  -- object_name
  -- object_date_created (YYY-MM-DD)

• tableObjectAmount
  -- object_amount_id
  -- object_id (對應tableObject)
  -- object_total

我想創建一個顯示 object_amount / object_date 的圖表；并且僅在我的應用程式的 tableObject->user_id == user_id 時顯示。

這是我的問題。

所以我的應用程式在 Codeingniter 上運行，這里是我的代碼：

public function index() {

    $objectsArray = array();
    $objectsDates = [];
    $objectsAmounts = [];

    $this->db->select('*');
    $this->db->from('objects');
    $this->db->where('user_id', $this->session->userdata('user_id'));
    $objectsDatesQuery = $this->db->get();


    foreach ($objectsDatesQuery->result() as $objectsDatesQueryRow) {
        $objectsDates['object_id'] = $objectsDatesQueryRow->object_id;
        $objectsDates['date'] = $objectsDatesQueryRow->object_date_created;


        $this->db->select('*');
        $this->db->from('object_amounts');
        $this->db->where('object_id', $objectsDates['object_id']);
        $objectsAmountsQuery = $this->db->get();

        foreach ($objectsAmountsQuery->result() as $objectsAmountsQueryRow) {
            $objectsDates['object_date'] = $objectsDatesQueryRow->object_date_created;
            $objectsAmounts['object_total'] = $objectsAmountsQueryRow->object_total;

            $objectsArray = array(
                'object_date' =>  $objectsDatesQueryRow->object_date_created,
                'object_total' => $objectsAmounts['object_total'],
            );
        }
    }
}

我圖表的代碼JS：

    var ctx = document.getElementById('myChart').getContext('2d');
        ctx.height = 246;
    var chartData = {"jsonarray": <?php echo json_encode($objectsArray); ?>};

    var labels = <?php echo json_encode($objectsArray['object_date']) ?>;
    var data = <?php echo json_encode($objectsArray['object_total']) ?>;

    var chart = new Chart(ctx, {
        // The type of chart we want to create
        type: 'bar',

        // The data for our dataset
        data: {
            labels: labels,
            datasets: [
                {
                    label: 'Object',
                    borderColor: "black",
                    color: 'red',
                    backgroundColor: "white",
                    height: 200,
                    pointRadius: 0,
                    data: data
                }
            ]
        },
        options: {
            plugins: {
                legend: {
                    display: false
                }
            },
            responsive: true,
            maintainAspectRatio: false,
            scales: {
                y: {
                    min: 0,
                    color: 'black'
                },
                x: {
                    min: 0,
                    color: 'black',
                }
            }
        }
    });

當我做

var_dump(json_encode($objectsArray))

我有

string(55) "{"objet_date":"2022-03-29","object_total":"3500.00"}"

最后，我的 chartJS：

我的chartJS的結果

非常感謝您的幫助！

uj5u.com熱心網友回復：

您可能希望使用聯接并在一個查詢中獲取資料：

$this->db->select('o.object_date_created, SUM(oa.object_total) AS total');
$this->db->from('objects o');
$this->db->join('object_amounts oa', 'o.object_id = oa.object_id', 'left outer');
$this->db->where('o.user_id', $this->session->userdata('user_id'));
$this->db->group_by('o.object_date_created');
$this->db->order_by('o.object_date_created', 'ASC');
$objectsArray = $this->db->get()->result_array();

這會從 objects 表中為某個用戶選擇所有物件，并為每個物件從 object_amounts 表中找到相應的 object_amounts。上還包括沒有相應 object_amounts 的物件left outer。join

我假設您要顯示每個 object_date_created 的 object_totals 的總和： group_by按日期對總計進行分組，SUM(oa.object_total)對每個日期的總計求和，order_by然后按日期對結果總和進行排序。

在你的 JavaScript 中：

var chartData = JSON.parse(<?= json_encode($objectsArray); ?>);

var labels = chartData.map( el => el.object_date_created );
var data = chartData.map( el => el.total );

標籤：php mysql 数据库 代码点火器 图表.js

上一篇：獲取存盤在多維陣列中的重復ID的總和

下一篇：Codeigniter4-應用驗證規則而不使欄位成為必填項