Find & Sort By Column Name in MYSQL Given Condition

In a table I have the following value:

    ID | Exercise1 | Exercise2 | Exercise3
     1 |     0     |     0     |     0
     2 |     0     |     0     |     0

When a user completes an exercise, the db switches from '0' to '1'. I'm looking for an sql query that searches by the ID number of the user returns the lowest column name that is set to 0.

EX:

ID | Exercise1 | Exercise2 | Exercise3
 1 |     1     |     1     |     0

Here the query would return with exercise3, since exercise1 and exercise2 have previously been updated and completed by the user.

I found SELECT COLUMN_NAME FROM information_schema.columns

but can't put it together with the sorting I'm looking for, any help would be deeply app开发者_如何学编程reciated.

If you have only a handful of exercises (e.g. < 5), then you can simply hardcode the query with a series of nested IF() statements.

If you have more than that, then you should change your data model so each user/exercise mapping is stored in a separate row.

Something like this?

SELECT CASE
         WHEN Exercise1=0 THEN 'Exercise1'
         WHEN Exercise2=0 THEN 'Exercise2'
         WHEN Exercise3=0 THEN 'Exercise3'
         ELSE NULL
       END AS Exercise
  FROM MyTable
  WHERE ID = SomeID

Hmmm... you have problems because your design is wrong. The problem is that your database design was affected by how you imagine the presentation of the table. But the database thinking is different. The database would be normally designed this way:

StudentID | ExcerciseID | Completed
     1    |     1       |     1
     1    |     2       |     1
     1    |     3       |     0
     2    |     1       |     0
....

And then you can do:

select StudentID, min(ExcerciseID) as FirstExcerciseNotCompleted
from Excercises
where Completed = 0

to see first incomplete excercise for each student, or if you want set next completed excercise to Student 1, just do:

update Excercises
set Completed = 1
where Student = 1 and ExcerciseID = (select min(ExcerciseID) from Excercises where StudentID = 1 and Completed = 0)