Selecting column names if value exists

Question

I would like to select the column names that have the value 1 and where ID is equal to 1. How can I do this?

For example, how would I select the column name if 1 is present in a row? The expected end result that I want to achieve, presuming that I was selecting ID 1, would be Allowed_2 and Allowed_3.

Here is the table which I was talking about in the last paragraph: https://i.stack.imgur.com/PL8Jr.png

see [here](https://stackoverflow.com/questions/4165195/) for how to get column names. But why would you want to do that? What is the purpose of getting those names? It seems like it shouldn't be necessary, there should be a better way to achieve whatever you want to achieve. — tim, Apr 11 '15 at 21:18
I am developing a game and players will have certain ranks. Their ranks will be stored in a database and accessed whenever needed. I want to find what rank/s a player has. Does this make sense and if so what would seem suitable for this situation? — NSPredator, Apr 11 '15 at 21:28
sure, that seems reasonable (If this grows any more complex, I would go with roles as they are easier to manage (eg you have an admin, a player, a gamemaster, whatever), instead of storing each individual right with each user in a table). But why would you want to get the column name? Just select the value, and then ask if it's one or zero. — tim, Apr 11 '15 at 21:34
But surely I would have to query each column which I presume would be a lenghty process as well as putting strain on the system? — NSPredator, Apr 11 '15 at 21:47
no, just to `SELECT Allowed_2, Allowed_3 FROM tableName WHERE id = 1` and then select that result. For example, in PHP it might look like `$row = $result->fetch_assoc();` to get the result, and then `if ($row['Allowed_2'] === '1') { echo 'user can do this'; }` and `if ($row['Allowed_3'] === '1') { echo 'user can do this'; }`. it's hard to say what the code needs to be exactly, but I hope you get the idea. — tim, Apr 11 '15 at 21:53

score 0 · Accepted Answer · answered Apr 11 '15 at 22:03

It is generally not needed to retrieve column names from the database, you want to work on the values inside the table instead. The column names are just identifiers, so you can link your code and the database, but they don't contain any meaning beyond that.

So for your example, if you want to know what the user with id 1 is allowed to do, you would perform a query like this:

SELECT Allowed_2, Allowed_3 FROM tableName WHERE id = 1

and then use it like this (example in PHP):

// database connection, query, etc.
$row = $result->fetch_assoc();

if ($row['Allowed_2'] === '1') { 
    echo 'user can do action 2'; 
}

if ($row['Allowed_3'] === '1') { 
    echo 'user can do this'; 
}

If your game grows more complex, with more users, think about introducing roles (eg admin, user, gamemaster, etc). You might have a table called role, which has an id, a name, and then the different Allowed_X columns. Then, you can map each user to one or more roles, and manage them that way, which makes it a lot easier to manage users.

Selecting column names if value exists

1 Answers1