Welcome Guest, Not a member yet? Register   Sign In
or_like problem
#1

[eluser]webnology[/eluser]
Hi all,

I'm having this model to search the db for a specific search_term. If I only search in the firstname column, it works fine, If I add or_like for the lastname column, my other where statement seem to be ignored.

Can anyone help me out here, please?

Code:
function getAllCustomersLikeSearchTerm($search_term, $user_id)
    {
        $this->db->select('*');
        $this->db->from('customers');
        $this->db->like('firstname', $search_term, 'both');
        $this->db->or_like('lastname', $search_term, 'both');
        $this->db->order_by('firstname', 'asc');
        $this->db->where('user_id', $user_id);
        $this->db->where('flag_sleep', '0', FALSE);
        
        $Q = $this->db->get();
        return $Q;
    }

Kind regards,
Michel
#2

[eluser]Burak Guzel[/eluser]
Because it generates a query like this:

Code:
SELECT * FROM customers WHERE firstname LIKE '%%' OR lastname LIKE '%%' AND user_id = '' AND flag_sleep = ''

AND has a higher precedence than OR , so it gets treated like this:

Code:
SELECT * FROM customers WHERE (firstname LIKE '%%') OR (lastname LIKE '%%' AND user_id = '' AND flag_sleep = '')

Not very experienced with the Active Record Class, so I'm not sure what the best solution would be to group your OR conditions.
#3

[eluser]mah0001[/eluser]
I don't think you can combine the AND and OR the way you want using the Active Record. The alternate is:
Code:
$sql = "SELECT * FROM {$this->db->dbprefix}customers WHERE user_id = ? AND flag_sleep=? AND (firstname like ? OR lastname like ?) order by firstname asc";
$Q=$this->db->query($sql, array($user_id,0, '%'.$search_term.'%', '%'.$search_term.'%'));
return $Q;
#4

[eluser]webnology[/eluser]
Ok, thx for the help guys. I'll give it a go with other statements.

M
#5

[eluser]Burak Guzel[/eluser]
If there is no simpler way of doing it, maybe this could work too:

Code:
function getAllCustomersLikeSearchTerm($search_term, $user_id)
    {
        $this->db->select('*');
        $this->db->from('customers');
        
        $this->db->like('firstname', $search_term, 'both');
        $this->db->where('user_id', $user_id);
        $this->db->where('flag_sleep', '0', FALSE);
        
        $this->db->or_like('lastname', $search_term, 'both');
        $this->db->where('user_id', $user_id);
        $this->db->where('flag_sleep', '0', FALSE);
        
        $this->db->order_by('firstname', 'asc');

        $Q = $this->db->get();
        return $Q;
    }




Theme © iAndrew 2016 - Forum software by © MyBB