consolidating $this->db->query() array

[eluser]smash120[/eluser]
Hello I am new to codeigniter, I am working on my first project and I have hit a road block. The issue is that I am joining three tables using the db functions and the results being pulled in are as follows

Code:

array
  0 =>
    array
      'account_num' => string '1' (length=1)
      'icd9_code' => string '1' (length=1)
      'dob' => string '12-12-1955' (length=10)
      'sex' => string 'Male' (length=4)
      'study' => string 'study 4' (length=7)
      'prescription' => string 'script 1' (length=8)
  1 =>
    array
      'account_num' => string '1' (length=1)
      'icd9_code' => string '1' (length=1)
      'dob' => string '12-12-1955' (length=10)
      'sex' => string 'Male' (length=4)
      'study' => string 'study 4' (length=7)
      'prescription' => string 'script 2' (length=8)
  2 =>
    array
      'account_num' => string '1' (length=1)
      'icd9_code' => string '1' (length=1)
      'dob' => string '12-12-1955' (length=10)
      'sex' => string 'Male' (length=4)
      'study' => string 'study 4' (length=7)
      'prescription' => string 'script 3' (length=8)
  3 =>
    array
      'account_num' => string '1' (length=1)
      'icd9_code' => string '1' (length=1)
      'dob' => string '12-12-1955' (length=10)
      'sex' => string 'Male' (length=4)
      'study' => string 'study 4' (length=7)
      'prescription' => string 'script 4' (length=8)
  4 =>
    array
      'account_num' => string '2' (length=1)
      'icd9_code' => string '2' (length=1)
      'dob' => string '11-11-1958' (length=10)
      'sex' => string 'Female' (length=6)
      'study' => string 'study 4' (length=7)
      'prescription' => string 'script 1' (length=8)
  5 =>
    array
      'account_num' => string '2' (length=1)
      'icd9_code' => string '2' (length=1)
      'dob' => string '11-11-1958' (length=10)
      'sex' => string 'Female' (length=6)
      'study' => string 'study 4' (length=7)
      'prescription' => string 'script 3' (length=8)
  6 =>
    array
      'account_num' => string '2' (length=1)
      'icd9_code' => string '2' (length=1)
      'dob' => string '11-11-1958' (length=10)
      'sex' => string 'Female' (length=6)
      'study' => string 'study 4' (length=7)
      'prescription' => string 'script 4' (length=8)
  7 =>
    array
      'account_num' => string '3' (length=1)
      'icd9_code' => string '3' (length=1)
      'dob' => string '03-11-1958' (length=10)
      'sex' => string 'Female' (length=6)
      'study' => string 'study 4' (length=7)
      'prescription' => string 'script 3' (length=8)
  8 =>
    array
      'account_num' => string '3' (length=1)
      'icd9_code' => string '3' (length=1)
      'dob' => string '03-11-1958' (length=10)
      'sex' => string 'Female' (length=6)
      'study' => string 'study 4' (length=7)
      'prescription' => string 'script 4' (length=8)

I managed to loop thru to get the output to display as follows

Code:

array
  'account_num' => string '1' (length=1)
  'dob' => string '12-12-1955' (length=10)
  'sex' => string 'Male' (length=4)
  'study' => string 'study 4' (length=7)
  'prescription' => string 'script 1' (length=8)
array
  'account_num' => string '1' (length=1)
  'dob' => string '12-12-1955' (length=10)
  'sex' => string 'Male' (length=4)
  'study' => string 'study 4' (length=7)
  'prescription' => string 'script 2' (length=8)
array
  'account_num' => string '1' (length=1)
  'dob' => string '12-12-1955' (length=10)
  'sex' => string 'Male' (length=4)
  'study' => string 'study 4' (length=7)
  'prescription' => string 'script 3' (length=8)
array
  'account_num' => string '1' (length=1)
  'dob' => string '12-12-1955' (length=10)
  'sex' => string 'Male' (length=4)
  'study' => string 'study 4' (length=7)
  'prescription' => string 'script 4' (length=8)
array
  'account_num' => string '2' (length=1)
  'dob' => string '11-11-1958' (length=10)
  'sex' => string 'Female' (length=6)
  'study' => string 'study 4' (length=7)
  'prescription' => string 'script 1' (length=8)
array
  'account_num' => string '2' (length=1)
  'dob' => string '11-11-1958' (length=10)
  'sex' => string 'Female' (length=6)
  'study' => string 'study 4' (length=7)
  'prescription' => string 'script 3' (length=8)
array
  'account_num' => string '2' (length=1)
  'dob' => string '11-11-1958' (length=10)
  'sex' => string 'Female' (length=6)
  'study' => string 'study 4' (length=7)
  'prescription' => string 'script 4' (length=8)
array
  'account_num' => string '3' (length=1)
  'dob' => string '03-11-1958' (length=10)
  'sex' => string 'Female' (length=6)
  'study' => string 'study 4' (length=7)
  'prescription' => string 'script 3' (length=8)
array
  'account_num' => string '3' (length=1)
  'dob' => string '03-11-1958' (length=10)
  'sex' => string 'Female' (length=6)
  'study' => string 'study 4' (length=7)
  'prescription' => string 'script 4' (length=8)

As you can see some info in each array are the same I wanted to know if there is a way to consolidate this data so it display as follows

account num 1
icd9 code 1
dob 12-12-1955
sex Male
study study 2
prescriptions script 1, script 2, script 3, script 4

account num 2
icd9 code 2
dob 11-11-1958
sex Female
study study 2
prescriptions script 1, script 3, script 4

and so on

and not like this

account num 1
icd9 code 1
dob 12-12-1955
sex Male
study study 2
prescriptions script 1

account num 1
icd9 code 1
dob 12-12-1955
sex Male
study study 2
prescriptions script 2

account num 1
icd9 code 1
dob 12-12-1955
sex Male
study study 2
prescriptions script 3

account num 1
icd9 code 1
dob 12-12-1955
sex Male
study study 2
prescriptions script 4

account num 2
icd9 code 2
dob 11-11-1958
sex Female
study study 2
prescriptions script 1

and so on. I appreciate any suggestions

[eluser]code137[/eluser]
Without knowing what the actual queries look like, I'd say look into group_concat for your query. Otherwise you will have to loop through it. http://dev.mysql.com/doc/refman/5.0/en/g...oup-concat

[eluser]smash120[/eluser]
This is the code I used to query the data base

Code:

$q = $this->db->query("SELECT ci_patient_info.account_num
                              FROM ci_patient_info
                              LEFT JOIN ci_study_history ON ci_patient_info.account_num = ci_study_history.account_num
                              LEFT JOIN ci_prescription_history ON ci_patient_info.account_num = ci_prescription_history.account_num
                              WHERE ci_patient_info.account_num = '".$this->input->post('search')."'".
                              " OR ci_patient_info.icd9_code = '".$this->input->post('search')."'".
                              " OR ci_patient_info.dob = '".$this->input->post('search')."'".
                              " OR ci_patient_info.dob = '".$this->input->post('search')."'".
                              " OR ci_study_history.study = '".$this->input->post('search')."'".
                              " OR ci_prescription_history.prescription = '".$this->input->post('search')."'");
        foreach ($q->result_object() as $key) {//here i consolidate all account numbers
            foreach ($key as $k => $v) {
                $acc_nums[]=$v;
                $acc_nums = array_unique($acc_nums);
            }
        }
        foreach ($acc_nums as $acc_num) {//loop thru account num and query database
            $q = $this->db->select('patient_info.* , study_history.study, prescription_history.prescription')
                 ->from('patient_info')
                 ->join('study_history','patient_info.account_num = study_history.account_num')
                 ->join('prescription_history','patient_info.account_num = prescription_history.account_num')
                 ->where('patient_info.account_num',2)
                 ->get();

            foreach ($q->result_array() as $val) {
                foreach ($val as $k => $v) {
                    switch ($k) {
                        case 'account_num':
                            $acc_num = $v;
                            break;

                        case 'icd9_code':
                            $icd9 = $v;
                            break;

                        case 'dob':
                            $dob = $v;
                            break;

                        case 'sex':
                            $sex = $v;
                            break;

                        case 'study':
                            //$studies = $v;
                            $study[] = $v;
                            $study = array_unique($study);
                            break;

                        case 'prescription':
                            //$scripts = $v;
                            $prescript[] = $v;
                            $prescript = array_unique($prescript);
                            break;

                        default:
                            break;
                    }
                }
            }
            
            //var_dump($study);
            foreach ($prescript as $key => $val) $scripts .= $val.', ';
            foreach ($study as $key => $val) $studies .= $val.', ';

            array_push($arr, array('Acccount Number' => $acc_num,
                                   'ICD9 Code' => $icd9,
                                   'D.O.B' => $dob,
                                   'Sex' => $sex,
                                   'Studies' => substr($studies,0,-2),
                                   'Prescriptions' => substr($scripts,0,-2)));
            unset($study);
            unset($prescripton);
        }
        return $arr;

[eluser]smash120[/eluser]
I figured it out. The problem was here

Code:

foreach ($prescript as $value) $scripts .= $value.', ';
foreach ($study as $value) $studies .= $value.', ';

My variables were still holding the previous values so everytime thru the loop it would append to the existing values so I had to reinitilize these variables and unset my study and prescript arrays in order to display the correct values