CODEWITHSUNDEEP
mysql
Europeuser
Europeuser

Reputation: 952

mysql get result from dynamicly formated column name

I have to get result from column which name is generated by the data from another column. I will explain with the following example:

column names in the database:

months | am1 | am2 | am3 | am4 | am5 | am6 | am7 | am8 | am9 | am10 |am11 | am12

enter image description here

I want in my query to get the value from column starting with am + months value my current query is:

$query = 'select id, iid, contractnumber, concat("am",`months`) as amount from credits where iid > 0';

but this instead of returning the value (71) of the specific am.. column it returns the column name, for example am5

How can I directly access the value of column am5

Thank you for your time !

Upvotes: 0

Views: 28

Answers (1)

slaakso
slaakso

Reputation: 9050

That is a bad design and you should change it, but if that's what you got, you can use CASE

select id, iid, contractnumber,
  case months
  when 1 then am1
  when 2 then am2
  when 3 then am3
  when 4 then am4
  when 5 then am5
  when 6 then am6
  when 7 then am7
  when 8 then am8
  when 9 then am9
  when 10 then am10
  when 11 then am11
  when 12 then am12
  end as amount
from credits 
where iid > 0

Upvotes: 1

Related Questions