MySQL Codeigniter - $this->db->query and CLI results do not match

Question

I am using this query to return search results

    $id = $this->auth->get_user_id();

    $query = $this->db->query("
    SELECT user_id,
           facebook_id,
           first_name,
           last_name,
           country,
           state,
           city,
           picture,
           cars_id,
           cars_name,
           cars_picture
    FROM   user_profiles
           JOIN users
             ON users.id = user_id
           JOIN cars
             ON cars.id_fk = user_id
    WHERE  user_id NOT LIKE '$id'
           AND activated = 1
           AND banned = 0
           AND first_name LIKE '%$search_term%'
            OR last_name LIKE '%$search_term%'
            OR Concat(first_name, ' ', last_name) LIKE
               '%$search_term%'            
    ORDER BY last_name ASC; 
    ");

If I click on an empty search box it return all users, if I put in a first name or last name it returns only that specific user as search result. Using command line, this query gives similar results when manually entering (or leaving blank) $id, $first_name, etc.

BUT, here's the strange behavior: the following lines seem to have no effect on the query:

user_id NOT LIKE '$id' //line 1
  AND activated = 1
  AND banned = 0

For example, I am not able to exclude the current user from search results (line 1). No matter what I put in for activated or banned, clicking on a blank search always returns all users.

The kicker is that if I run this SQL on command line and manually enter

user_id NOT LIKE '5'
   AND activated = 1
   AND banned = 0

it filters the results correctly.

Does anyone know what I'm doing wrong? Thanks for helping!

Christopher Armstrong · Accepted Answer · 2011-05-28 13:19:43Z

3

I think the ORs aren't binding to the last AND. Try this:

AND (first_name LIKE '%$search_term%'
        OR last_name LIKE '%$search_term%'
        OR Concat(first_name, ' ', last_name) LIKE
           '%$search_term%' )

answered May 28, 2011 at 13:19

Christopher Armstrong

7,9812 gold badges28 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ypercubeᵀᴹ Over a year ago

@torr: thnx, but actually @Christopher answered 26 seconds before I did :)

ypercubeᵀᴹ · Accepted Answer · 2011-05-28 13:44:53Z

The reason for this behaviour has to do with mixing AND and OR in a condition and with the precedenxe order of these two.

When you have a condition like:

WHERE a
  AND b
  AND c
  AND d
  AND e
  AND f

or like

WHERE a
   OR b
   OR c
   OR d
   OR e
   OR f

there is no need for parenthesis. However you add parenthesis, the result does not change.

But when you have this one:

WHERE a
  AND b
  AND c
   OR d
   OR e
   OR f

(because AND has higher precedence than OR), it is translated as:

WHERE ( a
       AND b
       AND c
       AND d
      )
   OR e
   OR f

where you may want something different, like:

WHERE a
  AND b
  AND c
  AND ( d
     OR e
     OR f
      )

So, I think you need:

WHERE  user_id NOT LIKE '$id'
       AND activated = 1
       AND banned = 0
       AND ( first_name LIKE '%$search_term%'
          OR last_name LIKE '%$search_term%'
          OR Concat(first_name, ' ', last_name) LIKE
             '%$search_term%'    
           )

Collectives™ on Stack Overflow

MySQL Codeigniter - $this->db->query and CLI results do not match

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related