CodeIgniter Forums

Full Version: retrieving data from three tables
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[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'];

?>