php mysql query select match exact word

Question

I want to select the match exact word from table, but i got a problem that

if row like : Angel and i did select * from table where row = "angel" it success and first

letter is small and in db its capital,

$r = mysql_fetch_row(mysql_query("SELECT ID 
                                  FROM Login
                                  WHERE Username = 'angel'
                                    And Password = 'zxc'"));
if($r[0])
    die("success");
else
    die("failed");

In mysql Table
Username : varchar(50) : Angel
Password varchar(50) : zxc

results should be falied

because its Angel not angel

so any solution for it

Answer 1

You can use BINARY for that.

SELECT *  FROM `table` WHERE BINARY `row` = 'angel'

That will make a case sensitive match.

Answer 2

Yes this is due to the collation of your table field. you should set it to a case sensitive collation usually suffixed with cs like latin1_swedish_cs

Answer 3

Try this,

$r = mysql_fetch_row(mysql_query("SELECT ID FROM Login WHERE  
            Password = 'zxc' AND Username collate latin1_general_cs = 'angel'"));

Read Case Sensitive Database Query and MySQL case sensitive query

Answer 4

Try like

SELECT ID
FROM Login
WHERE Username = 'angel' COLLATE utf8_bin;

Answer 5

mysql_query("
    ALTER TABLE `Login`
    CHANGE `Username` `Username` VARCHAR(250) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL
");    

$r = mysql_fetch_row(mysql_query("SELECT ID FROM Login WHERE  Username = 'angel' And Password = 'zxc'"));
if($r[0])
    die("success");
else
    die("failed");

Now it will work.

Answer 6

Try like

SELECT ID
FROM Login
WHERE upper(Username) = upper('angel')