Autosuggest with Different First Character Return

[kodemonki]

I have a cfinput box where users can start to type in the name (first or last) of an employee. My autosuggest CFC searches for first or lastnames starting with the entered string. Adding ?cfdebug to my address shows that the data is being retrieved correctly, but no drop down shows up with suggested data. I'm returning names in the formate 'firstname lastname'. If I try to look up Bart Simpson by entering 'Si' the data is returned correctly, but the 'si' just sits pathetically in the input box. No autosuggest data is visible.

Is this a bug? Am I doing something incorrectly? Please advise! Thanks!

[Dan_Bracuk]

Looks like your cfc code is case sensitive.

[Newsgroup_User]

yes, as Dan said, you seem to be performing case-sensitive checking of
names in your db. depending on your db, use available string
manipulation functions to convert the case. i.e. in MySQL it would be
something like:

...
WHERE LOWER(first_name) LIKE <cfqueryparam cfsqltype="cf_sql_varchar"
value="#lcase(keyword)#%"> OR LOWER(last_name) LIKE <cfqueryparam
cfsqltype="cf_sql_varchar" value="#lcase(keyword)#%">

your db's function to convert the case of column value may be different...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[kodemonki]

Alas, it is not case sensitive. Here is my query:
select v.firstname || ' ' || v.lastname name
from consultants_v v, employees_v ae
where v.employee_id = ae.employee_id
AND (upper(v.firstname) like upper('#search#%')
OR upper(v.lastname) like upper('#search#%'))
group by v.lastname, v.firstname
ORDER BY name

[Newsgroup_User]

> upper('#search#%')

that is not right. you are trying to use a db function on a cf variable.
it should instead be '#ucase(search)#%'

your query is probably better re-written using INNER JOIN:

SELECT v.fistname || ' ' || v.lastname name
FROM consultants_v v INNER JOIN employees_v ae ON v.employee_id =
ae.employee_id
WHERE (UPPER(v.firstname) LIKE '#UCASE(search)#%') OR (UPPER(v.lastname)
LIKE '#UCASE(search)#%')
GROUP BY v.lastname, v.firstname
ORDER BY name

btw, i am not sure you need that GROUP BY clause there at all...

and, please, DO use <cfqueryparam> tag in your queries!

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[kodemonki]

Even though my data is returned correctly from the query I took your advice and switched to the CF ucase() function. I am still having the same problem.

I add in cfqueryparam before I make pages live, but I do use it.

Thanks!

[kodemonki]

Also, I'm pretty sure that IS an inner join. My database does not allow for "INNER JOIN" syntax.

[Dan_Bracuk]

"inner join" means the same as "join" It's also equivalent to the syntax of the query you posted.

To troubleshoot your problem, you have to run your query in circumstances where you can see debugging info. I'm guessing autosuggest is not one of those circumstances.

[kodemonki · Accepted solution]

Right, Oracle 8i allows inner joins, but not "inner join".

Anyway, you're probably right about autosuggest. I'll probably have to have it return both "Bart Simpson" and "Simpson, Bart" which will be a small pain later. CFDEBUG is probably the best I can get. I still think this is a bug 🙂

[kodemonki]

It works if I return "Simpson Bart" but not "Simpson, Bart", even if I return valueList(query.value, ';')

Oh well, good enough.