Search for column format

Question

This is a bit of a difficult problem for me to word, and I may be going about it in the completely wrong way.

I'm storing a set of options in a database, where each option is its own column. The user can change the number of options, however, so I need a way of allowing PHP to always select all the options.

Let's say I have these columns: options_dialog_1, options_dialog_2, options_dialog_3, options_dialog_4

There could be a varying number of these dialog option columns, eg, another called options_dialog_5 could be added.

How do I select all the dialog option columns, based on their column name format?

Answer 1

I am not sure I understand the problem. Are you looking for

SELECT * FROM options_table

Something like (faux SQL - wont work)

SELECT ( SELECT column_names where column_name LIKE 'options_dialog%' ) 
FROM options_table

sounds not feasible to me (though I am sure it's possible somehow). If you need this, either consider refactoring the database design or maybe use a bitmask to store the selected options in a single column.

Answer 2

I think you have a database design problem here; repeating columns like that always leads to trouble in the end. I think you need two tables, one for the user and one for the options defined something like this...

USERS
   id
   name

OPTIONS
    id
    user_id
    option_dialogue_number
    option_dialogue_value

That turns the columns into rows, which are rather easier to get at.

Answer 3

Brian's answer will really, really pay you off in longer period. But if you need something quick & ugly, you can check out the "metadata dictionary" (tables that store information about all other tables, columns etc). You could get list of columns from it with first query and use it to build the second one.

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='mytable' AND COLUMN_NAME LIKE 'options_dialog%'

Visit the manual on INFORMATION_SCHEMA for more goodies.