MySQL, select column name based on a value

Question

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.

Would it be simpler to do this in PHP?

Ivan Cachicatari · Accepted Answer · 2014-07-29 02:18:20Z

3

Considering your table, you can add the column names to a new array and join it

$res = mysqli_query($link,"select * from Training where username = 'kd4893'");
$row = mysqli_fetch_assoc($res);

//printing the results
print_r($row);

$newrow = array();

$i = 0;
foreach($row as $key => $value){
    if($i > 1){
        if($value > 0){
            $newrow[] = $key;
        }
    }
    $i++;
}

//printing the joined array
echo join(",",$newrow);

The output is:

Array
(
    [id] => 1
    [username] => kd4893
    [9875] => 0
    [875] => 0
    [475] => 1
    [9835] => 1
    [375] => 0
    [9872] => 1
    [9234] => 1
    [3444] => 0
)

475,9835,9872,9234

This is no best way to do this, but is my answer.

answered Jul 29, 2014 at 2:18

Ivan Cachicatari

4,3143 gold badges24 silver badges41 bronze badges

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

Comments

Tom Lucas · Accepted Answer · 2014-07-29 01:50:52Z

2

Database normal form would require three tables:

A user table. fields: user_id, name
A course name table. fields: course_id, course_name
A cross reference table. fields: user_id, course_id

This might be harder in the short run but worth it if you add many features.

If you want to stick with one table, why not:

one table: user_name, comma-separated string of course names.

Your insert becomes:

INSERT INTO Training VALUES(kd4893, "475,9835,9872,9234")

answered Jul 29, 2014 at 1:50

Tom Lucas

1461 silver badge3 bronze badges

Comments

Anthony Neace · Accepted Answer · 2014-07-29 02:14:55Z

You're missing out on a lot of the power of a relational database by trying to dynamically generate your data as columns instead of part of a record in a table. By normalizing your data, you can craft your database schema in such a way that it will enforce some rules for you:

Course IDs must be unique.
Student IDs must be unique.
Students enrolled in courses must have a real StudentID, and courses must have a real CourseID.
A student can't have multiple concurrent enrollments of a course.
A student can only have one course status.

Example schema:

CREATE TABLE IF NOT EXISTS `Courses` (
    `ID` INT NOT NULL AUTO_INCREMENT,
    `Name` varchar(50) NULL,
    PRIMARY KEY (`ID`)
);

CREATE TABLE IF NOT EXISTS `Students` (
    `ID` INT NOT NULL AUTO_INCREMENT,
    `Name` varchar(50) NULL,
    PRIMARY KEY (`ID`)
);

CREATE TABLE IF NOT EXISTS `Enrolled` (
    `CourseID` INT NOT NULL,
    `StudentID` INT NOT NULL,  
    `Status` BIT NOT NULL DEFAULT 0,
    PRIMARY KEY (`CourseID`, `StudentID`),
    FOREIGN KEY (`CourseID`) REFERENCES Courses (`ID`),
    FOREIGN KEY (`StudentID`) REFERENCES Students (`ID`)
);

Now we can insert some data...

INSERT INTO Courses (ID, Name) VALUES (1, "Algebra 1");
INSERT INTO Courses (ID, Name) VALUES (2, "English");

INSERT INTO Students (ID, Name) VALUES (1, "Tom");
INSERT INTO Students (ID, Name) VALUES (2, "Tina");

INSERT INTO Enrolled (CourseID, StudentID, Status) VALUES (1, 1, 0);
INSERT INTO Enrolled (CourseID, StudentID, Status) VALUES (2, 1, 1);
INSERT INTO Enrolled (CourseID, StudentID, Status) VALUES (1, 2, 0);

...and query some sensible results.

Here is an example where we select the CourseID and CourseName for all courses that Tom has completed:

Select c.ID as CourseID, c.Name as CourseName
From Enrolled as e
Join Courses as c ON e.CourseID = c.ID
Join Students as s on e.StudentID = s.ID
Where s.ID = 1 AND e.Status = 1;

COURSEID    COURSENAME
2           English

Here's a sql fiddle with another query commented out. Feel free to play with it: http://sqlfiddle.com/#!2/126e5/3/1

Collectives™ on Stack Overflow

MySQL, select column name based on a value

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related