How to select data from mysql db using php regardless of case?

[willc]

Hello,

I am trying to create a member log-in where the user simply enters his username and password.  I have dumped all the members' names and passwords into the mysql database.  The names have gone in like "Johnson" not "johnson."  In other words, the first letter is capitalized.  With the code below, I can get "Johnson" through the member log-in with success but not "johnson."  I would like both to work.  The code I have thus far is pasted below.  Any help is greatly, greatly, greatly appreciated.  I cannot figure it out.

 

I have left the comments in of various methods I have tried without success.  I am not clear on whether I should capitalize the words or make them all lowercase or if there is an easier method altogether.  I do not want to have to do anything to the data in the database because that data will have to be updated a lot and it will involve a simple import from an Excel spreadsheet. 

 

I have read that case should not matter if the table fields (member names and passwords) are of the varchar type, which they are.  But, because I cannot log in members using their lowercase names, apparently case does matter.  It seems like the solution to this should be fairly simple, but I cannot sort it out.

 

Thank you for your help,

Will

 

<?php

$host="127.0.0.1"; // Host name

$username="xxxxx"; // Mysql username

$password="xxxxx"; // Mysql password

$db_name="xxxxx"; // Database name

$tbl_name="xxxxx"; // Table name

 

// Connect to server and select databse.

mysql_connect("$host", "$username", "$password")or die("cannot connect");

mysql_select_db("$db_name")or die("cannot select DB");

 

// username and password sent from signup form

$myusername=$_POST['myusername'];

$mypassword=$_POST['mypassword'];

 

 

$sql="SELECT * FROM $tbl_name WHERE lastname LIKE '%$myusername%' and membernum='$mypassword'";

//'SELECT * FROM TABLE'." WHERE lastname LIKE '%". mysql_real_escape_string($myusername). "%'"

//'SELECT * FROM TABLE'." WHERE name LIKE '%". mysql_real_escape_string($x). "%'"

//"SELECT * FROM $tbl_name WHERE lastname = $myusername regexp '^.{". $charcount ."}$' and membernum='$mypassword'";

$result=mysql_query($sql);

 

// Mysql_num_row is counting table row

$count=mysql_num_rows($result);

// If result matched $myusername and $mypassword, table row must be 1 row

 

if($count==1){

// Register $myusername, $mypassword and redirect to file "login_success.php"

session_register("myusername");

session_register("mypassword");

header("Location: http://www.xxxxxxxxxxxx.org");

}

else {

echo "Wrong Username or Password";

}

?>

[AndyB]

Just a minor clarification. Do you want Johnson or johnson to work as if they were equal or do you want Johnson and johnson to be treated as different user names?

[willc]

Thanks for your quick response!

 

I'd like both "johnson" and "Johnson" to be the same.  So if Mr. Johnson comes to the site, wants to log-in, and is feeling lazy about capitalizing his first name, it's not a big deal.  If there are multiple Johnsons, the only difference between them would be their passwords.  Not very smart probably, but this is not top-notch security I'm needing.

 

Thanks again!

[Mario F.]

LIKE is case-sensitive. You can instead do this:

 

// username and password sent from signup form
$myusername=strtoupper($_POST['myusername']);
$mypassword=$_POST['mypassword'];

$sql="SELECT * FROM $tbl_name WHERE UPPER(lastname) LIKE '%$myusername%' and membernum='$mypassword'";

[ohdang888]

is "WHERE" case sensitive?

[Mario F.]

It's neither WHERE is a clause, LIKE is an operator.

 

WHERE will fetch the field value and UPPER() will make it all uppercase. The LIKE operator is case sensitive. That is. it will only match if the value returned from WHERE, its 1st operand, is the same as its 2nd operand.

 

'Johnson' LIKE 'jonhson' returns false

 

'JOHNSON' LIKE 'JOHNSON' returns true

[willc]

my goodness. 

it worked.

thank you so much!

i can't tell you how frustrating that was!