CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
Random
Random

Reputation: 97

CodeIgniter/MySQL - select, where not equal to and like twice

I am having problems returning the correct records when using the following active record query in CodeIgniter:

$q = $this->db->query("SELECT fName, lName, userID 
                       FROM user 
                       WHERE userID !='$user_id'
                       AND fName LIKE '%$search_criteria%' 
                       OR lName LIKE '%$search_criteria%'");

The query searches the users table based on $search_criteria. $user_id is the ID of the logged in user so they don't appear in the search results.

If I search for the logged in users first name it doesn't return the logged in user in the search results, but it does return the logged in user if i search by their last name, which I don't understand why...

Upvotes: 0

Views: 853

Answers (1)

Brad Faircloth
Brad Faircloth

Reputation: 327

There is a flaw in the logic, you want to check user ID, and check for either first name OR last name... What I did here was look for userID AND fname or lname. The parens separate the criteria, allowing either fname or lname but always ID

$q = $this->db->query("SELECT fName, lName, userID 
                       FROM user 
                       WHERE userID !='$user_id'
                       AND (fName LIKE '%$search_criteria%' 
                       OR lName LIKE '%$search_criteria%'"));

Upvotes: 1

Related Questions