[SOLVED] Dynamic Table Names

[bubblegum.anarchy]

Does `table` in `SELECT * FROM table` have to be a constant.. or can the table be a dynamically generated value based on another tables column value.

 

SELECT concat('prefix_', tbl_name) FROM where_table_names_are_stored WHERE id = $id

 

I woud like to select from the above return value as a table name rather than a result set in one query.

 

Any ideas?

[btherl]

While I can't answer your question, I can suggest using CASE to choose between tables.  Or partitioning may suit your needs, if you are splitting the tables for efficiency rather than for design reasons.  Looks like partitioning in MySQL was only introduced in 5.1.  Partitioning is very powerful when used appropriately.

[bubblegum.anarchy]

no no, I needed something dynamic... and have gone with just using php like SELECT * FROM prefix_$table_name since my limited research suggests that dynamic table names are not possible unles using a procedure.

 

Thanks.