Welcome Guest, Not a member yet? Register   Sign In
Join table help

Hello guys, I need your help. I'm a beginner in CI and have been working on a web application for starters when I stumble upon this problem...

I want to get data from 2 tables with the relationship being the acct_id and base the results according to the status of trouble.troublestat (open, close, pending). Here is my function inside the model.

function getUnlockList(){
    $this->db->select('account.fname, account.lname, account.mname, trouble.troubletable, trouble.troublemsg, trouble.troublestat');
    $this->db->join('account', 'account.acct_id = trouble.acct_id', 'left');
    $this->db->where('trouble.troublestat', 'open');
    $q = $this->db->get();
    return $q->result();

It says I have error in my sql, can somebody please show me the right direction here? Smile


By the way here is the error I am getting.

Quote:A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN `account` ON `account`.`acct_id` = `trouble`.`acct_id` WHERE `trouble`' at line 2

SELECT `account`.`fname`, `account`.`lname`, `account`.`mname`, `trouble`.`troubletable`, `trouble`.`troublemsg`, `trouble`.`troublestat` LEFT JOIN `account` ON `account`.`acct_id` = `trouble`.`acct_id` WHERE `trouble`.`troublestat` = 'open'

You haven't specified your first table. Now you have only SELECT ... LEFT JOIN ... WHERE ...
The 'from' part is missing!!
It should be:
$this->db->join('second_table', 'join conditions', 'left');

Thanks so much mddd! That made it work and now I know. Smile

Theme © iAndrew 2016 - Forum software by © MyBB