[SOLVED] Using variable variables in mysql

[artacus]

My coworker built a non-normalized database and now I'm trying to do reports w/o having to do a ton of queries.

 

The main table I'll be using is category_map. Its a normalized table that gives me the level, category & sub-category. Now I've got about 5 non-normalized tables that are related. So I added a `field_name` field to category_map table. I store the table_name.field_name that each category is mapped to.

 

So a row in my category_map might look like this:

cat_id     description                    category        level     field_name                                             
---------  -----------------------------  --------------  --------  ------------------------------------------------------ 
40         Secretary                      Classified      JH        tbl_class_allocations_jh.secretary       
16         Office/Attendance Assistant    Classified      EL        tbl_class_allocations_el.office_attendance_assistant   

 

So my question is, "Is there any way to treat that field_name like you would a "variable variable" in PHP. So when the query runs I could get back the value stored in tbl_class_allocations_jh.secretary and not the string "tbl_class_allocations_jh.secretary"???

[gluck]

I don't understand the problem here but you can have a variable declared in mysql like SET @var_name = expr;

 

For your case you can use pl/sql stmts. Which version of mysql are you using? Give more details of the problem.

[artacus]

Yeah, I wanted to use a @var as a field name. But it seems you can use variables for field and table names.

 

I couldn't use a prepared statement because the value of the variable would change for each row.

 

I ended up with two choices, normalizing the tables and recreating the old ones as views (I didn't want to recode the application); or writing a stored procedure to pull the value. I ended up normalizing the tables and creating views.