Create a ChartJS with 2 database tables

by

0👍

✅

You probably want to use a join and get the data in one query:

$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();

This selects all objects for a certain user from the objects table, and for each object finds the corresponding object_amounts from the object_amounts table. The left outer on the join also includes objects with no corresponding object_amounts.

I assume you want to display the sum of the object_totals for each object_date_created:
group_by groups the totals by date, SUM(oa.object_total) sums the totals for each date,
and order_by then sorts the resulting sums by date.

And in your JavaScript:

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

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

Leave a comment