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

May 16 '06 #1

Subscribe Post Reply

52277

Thomas Bartkus

<ry******@yahoo.com> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.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

May 16 '06 #2

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.

May 16 '06 #3

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

Jun 6 '06 #4

Similar topics

MySQL Multi Table Delete

by: Gordon | last post by:

I have 2 tables t and t1. In this case, t1 is a copy of t. I want to delete rows from t1 based on criteria on the t table and a relationship between t ad t1 (in this case the id column). In the...

MySQL Database

String / Variable Problem - Dynamic Table Name

by: Martin Feuersteiner | last post by:

Hi I'm grateful for any light you can shed on this!! I've to admit, it's an unusual design but I've multiple contact tables named e.g. i2b_ash_contact or i2b_ted_contact. 'i2b_' and...

Microsoft SQL Server

How to detect if a MySQL db table exists in MySQL 4.1+

by: phillip.s.powell | last post by:

$sql = "SELECT IF((SHOW TABLES LIKE '$subselectTableName'), count(*), NULL) AS numRows FROM $subselectTableName"; I am trying to write a SQL statement that will tell me if a table exists or not,...

MySQL Database

Php MySql dynamic table populate

by: larry777 | last post by:

Greetings, I'm a Php newbie and am developing a photo album site and have the basic framework in place with user registration, login, and then a page that you can upload images to MySql and those...

PHP

Trouble passing mysql table name to php function and using it!

by: kennthompson | last post by:

Trouble passing mysql table name in php. If I use an existing table name already defined everything works fine as the following script illustrates. <?php function fms_get_info() { $result =...

PHP

Newbie Question about dynamic table name references

by: kalyson | last post by:

Hello, I realize that using the TYPE declaration for variables makes their definitions more dynamic, like this: myField tablename.name%TYPE; myRow tablename%ROWTYPE; ...

Oracle Database

AJAX call to a PHP/MySQL script failes if a dynamic table name has a space in it.

by: ArizonaJohn | last post by:

Hello, Below I have some Ajax and the page it points to. This code works great if $_SESSION has no spaces in it (for example, if it is "elpaso"). However, if $_SESSION has a space in it (for...

PHP

Dynamic table name

by: SBCUser666 | last post by:

I have an Access 2003 query that does a SELECT.. INTO table_name. I would like the table_name to be a fixed value plus todays date. Example: tbl_EXTRACT_070709 Can this be done?

Microsoft Access / VBA

dynamic table name:- [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrec

by: smanif1 | last post by:

Report_Name_Final = "TABLENAME" DIM mySQL1 mySQL1 = "SELECT * FROM " mySQL1 = replace(mySQL1,"",Request.Form("Report_Name_Final")) response.write objRS1("Value") trying to get dynamic...

ASP / Active Server Pages

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

Career Advice

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

Microsoft Access / VBA