I have a search form which is used by staff to search for a user account. I want the search form to take the input from the user and search the DB for a user that matches it. Here's my DB structure...
Users Table -> (id, fname, lname, ip_address....etc.)
Here is what I'm using in my model to search for the user...
$this->db->or_like('fname', $query);
$this->db->or_like('lname', $query);
$this->db->or_like('email', $query);
$this->db->or_where('ip_address', $query);
$query = $this->db->get('users');
This works great for most searches. For example....
Search: Bill
returns all users with a first name or last name of bill.....
However, a search for a first and last name doesn't return a user even if there is a user that matches. Example....
Search: Bill Gates
That search doesn't return the user with the fname of Bill and the lname of Gates. What can I do to make this work?
You'll have to massage the search term into multiple individual terms. CI will write the query as:
WHERE fname LIKE '%Bill Gates%' OR lname LIKE '%Bill Gates%' etc...
which will never match, since 'bill gates' never appears in a single field in the database. But if you split it into multiple words, (Bill and Gates) and add each word to the or set individually, you will get matches.
The other option, if you're using MyISAM tables, is to use a fulltext index across the 3 fields, which would find 'bill gates', even if bill is one field and gates is in the other.
Apparently fulltext support for InnoDB is now actually finally in the development pipeline, but for now it's still myisam only in production code.