Replacing field name with variable in select query

Question

I want to replace field name with variable to get data from table in PHP and MySQL.

select * from demo where variable-1

instead of select * from demo where fieldname=1

as there are more than 50 fieldname to be chosen from drowpdown.

You have to use Dynamic Query for your desired Output! — Prahalad Gaggar
– Prahalad Gaggar, Commented Jan 28, 2013 at 5:34

John Woo · Accepted Answer · 2013-01-28 05:52:30Z

5

Variables will not work on field names as well as table Names. The only time it will do is when you are creating dynamic sql, eg

SET @variableName = 'fieldname';
SET @sql = CONCAT('SELECT * FROM demo WHERE `', @variableName, '` = 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Dynamic SQL (Prepared Statements)

edited Jan 28, 2013 at 5:52

answered Jan 28, 2013 at 5:33

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

4 Comments

inhan Over a year ago

Wouldn't you surround the @variableName with backticks in case it might be a reserved word?

John Woo Over a year ago

@inhan yes, good point! actually you can alternatively use alias even if's a reserved keyword. :D

John Woo Over a year ago

@inhan ex, SET @sql = CONCAT('SELECT * FROM demo a WHERE a.', @variableName, ' = 1');

inhan Over a year ago

Yeah I almost forgot about that :)

Hary · Accepted Answer · 2013-01-28 05:34:24Z

0

You can use Prepared Statements

delimiter // 
CREATE PROCEDURE dynamic(IN tbl CHAR(64), IN col CHAR(64))
BEGIN
    SET @s = CONCAT('SELECT ',col,' FROM ',tbl );
    PREPARE stmt FROM @s;
    EXECUTE stmt;
END
//
delimiter ;

answered Jan 28, 2013 at 5:34

Hary

5,8368 gold badges49 silver badges91 bronze badges

Collectives™ on Stack Overflow

Replacing field name with variable in select query

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related