Case In-sensitive query in php

Question

Hello!

I try to make a registration form. When user post the fields, i check USER_NAME. If exist then no inster the row to MYSQL.

After post:

$check = "SELECT name FROM test WHERE name='".$_POST['user_name']."';  
 $result = mysql_query($check) or die (mysql_error()); 
 $numrows = mysql_num_rows($resutl);
 if ($numrow != 0){ 
     echo "exist"; exit;        
 }else{ 
     insert.....
 }

In the database i have a row with user_name=Test

If the posted value is Test, then ok, but if i post "test" or "TeSt" or something else, then the row iserted.

How can make it case insensitive? I think i should use str to lowercase to the posted value, but in the query..how can i do that?

Thank you

Answer 1

try SELECT name FROM test WHERE UPPER(name)='".strtoupper($_POST['user_name'])."'

be aware that you should correctly escape posted username (see php mysql_real_escape_string function)

Answer 2

The case sensitivity also depends on the data type of the column. You can probably fix the problem without the extra overhead of the string conversions (i.e. UPPER() and strtoupper()) by changing the column type to VARCHAR.

From the MySQL Manual:

For nonbinary strings (CHAR, VARCHAR, TEXT), string searches use the collation of the comparison operands. For binary strings (BINARY, VARBINARY, BLOB), comparisons use the numeric values of the bytes in the operands; this means that for alphabetic characters, comparisons will be case sensitive.

A comparison between a nonbinary string and binary string is treated as a comparison of binary strings.

Simple comparison operations (>=, >, =, <, <=, sorting, and grouping) are based on each character's “sort value.” Characters with the same sort value are treated as the same character. For example, if “e” and “é” have the same sort value in a given collation, they compare as equal.

Answer 3

If the collation of your field is a case-insensitive one (such as utf8_general_ci or latin1), you can use the LIKE operator to do a case-insensitive match.

"SELECT * FROM test WHERE name LIKE '$username';"