Selecting all cols that begin with 'F_pref_'???

zeb

I would like to select the contents of all columns in a table that begin with a certain string. Something like this:

SELECT F_pref_* FROM table_name WHERE F_user_ID = 1

and it would return all the following columns:

F_pref_theme
F_pref_displayThumbs
F_pref_actions
F_pref_time
F_pref_timeZone

How do I write the SECECT statement to do this?

Lars_Berg

You must supply all columns explicitly.

zeb

I was afraid of that -

Thanks anyway.

zeb

Is there no way to use the result of DESCRIBE table with a sub-select? Like this:

SELECT Field FROM (DESCRIBE table) WHERE Field = 'F_pref_%'

I suspect that you need to use a SELECT statement with a sub-SELECT, so is there any other way of getting the names of all the fields in a table other than with DESCRIBE?. Is there a way to limit the output of DESCRIBE to only the Field column?

Lars_Berg

You must write the columns explicitly. Why should that be such a problem?

zeb

It's not really a problem, I'm just going through the process of filling in all the holes in my MySQL knowledge - and this issue seems like it could be a timesaver. I designed this table to contain two types of fields, "user info" (prefix: F_user) and "user prefs" (prefix: F_pref) so that I could single out all F_pref_ fields with pattern matching or something. I wanted to avoid having a separate table to contain data that only paired one to one with data in another table.

I understand that not explicitly entering column names has been called a security risk. I.E. if you add a column to a table that contains sensitive data and shouldn't be called unless necessary - then SELECT * from table may make that new sensitive data available where such broad SELECT statements are used, especially in this case where the rest of the table contains user data.

Since this project is still in development, I have been adding F_pref_ columns as necessary, and I wanted to avoid having to type them in explicitly wherever the occur in my scripts.

I dunno, maybe making a separate table is necessary here...

Thanks.