In the application I’m working on, CJuiAutoComplete is used to search for people using their first and last names. I had the SQL statement setup as follows:
WHERE first_name LIKE :term AND last_name LIKE :term
This worked fine until I did some usability testing. I found that a lot of users typed in the first AND last name, without looking at the results that were already showing. The SQL statement above worked until they started to type in the last name.
To fix this, I used the MySQL CONCAT_WS function:
CONCAT_WS(' ',first_name,last_name) LIKE :term
I used CONCAT_WS instead of CONCAT because this added space inbetween the first and last name. Now the one CJuiAutoComplete field works as expected when typing out a full name!