Pulling/joining information from another table.

[eluser]Unknown[/eluser]
Hello there, rather new to CI, and have been trying to join/call information from another table to another.


The table that the information is in is called users of which we would like to pull out the username and email rows, and put them into a table called newsfeed which is called multiple times in my model/controller.


We would like to be able to call/associate the username and email of the users table with the newsfeed table.

Currently our model file for the try at joining the tables is:


Model file:

Code:

function join_data()
    {
        $this->db->select('username,
                   email');
             $this->db->from('users');
                   $this->db->join('newsfeed', 'username= author_id');
             $q = $this->db->get();
    }

Don't have a strong grasp on what the controller or view call should be as most tutorials i've tried on this manner did not work well for me.

If you need any additional information I'll try to provide what I can.

Any help would be greatly appreciated!

Cheers,
Whey.

[eluser]CrossMotion[/eluser]
Just like in native SQL querys, you must put the fields from both tables that you want to retrieve in the select statement. Also you dont want to add data from the newsfeed table to the users table, but the other way around. Your query should look something like this:

Code:

function join_data()
    {
        $this->db->select('n.*, u.username, u.email');
        $this->db->from('newsfeed n');
        $this->db->join('users u', 'username= author_id', 'left');
        $q = $this->db->get();

        return $q->result();
    }

Also, are you sure that author_id and username contain the same data? since author_id suggests an record ID (intenger), and username suggests a nickname.