Autocomplate search on multicolumn

QuestionsAutocomplate search on multicolumn
biowan asked 7 years ago

Hi Abu,

Is it possible to search on more than 1 field with autocomplete ?
In my DB, firstname and lastname are in two columns. It's easier to search person with the fullname.

Thanks for your help.

5 Answers
Abu Ghufran answered 7 years ago

Quickest solution would be to concat(firstname, ' ', lastname) as name in your select sql, then use it in autocomplete.
For now it works on one field.

biowan answered 7 years ago

Hi Abu,

I've already test the concatenation before to post my question. It's doesn't work with alias. It works without alias, but with "LIKE" comparator only.

Cannot work :
SELECT * FROM participant WHERE Concat(firstname, ' ',lastname) = '%anne%'

Can work :
SELECT * FROM participant WHERE Concat(firstname, ' ',lastname) LIKE '%anne%'

MMM… I see.
It's works, but the condition is 'anne%' and not '%anne%'. So, the query cannot select in the lastname.

Abu Ghufran answered 7 years ago

You can try begins with operator by setting search options. It will then use LIKE with 'anne%'

// optionally set limited search operators (e.g. bw = begins with, 'eq','ne','lt','le','gt','ge','bw','bn','in','ni','ew','en','cn','nc')
$col["searchoptions"]["sopt"] = array("bw");

biowan answered 7 years ago

"searchoptions" is the column filter right ?

But my question is the filter on the autocomplete, when you type in the text field of the record, not in the filter under the header. The "bc" operator is default in autocomplete filter, I wish to have "contain". So, it's possible.

Thanks for your help.

Abu Ghufran answered 7 years ago

For autocomplete, Please goto file jqgrid_dist.php and replace following line:

$sql = $c["formatoptions"]["sql"]. " $cond {$c["formatoptions"]["search_on"]} like '$term%'";

with

$sql = $c["formatoptions"]["sql"]. " $cond {$c["formatoptions"]["search_on"]} like '%$term%'";

Your Answer

7 + 10 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?