Active Record Join question

[eluser]Ty Bex[/eluser]
How do I display the variables from the membership table.

USERS (4 records)
--------------------
id
userid
name
memberships


MEMBERSHIP (10 records)
--------------------------
id
order
type
level

CONTROLLER

Code:

function officers ()
    {
        $data['main']            = "/frontend/v_club.php";
        $data['title']            = "Officers";
        $data['members']        = $this->M_Club->getofficers();
        $this->M_Template->call_view($data);
    }

MODEL

Code:

function getofficers(){
        $this->db->select('users.*', 'membership.order', 'membership.type', 'membership.level', FALSE);
        $this->db->where('users.active', 1);
        $this->db->where('membership.type', 'officer');
        $this->db->from('users');
        $this->db->join('membership', 'membership.id = users.memberships', 'LEFT OUTER');
        $this->db->order_by('membership.order', 'desc');
        $Q = $this->db->get();
        if ($Q->num_rows() > 0){
            return $Q;
        }else{
            $this->session->set_flashdata('error', 'No Home Information has been defined. Please check DB');
        }
    }

VIEW

Code:

foreach($members->result() as $row)
    {?>
        <div id="news_container">
            <div class="news_title">&lt;?=$row->name?&gt; &lt;?=$row->order?&gt; &lt;?=$row->level?&gt;</div>
        </div>
&lt;?}

RESULT
User One
A PHP Error was encountered Message: Undefined property: stdClass::$membership
A PHP Error was encountered Message: Undefined property: stdClass::$order
User Two
A PHP Error was encountered Message: Undefined property: stdClass::$membership
A PHP Error was encountered Message: Undefined property: stdClass::$order

[eluser]mah0001[/eluser]
Do you actually have a field active in the users table?

Code:

$this->db->where('users.active', 1);

Do a var_dump after the line:

Code:

$Q = $this->db->get();
var_dump($Q);

I don't think your query is actually returning anything. Try the var_dump and post the results.

[eluser]Ty Bex[/eluser]
I am getting the Name and I am getting the 4 records.. I am jsut not getting any of the membership information, such as level or type.. The thing is that the order by works correctly so I know I am getting the query to work. I will show the results of you suggestion when I get back to the office.

[eluser]mah0001[/eluser]
Also it would be helpful if you could post your users and membership tables schema again as for now it does not match the columns you are using in the AR query. Look at the join statement, you are using the users.memberships while under the user table definition the column is named membership not memberships.

Code:

$this->db->join('membership', 'membership.id = users.memberships', 'LEFT OUTER');
//users.memberships should be [b]users.membership[/b] as per your users table definition given by you at the top of the post

[eluser]Ty Bex[/eluser]
Ok I figured out what I was doing..

1.) Look at my query.

Code:

print $this->db->last_query();

2.) Query was this

SELECT `auth_users`.* FROM (`auth_users`) LEFT OUTER JOIN `membership` ON `membership`.`id` = `auth_users`.`memberships` WHERE `auth_users`.`active` = 1 AND `membership`.`type` = 'officer' ORDER BY `membership`.`order` asc


From

Code:

$this->db->select('auth_users.*', 'membership.order', 'membership.type', 'membership.level', FALSE);

3.) The issue is that my SELECT is not showing my membership.<field> values.

4.) The fix was removing the quotes around each field and have all the fields in one quote.

Code:

$this->db->select('auth_users.*, membership.order, membership.type, membership.level', FALSE);

Now Produces the following query.

SELECT auth_users.*, membership.order, membership.type, membership.level FROM (auth_users) LEFT OUTER JOIN membership ON membership.id = auth_users.memberships WHERE `auth_users`.`active` = 1 AND `membership`.`type` = 'officer' ORDER BY membership.order asc