[eluser]markup2go[/eluser]
Hi I'm trying to join tables in my model to gather directly related data of a result.
I can join tables just fine like this:
Code:
// Selects all jobs, adds category join in jobtype columns
function view_category(){
$this->db->select("jobs.*, categories.name as category_name, categories.slug as category_slug, categories.description as category_description, jobtypes.name as jobtype_name, jobtypes.slug as jobtype_slug, jobtypes.description as jobtype_description", FALSE);
$this->db->where('category_id', $this->uri->segment(4));
$this->db->join('categories', 'jobs.category_id = categories.id', 'left');
$this->db->join('jobtypes', 'jobs.jobtype_id = jobtypes.id', 'left');
$query = $this->db->get('jobs');
if($query->num_rows() > 0) {
return $query->result();
}
}
But now I need to add a where clause into it somehow, on the jobs table only but still joining the other 2.
Something like:
Code:
function get_job(){
// Get one job depending on url
// original query without the joins
//$query = $this->db->query("SELECT * FROM `jobs` WHERE `id` = '".$this->uri->segment(4)."' LIMIT 1");
$this->db->select("jobs.*");
$this->db->where('id', $this->uri->segment(4));
$this->db->select("categories.name as category_name, categories.slug as category_slug, categories.description as category_description, jobtypes.name as jobtype_name, jobtypes.slug as jobtype_slug, jobtypes.description as jobtype_description", FALSE);
$this->db->join('categories', 'jobs.category_id = categories.id', 'left');
$this->db->join('jobtypes', 'jobs.jobtype_id = jobtypes.id', 'left');
$query = $this->db->result("jobs");
if($query->num_rows() > 0) {
return $query->result();
}
}
This obviously isn't working and I'm pretty frustrated so I figured I would ask and see if anyone can correct me in the right direction.
Thanks!