CodeIgniter Forums
retrieving data from three tables - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: retrieving data from three tables (/thread-53239.html)



retrieving data from three tables - El Forum - 07-16-2012

[eluser]ozy123[/eluser]
Hi guys,

I have three tables as below:

bookings
--------
idbook
store_id
status


stores
--------
store_id
store_address


applicants
--------
idbook
id


I want to display every booking row where 'status' = 2 and for each of these rows display the 'id' from the applicants table(there will be multiple ids attached to each row). This is how my data should look in my view.


idbook : 1
store_id : 2
status: 2
id : 2/3/4

Any idea how I can do this? Using the code below I can retrieve every row but it displays a new row for each idbook and id i.e.

idbook store_id status id

1 11 2 2
1 11 2 3
1 11 2 4


Controller
------------------------------------------------
$data['applications'] = $this->booking_model->get_applications($userid);
$this->load->view('M_pending', $data);


Model - get_applications
------------------------------------------------
public function get_applications($userid)
{
$query = $this->db->query("select * from booking, stores, users_apply
where booking.store_id = stores.store_id
and users_apply.idbooking = booking.idBook
and cid = '$userid'
and status = 2");
return $query->result_array();
}



View - M_pending
-----------------------------------------------
<?php foreach ($applications as $row): ?>

<?php

echo $row['idBook'];
echo $row['storeid'];
echo $row['status'];
echo $row['id'];

?>