I need to make a dynamic table using the following table. It doesn't matter whether it's from ChartJS or Google Chart. I need to take "Blood Types" for the Y axis as labels and for the X axis I need to take the number of rows for the each blood type where "isAvailable" equals 1.
I tried above way, it gets the data to the view but need to rewrite the code for each blood type so it is not very efficient. I need to know is there any better way?
Controller
public function bloodTypesChart()
{
$query = $this->db->query("SELECT COUNT(PacketID) as count,(BloodType) as blood_type FROM packets WHERE (isAvailable) = '1'");
$packetCount = $this->db->count_all_results();
$record = $query->result();
$chartData = [];
foreach($record as $row) {
$chartData['label'][] = 'O+';
$chartData['data'][] = $packetCount;
}
$chartData['chart_data'] = json_encode($chartData);
$this->load->view('insight',$chartData);
}
This might not be the most efficient solution, but it is sure to get the job done for you right.
public function bloodTypesChart()
{
$totalPackets = $this->db->count_all_results();
$chartData = [];
$blood_types = $this->db->query("SELECT (BloodType) as blood_type FROM packets WHERE (isAvailable) = '1' GROUP BY blood_type")
->result_array();
foreach($blood_types as $bt)
{
$record = $this->db->query("SELECT COUNT(PacketID) as count FROM packets WHERE BloodType = '{$bt['blood_type']}'")
->result_array();
foreach($record as $row) {
$chartData['label'][] = $bt['blood_type'];
$chartData['data'][] = $row['count'];
}
}
$chartData['chart_data'] = json_encode($chartData);
$this->load->view('insight',$chartData);
}
RECOMENDED: You can also try something like this
$record = $this->db->from('packets')
->select('count(PacketID) count, BloodType blood_type')
->group_by('blood_type')
->get()
->result_array();
foreach($record as $row) {
$chartData['label'][] = $row['blood_type'];
$chartData['data'][] = $row['count'];
}
Of course you could still use the query() method and manualy write your queries.
I have not tested any of these but it should give you an idea of where to go next.
This is a better and a simple solution. Avoided using php loops to get data.
public function bloodTypesChart()
{
$query=" SELECT BloodType as blood_type, COUNT(PacketID) as mycount
FROM packets
WHERE isAvailable = 1
GROUP BY blood_type
";
$chartData = [];
$blood_types = $this->db->query($query)->result_array();
foreach($blood_types as $row)
{
$chartData['label'][] = $row['blood_type'];
$chartData['data'][] = $row['mycount'];
}
$chartData['chart_data'] = json_encode($chartData);
$this->load->view('insight',$chartData);
}