I have a query which is along the lines of
SELECT fuzzy_id
FROM fuzzy_search
WHERE ((
( fuzzy_search.c='1' && fuzzy_search.h='1' && fuzzy_search.n='1' && fuzzy_search.i='1' && fuzzy_search.a='1' && LENGTH(fuzzy_search.keyword)<'7') ||
(LENGTH(fuzzy_search.keyword)>3 && LENGTH(fuzzy_search.keyword)<7 && fuzzy_search.c<=1 && fuzzy_search.h>=1 && fuzzy_search.n>=1 && fuzzy_search.i>=1 && fuzzy_search.a>=1) ||
(LENGTH(fuzzy_search.keyword)>3 && LENGTH(fuzzy_search.keyword)<7 && fuzzy_search.c>=1 && fuzzy_search.h<=1 && fuzzy_search.n>=1 && fuzzy_search.i>=1 && fuzzy_search.a>=1) ||
(LENGTH(fuzzy_search.keyword)>3 && LENGTH(fuzzy_search.keyword)<7 && fuzzy_search.c>=1 && fuzzy_search.h>=1 && fuzzy_search.n<=1 && fuzzy_search.i>=1 && fuzzy_search.a>=1) ||
(LENGTH(fuzzy_search.keyword)>3 && LENGTH(fuzzy_search.keyword)<7 && fuzzy_search.c>=1 && fuzzy_search.h>=1 && fuzzy_search.n>=1 && fuzzy_search.i<=1 && fuzzy_search.a>=1) ||
(LENGTH(fuzzy_search.keyword)>3 && LENGTH(fuzzy_search.keyword)<7 && fuzzy_search.c>=1 && fuzzy_search.h>=1 && fuzzy_search.n>=1 && fuzzy_search.i>=1 && fuzzy_search.a<=1)) && fuzzy_search.keyword NOT LIKE '%chnia%')
|| ((
( fuzzy_search.t='1' && fuzzy_search.w='1' && fuzzy_search.a='1' && fuzzy_search.i='1' && fuzzy_search.n='1' && LENGTH(fuzzy_search.keyword)<'7') ||
(LENGTH(fuzzy_search.keyword)>3 && LENGTH(fuzzy_search.keyword)<7 && fuzzy_search.t<=1 && fuzzy_search.w>=1 && fuzzy_search.a>=1 && fuzzy_search.i>=1 && fuzzy_search.n>=1) ||
(LENGTH(fuzzy_search.keyword)>3 && LENGTH(fuzzy_search.keyword)<7 && fuzzy_search.t>=1 && fuzzy_search.w<=1 && fuzzy_search.a>=1 && fuzzy_search.i>=1 && fuzzy_search.n>=1) ||
(LENGTH(fuzzy_search.keyword)>3 && LENGTH(fuzzy_search.keyword)<7 && fuzzy_search.t>=1 && fuzzy_search.w>=1 && fuzzy_search.a<=1 && fuzzy_search.i>=1 && fuzzy_search.n>=1) ||
(LENGTH(fuzzy_search.keyword)>3 && LENGTH(fuzzy_search.keyword)<7 && fuzzy_search.t>=1 && fuzzy_search.w>=1 && fuzzy_search.a>=1 && fuzzy_search.i<=1 && fuzzy_search.n>=1) ||
(LENGTH(fuzzy_search.keyword)>3 && LENGTH(fuzzy_search.keyword)<7 && fuzzy_search.t>=1 && fuzzy_search.w>=1 && fuzzy_search.a>=1 && fuzzy_search.i>=1 && fuzzy_search.n<=1)) && fuzzy_search.keyword NOT LIKE '%twain%')
ORDER BY ABS(ASCII('chnia')-ASCII(fuzzy_search.keyword)) , ABS(ASCII('twain')-ASCII(fuzzy_search.keyword)) LIMIT 20
The two middle sections which are very similar are repeated depending on how many search criteria are entered . The problem I am having is to do with the ordering.
At the moment it orders the words on the first letter of the first criteria but the second order by case is effectively ignored. I need to find a way of ordering the results depending on either case. So that in this case if the word begins with c it will be put near the top of the list and also if the word begins with t it will be put near the top of the list. I can best get accross my intent with an if statement
if (ABS(ASCII('chnia')-ASCII(fuzzy_search.keyword)) < ABS(ASCII('twain')-ASCII(fuzzy_search.keyword)))
{
//then this result by the first case
}
else
{
//order this result by the second case
}
I hope I have explained myself well enough. If I have not please let me know.
Thank you in advance
Rob
