fenway

Have you read the sticky in the chlid board?

BTW, if you have a case-insensitive collation, you're just killing the index that way. I actually meant this: SELECT LoaneeId, Name, Address, PhoneNumber, EmailAddress, CASE WHEN upper( Name ) LIKE '%TEST%' THEN 4 WHEN upper( Address ) LIKE '%TEST%' THEN 2 WHEN upper( EmailAddress ) LIKE '%TEST%' THEN 1 ELSE 0 END ) AS score FROM loanee_table WHERE Active =1 AND ( upper( Name ) LIKE '%TEST%' OR upper( Address ) LIKE '%TEST%' OR upper( EmailAddress ) LIKE '%TEST%' ) ORDER BY score DESC

Say what?

What's "nothing at all"? have you dumped the resultset from your initial query on line 1?

Hope those IP addresses are being stored numerically....

Well, the first part can be accomplished with group-concat.

You mean $users[0].

Say what?

Looks good to me... though I suppose you could replace NOT IN with a LEFT JOIN...

How?

You may also be able to convert it to a join, which might be faster.

What's wrong with a good ol' INT?

So, solved?

and that wasn't the least bit sarcastic... using LIKE is bad enough... even worse is using it 3 times.

Well, a scalar subquery, that is.

I only now realize that you're UNIONing the *same* table! This is the wrong approach --- what you want is weighted matching. Then you don't need any magical union. Simply assign a higher "score" for a match on name than on e-mail, and sort on this column.

Say that again? How can they be different if they're duplicates?!?!

Let's start again. Show me some sample output from the original/current query (without group-concat).

WHERE ( value LIKE '%$gender%' OR value LIKE '%height'% OR OR value LIKE %'build%' ) ugh.

For debugging, forget the group_concat... what I was mentioning is that you're not grouping by anything, so what do you expect group concat to do?

That's not very normalized, now, is it? You'll have to OR multiple LIKEs together.

I assume you're referring to this quote from the manual? I've never actually had this be a problem... the only "workaround" is to use a GROUP BY and "filter" the rows, taking advantage of mysql's loose group by restrictions. But then you might get a random A/B/C... do you care?

GROUP_CONCAT() without a GROUP BY clause?

Performance nightmare.

I didn't say not to use an order by clause... i mean "ORDER BY name" in each one.