Create a ChartJS with 2 database tables

Question

I'm trying to create a chart (ChartJS) that combine 2 tables of my database.

Case:

Database

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

• tableObjectAmount
  -- object_amount_id
  -- object_id (corresponding to the tableObject)
  -- object_total

I would like to create a chart that display object_amount / object_date; and showing only when tableObject->user_id == user_id of my app.

This is my problem.

So my app run on Codeingniter, here my code:

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'],
            );
        }
    }
}

The code JS of my chart :

    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',
                }
            }
        }
    });

When I do

var_dump(json_encode($objectsArray))

I have

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

And finally, my chartJS:

Result of my chartJS

Thank you very very much for any help!

Answer 1

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