MySQL dynamic table name within query

[ryanmhuc]

Is it possible to have a dynamic table name within a query or a table
name that is a variable? This does not work but gives an example:

SELECT * FROM concat('table', 'name')
- OR -

SET @table = 'a';
SELCT * FROM @table

Of course the query can be constructed dynamically but does not exactly
fit the needs. The situation is very complicated and would just be
confusing to discuss here so please do not offer suggestions on how to
construct the query programically as that will not work for this
situation.

Thanks

[Thomas Bartkus]

<ry******@yahoo .com> wrote in message
news:11******** **************@ v46g2000cwv.goo glegroups.com.. .

Is it possible to have a dynamic table name within a query or a table
name that is a variable?
No!
This does not work but gives an example:

SELECT * FROM concat('table', 'name')
- OR -

SET @table = 'a';
SELCT * FROM @table

Of course the query can be constructed dynamically but does not exactly
fit the needs. The situation is very complicated and would just be
confusing to discuss here so please do not offer suggestions on how to
construct the query programically as that will not work for this
situation.

Thanks

[Bill Karwin]

ry******@yahoo. com wrote:

Of course the query can be constructed dynamically but does not exactly
fit the needs. The situation is very complicated and would just be
confusing to discuss here so please do not offer suggestions on how to
construct the query programically as that will not work for this
situation.

Table names, column names, etc. cannot be dynamic in the way you
describe. This is not permitted by the SQL language, for many reasons.

For instance, there would be no way for the query optimizer to decide
which index(es) to use, if it doesn't know at parse time which tables
and columns are being queried.

Find another way to solve your problem.

Regards,
Bill K.

[onetwitch]

Actually, it is possible. What you need to do is first create a variable that will contain the name of your table, then put that into the sql statement, except don't put the variable within single quotes:

$dynamic_table_ name = "table"; (of course, this is where you would make it dynamic)

then, unlike the usual method of putting varibles in mysql statements - '$dynamic_table _name' - leave off the single quotes (') and just put
$dynamic_table_ name, i.e. "select * from $dynamic_table_ name......

Is it possible to have a dynamic table name within a query or a table
name that is a variable? This does not work but gives an example:

SELECT * FROM concat('table', 'name')
- OR -

SET @table = 'a';
SELCT * FROM @table

Of course the query can be constructed dynamically but does not exactly
fit the needs. The situation is very complicated and would just be
confusing to discuss here so please do not offer suggestions on how to
construct the query programically as that will not work for this
situation.

Thanks