[SOLVED] select column name where value is 1

[SatansBudgie]

i'm new to mysql so don't hurt me if theres an easy way to do this

 

if there anyway to return a column name where the column is 1

e.g. i have a few columns where some values are 1 and some are 0, i want to return the column names where its value in a field of the table is 1.

 

is this possible?

 

thankyou in advance

[akitchin]

SELECT name FROM table WHERE certain_field = 1

 

it might be helpful to browse for a beginner's tutorial on SQL statements, since this is pretty much the first thing you learn.

[SatansBudgie]

but won't i need to know the name of the column in order to do that?

i'm wanting to look at the data in a table and return all of the column headings where they have a field value of one

 

e.g.    +--------+--------+--------+  Here the statement would output field2 and field3 as their field values

        |  field1  |  field2  |  field3  |  are 1

        +--------+--------+--------+

        |    0      |    1      |    1      |

        +--------+--------+--------+

 

[akitchin]

if you have variably named columns, you desperately need to revisit your database's design. doing this with the current design will be a major pain. why not set up a table like this:

 

field_name  |  field_value
field1      |  0
field2      |  1
field3      |  1

 

this would allow you to use:

 

SELECT field_name FROM table WHERE field_value = 1

[SatansBudgie]

I've thought about maybe doing that but in the context i'm using it in i'm not so sure how else i could structure it.

Im making a table that holds usernames and what 'items' the user has unlocked and if they have been bought.

In its current structure there are two columns for each item in binary (one column for if it is unlocked and one for if it is bought), its field will be 1 if it has been and 0 if not.

I am constantly adding items to this and i'm making a php script to come up with a list of all items bought or unlocked, hence why i need to select column names (the item name) where the value is 1 (i.e. is HAS been bought/unlocked).

 

Current Structure:

+------------+---------+----------+---------+----------+ 

|  username  |  Bshield  |  Ushield  |  Bsword  |  Usword  | 

+------------+---------+----------+---------+----------+

|    USER1    |    0      |      1      |    1      |      1      |

+------------+---------+----------+---------+----------+

|    USER2    |    1      |      1      |    0      |      0      |

+------------+---------+----------+---------+----------+

 

B is bought

U is unlocked

This is why i would like to see all the columns where a value is 1, to see the item names.

 

If I am missing something and there is a better way of structuring this I would be extremely grateful of suggestions

 

Thankyou

[Ken2k7]

*Ken2k7 scratches head

 

Try this set up -

 

Table users

id PRIMARY KEY AUTO_INCREMENT

user_name

 

Table items

id PRIMARY KEY AUTO_INCREMENT

item_name

item_requirement_id FOREIGN KEY item_requirements.id

 

Table purchases

id PRIMARY KEY AUTO_INCREMENT

user_id FOREIGN KEY users.id

item_id FOREIGN KEY items.id

 

Table item_requirements

id PRIMARY KEY AUTO_INCREMENT

requirement

 

That's probably what I would go for. Just an example though. So users table is self-explanatory, as well as items. purchases table holds the purchases. If a user purchased 10 items, then that user should have 10 rows in the purchases table. item_requirements table holds the requirements to unlocking items, etc.

[SatansBudgie]

ah thats perfect thanks. creating that structure in mySQL right now.

thats a whole lot simpler

 

thanks alot for all the help.