I will be querying from many databases and all of the databases that all have a table beginning with word the "DataTable" , but are named differently for example DataTable981911 , DataTable98174, DataTable946. How can I create a select query on table beginning with the word "DataTable" ? Find the table 9 characters in length beginning with the word "DataTable" and then do the select query from it. Thanks for your help.
-
-
As I understand you want the name of all tables beginning with 'datatable' and use these to perform operation on those tables.
You have 2 options:
[code=mysql]SHOW TABLES FROM db_name LIKE 'datatable%';[/code]shows all tables in database 'db_name'
or
[code=mysql]SELECT table_schema, table_name FROM INFORMATION_SCH EMA.TABLES WHERE table_name LIKE 'datatable%';[/code]shows all tables 'table_name' in database 'table_schema'
Ronald -
I am getting tripped up on how to write the From statement. Also how would you deal with aliases when working with multiple tables? Here is an example of my SQL string using a ADODB.Connectio n in VBA-
sSQL = " SELECT a.LDesc, b.ItemID, m.metric1, m.metric2 " & _
" from table_name LIKE 'datatable%' m " & _
" JOIN DimMrkt a ON a.ItemID = m.Mrktkey
" JOIN DimPer b ON b.ItemID = m.Perkey
" GROUP BY a.LDesc, b.ItemID, m.metric1, m.metric2; "Comment
-
I do not understand what are trying to accomplish with that statement. Following your original question there are 2 steps to follow:
1. get names of all tables of which the name starts with 'DataTable' like the statement I showed you, now in a php statement[php]$sql="SELECT table_schema, table_name FROM INFORMATION_SCH EMA.TABLES WHERE table_name LIKE 'datatable%'";
$result=mysql_q uery($sql)
or die ("Select information schema error: ".mysql_error() );
[/php]
2. Then you use the resultset of that select (all table names) to access those tables.
[php]while ($row=mysql_fet ch_assoc($resul t)) {
// get the database name
$database_name= $row['table_schema'];
// get the table name
$tablename=$row['table_name']
// connect to the server
......
// select the database
mysql_select_db ($database);
// do the select on that particular table
$sql1="select .... FROM $tablename .... etc ....";
// process the select
.......
}
[/php]RonaldComment
Comment