Greg Aker :: Blob :: DB Results in ExpressionEngine and CodeIgniter

DB Results in ExpressionEngine and CodeIgniter

June 9, 2011

I learned PHP by forcing myself to try to create ExpressionEngine addons. I studied the 1.x code. I poured over the CodeIgniter core, trying to figure out how the heck it worked. One thing that confused me was how the heck do I get a single result from an active record query! If you're in this boat, I hope this might help clear it up for you a bit.

In ExpressionEngine 2.x, unless you're doing something extremely complex, using the CodeIgniter Active Record class is the way to go.

For instance, if we want to do a query to get members and their associated member group info, then loop through the results, we'd do:

<?php

$qry = $this->EE->db->select('m.member_id, m.group_id, m.username, m.screen_name, m.email, m.url, m.location, g.group_title')
                    ->from('members m')
                    ->join('member_groups g', 'g.group_id = m.group_id')
                    ->where('g.site_id', $this->EE->config->item('site_id'))
                    ->get();

if ($qry->num_rows() !== 0)
{
    foreach ($qry->result() as $row)
    {
        echo 'Member Id: ' . $row->member_id . '<br>';
        echo 'Member Group: ' . $row->group_title . '<br>';
        echo 'Screen Name: ' . $row->screen_name . '<br>';
        echo 'Username: ' . $row->username . '<br>';
        echo 'Email: ' . $row->email . '<br>';
        echo 'URL: ' . $row->url . '<br>';
        echo 'Location: ' . $row->location . '<br>';
        echo '<br><br>';
    }
}

In a test EE installation, I get the following HTML returned from that:

Member Id: 2
Member Group: Members
Screen Name: dude_fella_0
Username: dude_fella_0
Email: dudefella_0@example.com
URL: <br>Location:

Member Id: 3
Member Group: Members
Screen Name: dude_fella_1
Username: dude_fella_1
Email: dudefella_1@example.com
URL: 
Location:

So through that loop, CodeIgniter has put the results into objects that you access like $var->query_column. To get a better idea as to what is going on, tweak your code to look like this.

<?php

$qry = $this->EE->db->select('m.member_id, m.group_id, m.username, m.screen_name, m.email, m.url, m.location, g.group_title')
                    ->from('members m')
                    ->join('member_groups g', 'g.group_id = m.group_id')
                    ->where('g.site_id', $this->EE->config->item('site_id'))
                    ->get();

var_dump($qry->result()); exit;

Generally speaking, looping through a result set is easy. When I was starting out, for some reason I hit a huge roadblock with getting a single result. So let's see how that works.

<?php

$qry = $this->EE->db->select('m.member_id, m.group_id, m.username, m.screen_name, m.email, m.url, m.location, g.group_title')
                    ->from('members m')
                    ->join('member_groups g', 'g.group_id = m.group_id')
                    ->where('m.member_id', 2)
                    ->where('g.site_id', $this->EE->config->item('site_id'))
                    ->get();

The query looks basically the same, but there's nothing we want to be looping through, since we should only have one result. This is where the row() method comes into play.

// Continued after the last query.

<p>Greetings <?php echo $qry->row('username');?>, how are you today?</p>

<p>Our records indicate your email address is <?php echo $qry->row('email'); ?>. Do you need to update it?

As you can see, it's just as easy to deal with a single result!