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

Hanky Panky · Accepted Answer · 2013-08-22 04:55:00Z

2

You can use BINARY for that.

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

That will make a case sensitive match.

answered Aug 22, 2013 at 4:55

Hanky Panky

46.9k9 gold badges76 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DevZer0 · Accepted Answer · 2013-08-22 04:55:39Z

1

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

answered Aug 22, 2013 at 4:55

DevZer0

13.5k7 gold badges29 silver badges54 bronze badges

Comments

GautamD31 · Accepted Answer · 2013-08-22 04:54:50Z

0

Try like

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

answered Aug 22, 2013 at 4:54

GautamD31

28.8k10 gold badges66 silver badges86 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:11:37Z

0

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

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Aug 22, 2013 at 4:53

Rohan Kumar

40.7k11 gold badges81 silver badges111 bronze badges

Comments

Alexander Yancharuk · Accepted Answer · 2013-08-22 06:35:04Z

0

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.

edited Aug 22, 2013 at 6:35

Alexander Yancharuk

14.7k5 gold badges60 silver badges57 bronze badges

answered Aug 22, 2013 at 5:04

Vishnu Kant Maurya

157 bronze badges

1 Comment

Hanky Panky Over a year ago

How would you know in which case the username is stored in DB? it wont always be ucfirst

esmoreno · Accepted Answer · 2013-08-22 06:47:07Z

0

Try like

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

answered Aug 22, 2013 at 6:47

esmoreno

6665 silver badges12 bronze badges

Collectives™ on Stack Overflow

php mysql query select match exact word

6 Answers 6

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related