Need to create a chart using a MySQL table in CodeIgniter 3-CodePudding

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.

Table

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

CodePudding user response：

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.

CodePudding user response：

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