CJuiAutoComplete one field searching for First and Last names

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!


thanks for your sharing

in my CJuiautocomplete i use to search 2 field in table too like firstname and lastname…thankss

now i know how to do it

That’s a very bad way of doing this, because you cannot use an index for searching this way and on a large data set the performance would degrade quickly. Best to split the string into 2 words by space and search in first and last name with 2 strings as it will perform way better.