Problems with Active Record

[eluser]ekarey[/eluser]
I have 2 functions in my model called by my controller within the same class and it seems that when I run get_game_count() after running get_all_games() it still uses the db->select, db-join and db-limit from the previous function call. What am I doing wrong? Thanks!

Code:

function get_all_games($page='1', $sterm='')
    {
        $startpoint = ($page-1)*20;
    
        if(!empty($sterm))
        {
            $this->db->like('title', $sterm);
        }
        
        $this->db->select('g.game_id game_id, g.title game_title, g.platform_id platform_id, p.name platform');
           $this->db->join('gameplatforms p', 'p.platform_id = g.platform_id');
        
        $this->db->limit(20, $startpoint);
        
        $query = $this->db->from('gamedb g');
        
        return $query;
    }
    
    function get_game_count($sterm = '')
    {
        if(!empty($sterm))
        {
            $this->db->like('title', $sterm);
        }
        
        $this->db->get('gamedb');
        
        return $this->db->count_all_results();
    }

[eluser]TheFuzzy0ne[/eluser]
Welcome to the CodeIgniter forums!

You need to call $this->db->get() to finalise the query.

[eluser]ekarey[/eluser]
Would you mind providing an example of how I would use that with the above functions? I tried a few placements and still generated errors?

Thanks very much!

[eluser]TheFuzzy0ne[/eluser]
This is where you're going wrong:

Code:

$query = $this->db->from('gamedb g');

$this->db->from() does not return anything, nor does it initiate the query. It's just a method to help build the query.

What you need is this:

Code:

$query = $this->db->get();

$this->db->get() is what executes the query and returns toe result object.

So here's the revised function (untested):

Code:

function get_all_games($page='1', $sterm='')
{
    $startpoint = ($page-1)*20;
  
    if(!empty($sterm))
    {
        $this->db->like('title', $sterm);
    }
        
    $this->db->select('g.game_id game_id, g.title game_title, g.platform_id platform_id, p.name platform');
    $this->db->join('gameplatforms p', 'p.platform_id = g.platform_id');
        
    $this->db->limit(20, $startpoint);
        
    $this->db->from('gamedb g');
        
    return $this->db->get(); # Return the query results.
}

Hope this helps. If in doubt, [url="http://ellislab.com/codeigniter/user-guide/database/active_record.html"]RTFUG[/url].

[eluser]ekarey[/eluser]
I've certainly been reading the user guide religiously....great resource for sure.

Now I am getting this:

Code:

Error Number: 1054

Unknown column 'jpp_g.platform_id' in 'on clause'

SELECT `g`.`game_id` game_id, `g`.`title` game_title, `g`.`platform_id` platform_id, `p`.`name` platform FROM (`jpp_gamedb` g) JOIN `jpp_gameplatforms` p ON `p`.`platform_id` = `jpp_g`.`platform_id` LIMIT 20

Thanks!

[eluser]TheFuzzy0ne[/eluser]
It's hard to know what the problem is without knowing your table schema, however, I've noticed a comma missing from your select statement. I doubt it will fix the problem, though, but it will definitely be the next problem that needs fixing.

Code:

$this->db->select('g.game_id, game_id, g.title game_title, g.platform_id, platform_id, p.name, platform');
       # inserted comma here^             ...                  and here ^    ...    and here ^

EDIT: Scrap that. It's 03:08AM and I need caffeine. If you can show me your database schema, I can help you further

[eluser]ekarey[/eluser]
Yeah thanks for that catch. The issue I have now is that in the Join it is adding my table prefix to the g which is the alias for gamedb for some reason. Do aliases not work with get?

[eluser]TheFuzzy0ne[/eluser]
Please ignore my last comments. I didn't realise you were using aliases. I thought you had to use AS in the select statement... Put it back the way it was, and pass FALSE into the select function as the second parameter, it will prevent escaping.

[eluser]TheFuzzy0ne[/eluser]
OK, I'm going to go and put the kettle on. Let's start afresh. Please paste the code you have now, and I'll look at it shortly.

[eluser]ekarey[/eluser]
Here is what I have now:

Code:

function get_all_games($page='1', $sterm='')
    {
        $startpoint = ($page-1)*20;
    
        if(!empty($sterm))
        {
            $this->db->like('title', $sterm);
        }
        
        $this->db->select('g.game_id game_id, g.title game_title, g.platform_id platform_id, p.name platform', FALSE);
           $this->db->join('gameplatforms p', 'p.platform_id = g.platform_id');
        
        $this->db->limit(20, $startpoint);
        
        $this->db->from('gamedb g');
        
        return $this->db->get();
    }
    
    function get_game_count($sterm = '')
    {
        if(!empty($sterm))
        {
            $this->db->like('title', $sterm);
        }
        
        $this->db->get('gamedb');
        
        return $this->db->count_all_results();
    }

Still getting the same error, its creating the query:

JOIN jpp_gameplatforms p ON p.platform_id = jpp_g.platform_id

where it should be:

JOIN jpp_gameplatforms p ON p.platform_id = g.platform_id