CodeIgniter Forums
Active record WHERE clause not working? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Active record WHERE clause not working? (/showthread.php?tid=39336)

Pages: 1 2 3

Active record WHERE clause not working? - El Forum - 03-08-2011

[eluser]danmontgomery[/eluser]
You can try get_where():

Code:
$q = $this->db->get_where('content', array('url' => $url));

You can also try $this->db->last_query() to see the query:

Code:
$this->db->where('url', $url);
$q = $this->db->get('content');
echo $this->db->last_query();


Active record WHERE clause not working? - El Forum - 03-08-2011

[eluser]xarazar[/eluser]
Thanks noctrum.
I can't understand why but get_where() does work. But guess what - last_query() returns null...
How is it even possible?

Update:

I think I may have a hint. When I moved the entire database interaction to my controller everything works like expected. The problem begins when I move the database query to my model.
Does this help? Anyone?

Active record WHERE clause not working? - El Forum - 03-09-2011

[eluser]xarazar[/eluser]
Just to clarify this is the exact setup of my app:

Code:
class Site extends CI_Controller{
    function index(){
        $this->output->enable_profiler(TRUE);
        $q = $this->db->where('url', 'home');
        $q = $this->db->get('content');
        print_r($q->result());
    }
}
The above works as expected, prints out one record from the database, the profiler shows the executed query.

When I change the setup to:

Code:
class Site extends CI_Controller{
    function index(){
        $this->output->enable_profiler(TRUE);
        print_r($this->site_model->get_page('home'));
    }
}


class Site_model extends CI_Model{
    
    function get_page($url = 'home'){
        $q = $this->db->where('url',  $url);
        $q = $this->db->get('content');
        return $q->result();
    }
}

The script prints out all the records from the table = the WHERE clause doesn't work.

When I modify my model to use get_where like this:
Code:
class Site_model extends CI_Model{
    function get_page($url = 'home'){
        $q = $this->db->get_where('content',  array('url' => $url));
        return $q->result();
    }
}

everything seems to be working fine.

I'm this close to going back to CI 1.7.3 so any help or suggestions will be greatly appreciated.

Active record WHERE clause not working? - El Forum - 03-09-2011

[eluser]InsiteFX[/eluser]
This is how I use where in my blog model and it works for me, not sure if it will help you.

Also if using CI 2.0 make sure you download the latest version from the Reactor.

Code:
function get_post($id)
{
    $data = array();

    $this->db->where('id', $id);
    $this->db->limit(1);

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

    if ($query->num_rows() > 0)
    {
        $data = $query->row_array();
    }

    $query->free_result();
    return $data;
}

InsiteFX

Active record WHERE clause not working? - El Forum - 03-09-2011

[eluser]xarazar[/eluser]
Thanks InsiteFX.
To be perfectly honest I have the feeling that you're trying to explain to me how to use WHERE whereas my problem lies in the fact that any database queries run in my model don't seem to accept the WHERE clause unless I put my queries within the controller (bad idea) or use get_where() which is fine but I shouldn't have to.
I have the latest version of CI.

So the questions are:

1. Why does WHERE work in the controller and doesn't work in the model?
2. Why get_where() works fine (in the model) whereas where() doeasn't?

Active record WHERE clause not working? - El Forum - 03-09-2011

[eluser]danmontgomery[/eluser]
Do you have a constructor in the model you just haven't posted? Are you calling parent::__construct(); in all of your controller and model constructors (if they exist)?

I don't see a good reason for get_where() to work and where() not to. get_where() just calls where().

Code:
function get_where($table = '', $where = null, $limit = null, $offset = null)
{
    if ($table != '')
    {
        $this->from($table);
    }

    if ( ! is_null($where))
    {
        $this->where($where);
    }
// ... etc


Active record WHERE clause not working? - El Forum - 03-09-2011

[eluser]xarazar[/eluser]
Hi noctrum,
I get your point. There is no reason why one would work and the other wouldn't.
But I just tested something. I replaced this:

Code:
$this->db->where('url',  $url);
$q = $this->db->get('content');

with this:

Code:
$q = $this->db->where('url', $url)->get('content');

and it works fine.

So I narrowed it down but I can't explain why it happens. Any ideas?
Multi-line Active Records queries are not being assembled correctly?

Active record WHERE clause not working? - El Forum - 03-09-2011

[eluser]danmontgomery[/eluser]
What version of PHP?

Active record WHERE clause not working? - El Forum - 03-09-2011

[eluser]xarazar[/eluser]
Thought about it... it's 5.2.9

Active record WHERE clause not working? - El Forum - 03-09-2011

[eluser]InsiteFX[/eluser]
Also what MySQL database version are you running?

InsiteFX