[SOLVED] case sensitive database lookup

[acctman]

my script uses this code to check if a user name exist, how would I make it case sensitive, so John and JOHN will be check as well as any variations?

 

//username exists?
$result = sql_query("SELECT `m_user` FROM $membtable WHERE m_user='{$en['user']}'");
        if (sql_num_rows($result) > 0) $err .= _reg_member_exists.'<br>';

[trq]

Do you meen you want it to find John and JOHN as the same user?

[acctman]

Do you meen you want it to find John and JOHN as the same user?

 

yes, any case variation of John to be the same name

[trq]

$result = sql_query("SELECT LCASE(m_user) FROM $membtable WHERE m_user='" . strtolower($en['user']) . '");

[teng84]

$result = sql_query("SELECT `m_user` FROM $membtable 
WHERE m_user='".strtolower($en['user'])."' 
or  m_user='".strtoupper($en['user'])."' 
or m_user='{$en['user'])}'  ");

is that what you mean

 

i notice that this is almost the same as thorpe have but in case!

[acctman]

will both examples find joHn, john, JoHN, JOhn, etc as the same?

[corbin]

Unless I'm mistaken, MySQL is case insensative by default.

[corbin]

$result = sql_query("SELECT `m_user` FROM $membtable 
WHERE m_user='".strtolower($en['user'])."' 
or  m_user='".strtoupper($en['user'])."' 
or m_user='{$en['user'])}'  ");

is that what you mean

 

i notice that this is almost the same as thorpe have but in case!

 

Assuming MySQL WHERE clauses are case sensative, this won't work....

 

Let's assume 'Corbin' is stored in the db and 'cOrbin' is $en['user'].

 

strtolower('cOrbin') -> 'corbin'

strtoupper('cOrbin') -> 'CORBIN'

 

corbin, CORBIN, and cOrbin all do not match Corbin.

[trq]

Yes I believe MySql is case insensative by default, but the op is using sql_query() so one would assume he may not be using MySql.

[teng84]

i guess this guy mean is SQL not php

 

try

LOWER()

UPPER()

 

$result = sql_query("SELECT UPPER(m_user) as  x FROM $membtable

WHERE  x='".strtoupper($en['user'])."' ;

 

i guess this is what you mean

 

[trq]

i guess this guy mean is SQL not php

 

try

LOWER()

UPPER()

 

$result = sql_query("SELECT UPPER(m_user) as  x FROM $membtable

WHERE  x='".strtoupper($en['user'])."' ;

 

i guess this is what you mean

 

 

That is exacty what I posted several posts ago.

[corbin]

Ahhh.... I read that as mysql_query.... So used to seeing mysql around these forums, I just see it that way now ;p.

 

I think MSSQL is case insensative as well, and I would assume other database systems are although all I've ever used is MySQL MSSQL and a little bit of Access.

[trq]

Well, we can safely assume that whatever db is being used it is not case insensative, otherwise, why post the question?

[acctman]

i'm using Mysql/PHP... i'm editing a current script

[teng84]

then no prob with that mysql is case insensitive  just like what the other guys is saying

[acctman]

i would have to change the m_user field in the mysql db to TINYTEXT to be case insensitive right... that would back jOhn, JOHN, joHN, JoHN, etc... all be the same as 'john' right? And no special coding would be needed when doing php queries right?

[Barand]

Case insensitivity will depend on the particular collation being used

 

Notice that there is a convention for collation names: They start with the name of the character set they are associated with, they usually include a language name, and they end with _ci (case insensitive), _cs (case sensitive), or _bin (binary).

[acctman]

Case insensitivity will depend on the particular collation being used

 

Notice that there is a convention for collation names: They start with the name of the character set they are associated with, they usually include a language name, and they end with _ci (case insensitive), _cs (case sensitive), or _bin (binary).

 

m_user is set to latin1_swedish_ci but both user John and john can be two separate accounts, why is this?

[Barand]

is m_user a primary key or defined as unique?

[acctman]

is m_user a primary key or defined as unique?

 

not primary or defined as unique

[Barand]

in which case you can have john, john, john, John as different accounts

[acctman]

so by making 'm_user' index type:UNIQUE, it would make all new entry case insensitive?

[Barand]

 

 

Then if you have "john" in the column it won't let you add "John" or "jOhn" etc  because they will be treated as same value.

[acctman]

 

 

Then if you have "john" in the column it won't let you add "John" or "jOhn" etc  because they will be treated as same value.

 

thanks exactly what i want =) one more question will i lose any performance by switching from Index to Unique, i'm editting an existing script and I don't understand why the create would not make the username field unique. doesn't make any since for it not to be.