I am developing an API built with PHP and Codeigniter and I use MySQL as database.
I have a user table called Users which contains the following columns:
- ID - Firstname - Lastname - Email
How can a user search this table using a full name? For example it works fine to search either by Martin or Smith, but not Martin Smith.
This is the query I am using now:
$this->db->select('*');
$this->db->from('users');
$this->db->like('firstname', $args['query']);
$this->db->or_like('lastname', $args['query']);
Update
If I use this query as suggested below I cannot find anything. Not with full name, first name or last name. Then both first name AND last name needs to be the same word.
$this->db->select('*');
$this->db->from('users');
$this->db->like('firstname', $args['query']);
$this->db->like('lastname', $args['query']);
Advertisement
Answer
Straight out of my head, but what about
$input = 'Martin Smith';
$names = explode($input);
$this->db->select('*');
$this->db->from('users');
$this->db->where_in('firstname', $names);
$this->db->or_where_in('lastname', $names);
Should output something like:
SELECT * FROM users
WHERE firstname IN ('Martin', 'Smith')
OR lastname IN ('Martin', 'Smith')