SEO

#1 (**permalink**) 02-28-2008, 05:10 AM

Ok, here's one. Either its impossible, or someone might
be able to point me to the light.

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.

Thanks in advance.

Michael
--
Michael Fischer Happiness is a config option.
michael@visv.net Recompile and be happy.

#2 (**permalink**) 02-28-2008, 05:10 AM

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@allantgroup.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Slightly bizzare query strategy