2 Replies - 467 Views - Last Post: 29 July 2014 - 05:40 AM Rate Topic: -----

#1 helpme18  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 21-October 09

MySQL, select column name based on a value.

Posted 28 July 2014 - 06:14 PM

Hello.
I have a database that contains the course number for training that needs to be done as column names. Each time a new training form is added a new column is created for it. A 0 stands for uncompleted and a 1 stands for completed. I want to be able to get the course numbers(column names) for a specific person based on whether the value for the column is 1(complete). So it would have to iterate through all the columns to check if they are 1. But i am not going to know the names of the columns beforehand since a script dynamically adds new course numbers to the table. Any ideas?



CREATE TABLE Training(
id int NOT NULL AUTOINCREMENT,
username varchar(6) not null,
`9875` INT(2) NULL,
`875` INT(2) NULL,
`475` INT(2) NULL,
`9835` INT(2) NULL,
`375` INT(2) NULL,
`9872` INT(2) NULL,
`9234` NULL,
`3444` NULL
);

INSERT INTO Training VALUES(kd4893,0,0,1,1,0,1,1,0)

So a query on kd4893 would return 475,9835,9872,9234.

Edit: Would it be simpler to do this in PHP?

This post has been edited by helpme18: 28 July 2014 - 06:15 PM


Is This A Good Question/Topic? 0
  • +

Replies To: MySQL, select column name based on a value.

#2 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3560
  • View blog
  • Posts: 10,352
  • Joined: 08-June 10

Re: MySQL, select column name based on a value.

Posted 29 July 2014 - 03:09 AM

Quote

I have a database that contains the course number for training that needs to be done as column names. Each time a new training form is added a new column is created for it.

that’s already a wrong design choice. having data (and course names/numbers are data) as column names (meta data) is a strong violation of database design. additionally, you should never require the table structure to be modified just to add the same data types.

read also Atli’s tutorial on Database Normalisation, that should clarify your issues.

something like
CREATE TABLE Training(
  id INT NOT NULL AUTOINCREMENT,
  username VARCHAR(6) NOT NULL,
  course SMALLINT NOT NULL,
  completed TINYINT DEFAULT 0
  FOREIGN KEY course REFERENCES courses (coursenumber) ON DELETE CASCADE
);

should totally suffice.

This post has been edited by Dormilich: 29 July 2014 - 03:16 AM

Was This Post Helpful? 0
  • +
  • -

#3 DarenR  Icon User is online

  • D.I.C Lover

Reputation: 485
  • View blog
  • Posts: 3,264
  • Joined: 12-January 10

Re: MySQL, select column name based on a value.

Posted 29 July 2014 - 05:40 AM

as Dom said -- very bad idea

you should have a set table and manipulate the data with in the table

your course table could be like


table 1
course_id course_name


table 2
student_ID  STUDENT_NAME

TABLE 3

COMPLETION_ID      COURSE_ID    STUDENT_ID     BIT (OF T/F)



DATA WOULD LOOK LIKE

TABLE 1
1          MATH 101

TABLE 2

23    JOHN SMITH


TABLE 3

1          1           23          T OR 1 DEPENDING ON HOW YOU WANT TO USE IT

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1