Using variables instead of fieldnames in mysql select query

Question

I´ve searched a lot but I can´t figure out how to do it, if it´s possible...

I have this table:

CREATE TABLE bilanci (
  id              int AUTO_INCREMENT NOT NULL,
  medicoid        int NOT NULL,
  `1`             int NOT NULL DEFAULT 0,
  `2`             int NOT NULL DEFAULT 0,
  `3`             int NOT NULL DEFAULT 0,
  `4`             int NOT NULL DEFAULT 0,
  `5`             int NOT NULL DEFAULT 0,
  `6`             int NOT NULL DEFAULT 0,
  `7`             int NOT NULL DEFAULT 0,
  `8`             int NOT NULL DEFAULT 0,
  `9`             int NOT NULL DEFAULT 0,
  `10`            int NOT NULL DEFAULT 0,
  `11`            int NOT NULL DEFAULT 0,
  `12`            int NOT NULL DEFAULT 0,
  conguagliodic   decimal(10,2),
  totbilancianno  int DEFAULT 0,
  totpagato       decimal(12,2),
  totdapagare     decimal(12,2),
  conguaglio      decimal(10,2),
  rifanno         int NOT NULL,
  pvimun          decimal(10,4) NOT NULL DEFAULT 9.4432,
  PRIMARY KEY (id)
) ENGINE = InnoDB;

The fileds named with numbers correspond to months and I need to have a select like:

select medicoid, (month(curdate()) -2), totdapagare from bilanci

Where (month(curdate()) -2) correspond to the field I need to select.

Is this possible?

Answer 1

I would suggest you to normalize your database structure, you could have one table like this:

CREATE TABLE bilanci (
  id              int AUTO_INCREMENT NOT NULL,
  medicoid        int NOT NULL,
  conguagliodic   decimal(10,2),
  totbilancianno  int DEFAULT 0,
  totpagato       decimal(12,2),
  totdapagare     decimal(12,2),
  conguaglio      decimal(10,2),
  pvimun          decimal(10,4) NOT NULL DEFAULT 9.4432,
  PRIMARY KEY (id)
) ENGINE = InnoDB;

and a second table bilanci_month:

create table bilanci_month (
  id int auto_increment,
  bilanci_id int,
  rifanno int NOT NULL,
  month int NOT NULL,
  value int)

(bilanci_id can be defined as a foreign key of bilanci_month), then your select query would be like this:

select
  b.medicoid,
  coalesce(bm.value, 0),
  b.totdapagare from bilanci
from
  bilanci b left join bilanci_month bm
  on b.id = bm.bilanci_id and bm.month = month(curdate())-2

also, be careful about month(curdate())-2, what would happen if the month is january or february? You have to implement some logic to get for example november or december of the previous year (and add this logic to your join).