List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:November 8 2006 4:47am
Subject:Re: Slightly bizzare query strategy
View as plain text  
In the last episode (Nov 07), Michael Fischer said:
> Given:
> 
> * A table TABLE_DESC, which is rather like a system table,
> in that it contains the names and column_names of other tables.
> 
> * A table MY_TABLE, which contains "ordinary" data, and has a set
> of rows in TABLE_DESC.
> 
> What I wish to do is to do 
> 
> SELECT MY_TABLE.*
> 
> but without knowing that the name of the table is "MY_TABLE" in
> advance of submitting the query to the db, but rather discovering its
> name from TABLE_DESC in the same query.
> 
> Perhaps this can be done with some combination of setting variables
> within a function or procedure, and views, but I haven't been able to
> work out a way to do it yet.
> 
> The minimal
> 
> set @tname := (SELECT table_name FROM table_desc WHERE id = :id);
> SELECT * FROM @tname; 
> 
> certainly doesn't work....although @tname does contain the expected value.
> 
> Playing games with subqueries has been equally unsuccessful.

You'll need to use a prepared statement to build your query:

http://dev.mysql.com/doc/refman/5.0/en/sqlps.html

Note that you can't use placeholders for the table name itself, so
you'll need to concatenate it into the query string before preparing
it.

-- 
	Dan Nelson
	dnelson@stripped
Thread
Slightly bizzare query strategyMichael Fischer8 Nov
  • Re: Slightly bizzare query strategyDan Nelson8 Nov