[RESOLVED] Compare case-insensitive, but keep case intact?

[Pikachu2000]

I have a registration form in place that I validate for duplicate username before allowing a db insert. I just realized however, that if I try to SELECT 'Bobby' and the db contains a username BoBbY, it won't return a result.

I know I could just strtolower() or ucfirst() all the inserts and selects, but I'd rather let the user keep the case they want in their username. I've been messing around with this for a while now, but I need some help figuring this one out. How can I query the db so the WHERE field = 'username' will match the record 'UserName', 'UsErNaMe', etc.?

It should go without saying I'd prefer to avoid a SELECT * FROM table, and then looping through every record with a while($array) / if(strtolower($input) == strtolower($record).

[bradgrafelman]

While there are string manipulation functions in SQL that can mimic the PHP loop you mentioned, there's a better way of doing this: use a case-insensitive character collation.

For example, if your collation was set to utf8_general on a MySQL server, simply change it to utf8_general_ci (the "_ci" on the end of a collation means case insensitive).

[Pikachu2000]

Thanks Brad. As it turns out, I'm an idiot in this case (no pun intended). I was driving myself crazy trying to figure this out, all the while I was testing the wrong field. I was testing the first name field, which of course SHOULD allow duplicates. My first clue should have been the big, bold "First Name: " next to my text box . . .

Boy, do I feel silly now! :queasy: