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:
Thank you very very much for any help!
CodePudding user response:
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 );