New to mysql joins and having problems joining 3 tables.

New to mysql joins and having problems joining 3 tables. - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: New to mysql joins and having problems joining 3 tables. (/showthread.php?tid=15040)

Pages: 1 2

New to mysql joins and having problems joining 3 tables. - El Forum - 01-24-2009

[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

New to mysql joins and having problems joining 3 tables. - El Forum - 01-24-2009

[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.

New to mysql joins and having problems joining 3 tables. - El Forum - 01-24-2009

[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."))";

New to mysql joins and having problems joining 3 tables. - El Forum - 01-24-2009

[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);

New to mysql joins and having problems joining 3 tables. - El Forum - 01-24-2009

[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.

New to mysql joins and having problems joining 3 tables. - El Forum - 01-24-2009

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

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

New to mysql joins and having problems joining 3 tables. - El Forum - 01-24-2009

[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 !

New to mysql joins and having problems joining 3 tables. - El Forum - 01-24-2009

[eluser]betoranaldi[/eluser]
[quote author="mandril" date="1232848988"]the problem is i misstypes the last " Tongue

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);

New to mysql joins and having problems joining 3 tables. - El Forum - 01-24-2009

[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);

New to mysql joins and having problems joining 3 tables. - El Forum - 01-24-2009

[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