User defined variables in SQL query

Question

I am having a wee bit of trouble calling a variable in phpmyadmin using the SQL query window.

I am still just learning the ropes so this is very simple. I believe it's just a small syntax or quote issue.

What I am trying to do is:

SET @var1 = Value (Does it need quote marks after "=" or not?)

SELECT * From `Table` WHERE 'Column' = @var1      (Same question about quote marks)

This seems just stupid simple. I did a search and just couldn't quite find what I am doing wrong.

If you are using a reserved word and wanted to use it as fieldname, use double quotes(single quotes doesn't work, it's for string), it's ANSI SQL-compliant. And besides, backtick is not stackoverflow-friendly, you'll not be able to use them explaining the codes inside of sentences — Michael Buen
– Michael Buen, Commented May 13, 2012 at 5:40

nawfal · Accepted Answer · 2012-05-13 06:23:43Z

4

You dont need quotes:

SET @var1 =10;
SELECT * FROM table WHERE `column` = @var1 //should work

Or you could do:

SET @var1:='somename';
SELECT * FROM table WHERE `somefield`=@var1

See: variables

edited May 13, 2012 at 6:23

nawfal

73.8k59 gold badges342 silver badges379 bronze badges

answered May 13, 2012 at 4:47

Sudhir Bastakoti

100k15 gold badges163 silver badges169 bronze badges

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

2 Comments

Chris Nelson Over a year ago

Believe you just helped me with two reasons why it wasn't working. I wasn't using a semicolon after the "SET" command. And after reading the link you have I think my value isn't compatible. The value I am trying to use looks like '10-12001'

Sudhir Bastakoti Over a year ago

if thats string then you could use something like in the added answer

bfavaretto · Accepted Answer · 2012-05-13 04:52:33Z

1

If your value contains a string, you have to use quotes around it, otherwise you don't. But you should not quote your column name! So:

SET @var1 = 'stringval';
SELECT * From Table WHERE Column = @var1;

answered May 13, 2012 at 4:52

bfavaretto

72k18 gold badges118 silver badges162 bronze badges

3 Comments

Chris Nelson Over a year ago

K good to know. We have now moved past the syntax error and I am getting this: #1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

Chris Nelson Over a year ago

And I just checked and none of my stuff is using utf8_unicode. It's all utf8_general

Chris Nelson Over a year ago

Problem solved. I just had to change a couple settings. Thanks guys!

Collectives™ on Stack Overflow

User defined variables in SQL query

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related