MySQL SQL Syntax Issue

Question

I am using a simplest SQL clause to retrieve user id,

String query = "SELECT * FROM `login` WHERE `user_id` LIKE `userid` ";

in here, user_id is column, userid is input variable and not null,

when debug, system always report following errors,

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'userid' in 'where clause'

I can print userid as Allan, userid is String type and current value is "Allan". Why system unable to compare column and input variable?

Thank you very much in advance!

Rosemary

userid is input variable ? What do you mean by that? What programming language do you use? — Leo Chapiro
– Leo Chapiro, Commented Jul 25, 2014 at 14:08

John Conde · Accepted Answer · 2014-07-25 14:05:49Z

4

You need to place your string value in quotes, not ticks. By placing it in ticks you are telling MySQL it is a column identifier and not a string value like you want

String query = "SELECT * FROM `login` WHERE `user_id` LIKE 'userid' ";

answered Jul 25, 2014 at 14:05

John Conde

220k100 gold badges464 silver badges504 bronze badges

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

5 Comments

user3837183 Over a year ago

Thank you very much. I just copy/paste you suggested, but still error

user3837183 Over a year ago

QUERY: SELECT * FROM login WHERE user_id LIKE 'userid'

user3837183 Over a year ago

java.sql.SQLException: Illegal operation on empty result set.

Strawberry Over a year ago

Do we think that this is a case sensitive collation?

Funk Forty Niner Over a year ago

"You need to place your string value in quotes, not ticks. By placing it in ticks you are telling MySQL it is a column identifier and not a string value like you want" - I might quote you on that someday ;) or, have I just done that now? ;)

Prinsig · Accepted Answer · 2014-07-25 14:07:01Z

0

For the input variable, use '' not ``. I believe SQL interprets them as part of the system rather than quotations.

answered Jul 25, 2014 at 14:07

Prinsig

2453 silver badges9 bronze badges

Comments

Dennis van der Schagt · Accepted Answer · 2014-07-25 14:30:17Z

0

John Conde's answer already resolves the error, but I have a small remark.

It looks like you want to select all login information from users with a user_id that is the same as the userid variable in your java code. To do that you can use:

String userid = "Rosemary";
String query = "SELECT * FROM `login` WHERE `user_id` LIKE '" + userid + "' ";

The actual query then becomes:

SELECT * FROM `login` WHERE `user_id` LIKE 'Rosemary'

answered Jul 25, 2014 at 14:30

Dennis van der Schagt

2,4122 gold badges29 silver badges35 bronze badges

1 Comment

user3837183 Over a year ago

dennisschagt, Thank you very very much!!!, I got passed by your way. Actually, John's way has generated same SQL query as yours, but still fail when debug, that's weird. Also thanks Prinsig's kind help, have a nice day you guys!

Collectives™ on Stack Overflow

MySQL SQL Syntax Issue

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related