• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Joins and error handling

#1
[eluser]tim1965[/eluser]
Hi

Hopefully this will be an easy answer. I have a model, and a function that selects across three tables
Quote:
master_charges
master_property_details
master_contactdetails
Quote:
This is the function
function get_property_details()
{
$propid = $this->session->userdata('propid');
$property_id = $propid['prop_id'][0];

$this->db->from('master_property_details');
$this->db->join('master_charges', 'master_property_details.property_id = master_charges.property_id');
$this->db->join('master_contactdetails', 'master_property_details.property_id = master_contactdetails.property_id');
$this->db->where('master_property_details.property_id',$property_id, FALSE);
$query = $this->db->get();
if($query->num_rows() >0)
{
$row=$query->row_array();
return $row;
}
}
It uses the proeprty_id to select off of each table and join the results together works well.
However as part of my testing i simulated one of the tables not returing a query result for the select i.e. of the three tables only two returned result sets for the query. This caused a problem in the view with no data being displayed. i.e. only if all 3 result sets are returned will the view display all data correctly. If all 3 are not present then it doesnt return any data to the view.
So my question is how can i amend my model to handle the fact that not all resultsets may be present, but still correclty apply those that are to the view.
If the above isnt clear then please let me knoew and i will amend the post.
Let me know if the above isnt clear and i will amend post if neccessary.
Many thanks in advance.

#2
[eluser]davidbehler[/eluser]
Just change
Code:
$this->db->join('master_charges', 'master_property_details.property_id = master_charges.property_id');
$this->db->join('master_contactdetails', 'master_property_details.property_id = master_contactdetails.property_id');
to
Code:
$this->db->join('master_charges', 'master_property_details.property_id = master_charges.property_id', 'left outer');
$this->db->join('master_contactdetails', 'master_property_details.property_id = master_contactdetails.property_id', 'left outer');

That way your join will be an outer join and return results even though master_charges or master_contactdetails is empty.

#3
[eluser]tim1965[/eluser]
Perfecto

Many thanks for your help.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.