Select from all tables

Question

I have a lot of tables in my data base all with same structure. I want to select from all tables without having to list them all like so:

SELECT name FROM table1,table2,table3,table4

And I tried but this doesn't work:

SELECT name FROM *

Is there a way to select all tables in a database without listing each table in the query?

Answer 1

i am working on a online file browser, each directory has its own table

It is very unuseful due to one reason: when you have about 200 files (this situation is real, yeah?) you have about 200 tables. And if there are about thousand files in each directory.. etc. In some time you will either have slow processing while selecting from your database either have to buy more server resources.

I think you should change your database structure: just begin from adding parent_folder_id column to your table, after this you can put all your rows (files and directories -- because directory is a file too -- here you can add type column to determine this) into the one table.

Answer 2

As far as I know there are no such wildcards to select from *all tables. I would recommend writing a view and then call that view instead (it will save you writing out the names every time) – VoodooChild

Answer 3

That means you should not have a lot of tables with same structure at all.
But just one table with a field to distinguish different kinds of data, whatever it is.

Then select all would be no problem.

Answer 4

I found a solution, but I would still like to know if there is a simpler way or a better solution.

But here's what I came up with:

$tables = mysql_query("show tables");
$string = '';
while ($table_data = mysql_fetch_row($tables)){
    $string.=$table_data[0].',';
}   
$ALL_TABLES = substr($string,0,strlen($string)-1);
$sql="SELECT name FROM $ALL_TABLES ";