CODEWITHSUNDEEP
phpmysqlcodeigniterone-to-many
Muhammad Rashid
Muhammad Rashid

Reputation: 219

Show One To Many Relation Data

I'm facing a problem with displaying records using CodeIgniter. I've done this using pure PHP code but I'm quite confused how can I do it using CodeIgniter.

Doctor         Doctor Specialty     
Rashid         Cardiology, Gastroenterology , Neurology

Controller code:

public function appointmentSixStats(){

  $this->load->view('admin/dashboard/appointment-stats-doctor',$this->data);
}

My table structure:

The problem:

I'm willing to show each doctor record as comma-separated values along with their specialties.

Upvotes: 0

Views: 76

Answers (2)

Kumar V
Kumar V

Reputation: 8830

You can get it from query itself.

Try this code.

    $this->db->select('d.name, GROUP_CONCAT(s.specialty SEPARATOR ",") as doc_specialty'); 
    $this->db->from('tbl_doctor d');   
    $this->db->join('tbl_doctor_specialty ds', 'd.id = ds.doctor_id');
    $this->db->join('tbl_specialty s', 's.id = ds.specialty_id');
    $this->db->group_by("d.id");
    $query = $this->db->get();

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

You can use GROUP_CONCAT(expr) how ever it is not recommended because of character length restriction of default set to 1024 character but it can be increased

$sql="SELECT d.* ,
GROUP_CONCAT(s.title) specialities,
GROUP_CONCAT(a.title) appointments
FROM  tbl_doctor d
JOIN tbl_doctor_specialty ds ON (d.id = ds.doctor_id)
JOIN tbl_specialty  s ON (s.id = ds.speciality.id)
JOIN tbl_appointment  a ON (a.doctor_id =d.id)
GROUP BY d.id";
$result=$this->db->query($sql);
return $result->result();

Upvotes: 2

Related Questions