New to mysql joins and having problems joining 3 tables.

[eluser]betoranaldi[/eluser]
I am familiar with mysql queries but new to joins and ActiveRecord.

I am trying to join 3 tables. I can get two of them to join without a problem but once I join the third, it returns no results (from what I can see.)

Code:

$this->db->select('*');
$this->db->from('jobs');
$this->db->join('requestors', 'requestors.id = jobs.requestor');
$this->db->where('jobs.assigned', $this->session->userdata('user_id'));
$this->db->limit(10, $os);
$data ['query'] = $this->db->get();

This returns all rows assigned to a specific user and gives me access to the first name/last name columns of the requestors table.

Now when I add the join to the flow table (to replace numbers found in the flow column of the jobs table) something breaks and I get no results. I don't get any php or mysql warnings.

Code:

$this->db->select('*');
$this->db->from('jobs');
$this->db->join('requestors', 'requestors.id = jobs.requestor');
$this->db->join('flow', 'flow.id = jobs.flow');
$this->db->where('jobs.assigned', $this->session->userdata('user_id'));
$this->db->limit(10, $os);
$data ['query'] = $this->db->get();

What am I missing or doing wrong? Is there any resource for CI and ActiveRecord Joins?

Thanks
Brian

[eluser]davidbehler[/eluser]
Have you tried doing the query the old fashioned way

Code:

$result = $this-db->query("SELECT * FROM ...");

instead of using active record?

Try getting that to work.

[eluser]mandril[/eluser]
it would be easier if you do this:

Code:

$result = $this-db->query("SELECT * FROM
                          jobs,requestors,flow
                          WHERE requestors.id = jobs.requestor
                          AND flow.id = jobs.flow
                          AND jobs.assigned = ".$this->session->userdata('user_id')."
                          LIMIT (10".$os."))";

[eluser]betoranaldi[/eluser]
Thank you both for your responses.

I was under the impression ActiveRecord was the preferred method to use, is there any resource difference between a regular query and AR, or does it not matter?

[quote author="mandril" date="1232843254"]it would be easier if you do this:

Code:

$result = $this-db->query("SELECT * FROM
                          jobs,requestors,flow
                          WHERE requestors.id = jobs.requestor
                          AND flow.id = jobs.flow
                          AND jobs.assigned = ".$this->session->userdata('user_id')."
                          LIMIT (10".$os."))";

[/quote]

I understood mandril's query, which was a great example (thanks!) but I think there is something wrong with it. When I enter it into my controller as follows, the page doesn't render at all (it is just blank.) I also tried pasting the code above as is without changing it all to one line and get the same results.

Code:

$query = $this-db->query("SELECT * FROM jobs,requestors,flow WHERE requestors.id = jobs.requestor AND flow.id = jobs.flow AND jobs.assigned = ".$this->session->userdata('user_id')." LIMIT (10".$os."))";

$this->load->view('jobs/queue', $query);

[eluser]davidbehler[/eluser]
try this

Code:

$result = $this-db->query("SELECT * FROM jobs,requestors,flow WHERE requestors.id = jobs.requestor AND flow.id = jobs.flow AND jobs.assigned = ".$this->session->userdata('user_id')." LIMIT (10".$os."))";

if($result->num_rows() > 0)
{
  $rows = false;
}
else
{
  $rows = $result->result_array();
}

$this->load->view('jobs/queue', $rows);

The query method returns a result object and can not be used e.g. in a foreach loop.

[eluser]mandril[/eluser]
the problem is i misstypes the last "

Code:

$result = $this-db->query("SELECT * FROM jobs,requestors,flow WHERE requestors.id = jobs.requestor AND flow.id = jobs.flow AND jobs.assigned = ".$this->session->userdata('user_id')." LIMIT 10,".$os);

try it now... and tell us.


greetings

[eluser]mandril[/eluser]
or you can try this

Code:

$result = $this-db->query("SELECT * FROM jobs,requestors,flow WHERE requestors.id = jobs.requestor AND flow.id = jobs.flow AND jobs.assigned = '".$this->session->userdata('user_id')."' LIMIT 10,".$os);

with the '' in the where !

[eluser]betoranaldi[/eluser]
[quote author="mandril" date="1232848988"]the problem is i misstypes the last "
try it now... and tell us.
greetings[/quote]

Thanks mandril, but I am afraid it still is not working.

Nothing outputs to the browser so I am assuming my syntax is still wrong. If I comment out this line, the browser renders the page without results, so I know this is the problem.

This is my exact line, I have looked it over and over but don't see anything wrong (but I gess that doesnt mean much when I also missed your " typo.

Code:

$data = $this-db->query("SELECT * FROM jobs,requestors,flow WHERE requestors.id = jobs.requestor AND flow.id = jobs.flow AND jobs.assigned = ".$this->session->userdata('user_id')." LIMIT 10,".$os);

[eluser]betoranaldi[/eluser]
Also tried this with no luck

Code:

$data = $this-db->query("SELECT * FROM jobs,requestors,flow WHERE requestors.id = jobs.requestor AND flow.id = jobs.flow AND jobs.assigned = '".$this->session->userdata('user_id')."' LIMIT 10,".$os);

[eluser]mandril[/eluser]
try joining a simple one.. when you face a problem you have to test small things.

try joinin 2 tables.


and put ' ' in the WHERE statements, like this.


WHERE name = 'mandril'


good luck, the sql seems correct.. maybe some DB problems