(09-15-2022, 02:23 AM)optic4predator1 Wrote: I'm working on a college project using Codeigniter and I want to display the following data by showing the number of data in real time. so i would like to calculate the age of patients based on two dates, birthdate date and registration date, I would like to know the age of the patient at the time of treatment so for this i have a TABLE named THE_PATIENT which contains id, patient_name, date_of_birth, abou_patient, created_at and a TABLE named MEDICAL_INTERVENTION which contains id, patient_id, disease_type_id , created_at. So i would like to display the total of MEDICAL_INTERVENTIONS recorded and JOIN the THE_PATIENT Table, calculate age of patients from date_of_birth to created_at, and add cases if age between 1y to 15y range = kid , else if age between 16y to 70y range = adult , else if age over 70 range = old_adult , after geting data i want to group_by age range and display it in my view. Any help will be appreciated Thank you for your assistance, my respects.

Code:

class My_model extends CI_Model {
   
        function get_medical_intv_by_patients_age_range() {
            $q = $this->db->select(' medical_intervention.patient_id, the_patient.patient_name, COUNT(medical_intervention.patient_id) as total_medical_interventions, DATEDIFF (YEAR, the_patient.date_of_birth , the_patient.created_at) AS AGE, CASE WHEN AGE BETWEEN  1 to 15 THEN kid END AS patient_age_range_1, CASE WHEN AGE BETWEEN  16 to 70 THEN adult END AS patient_age_range_2, CASE WHEN AGE > 70 THEN old END AS patient_age_range_3,  ')
                          ->from('medical_intervention')
                          ->join('the_patient', 'medical_intervention.patient_id= the_patient.id', 'left')
                          ->group_by('patient_age_ranges')
                          ->get();
              return $q->result();
          }
    }