ansaurus

Question

Answer 1

A:

Sounds like you want to UNION together each table, so you get the results as if they were one big table. You'll need to write out the query in full like

SELECT * FROM table1 UNION SELECT * FROM table2 UNION ... SELECT * FROM tableN

Copy & paste may be your friend here.

I'm curious as to why you have lots of different tables with the same structure?

sasfrog 2010-08-26 03:54:47

i am working on a online file browser, each directory has its own table "files/pictures/,files/videos/,files/music/" and so on records in each table are the files details size name description and so on . originally i had all files listed in one table. but i had over 5,000 records and queries were slow, so now tables are automatically created every time i add a directory, records are automatically stored every time files are uploaded, now i tring to create a search function for users and i wanna search all directories or tables

dlaurent86 2010-08-26 04:08:59

`each directory has its own table` OMG

Col. Shrapnel 2010-08-26 05:47:05

When your query is slow with 5000 records it's either time to do some indexing or re-thing your table structure. Creating a table for each directory will be a more a performance problem than having a single table with proper index and design.

DrColossos 2010-08-26 05:47:07

You have a serious flaw in your design. When queries get slow on a table of just 5K records (which is peanuts for *any* database engine), you should optimize your table structure(s) and queries.

Dennis Haarbrink 2010-08-26 05:48:05

DrColossos- at the most i will have 50 tablesand i didnt mean to say the queries were slow sorryMy script was slow be cuz i had a scan.php script that would loop through all files in directory and subdirectories create a thumbnail for jpg and flv files then insert record into the files table this script would also check files in the files table with file_exists() if it didn't exist it would delete the record. and to be honest thinkin about it now im not sure why i decided to create a table for every directory, i guess it seem like a good idea at the time. but now i scan 1 directory at a time

dlaurent86 2010-08-26 06:39:22

when user click on directory there is a table named after that directory ajax returns all results or (files and folders) from that table. that is why i have a table for every directory. but i realize i may have a bad design. it now works fine and does what i intended it to . And i think ive put way too much time into my bad design to start over. so im going to stick with it for now, But i do appreciate all of your inputs. Thank you

dlaurent86 2010-08-26 06:53:32

Answer 2

+1 A:

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

VoodooChild 2010-08-26 03:55:40

Answer 3

A:

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 ";

dlaurent86 2010-08-26 03:58:32

I still think you want to UNION each table together because specifying "table1,table2,table3" etc. in a FROM clause implies you want to *join* the tables (not the same as appending them sequentially), and by not including any criteria for the join means you will get a cartesian product.

sasfrog 2010-08-26 05:14:51

I don't recommend this approach, but I have one hint for you: http://www.php.net/implode

Dennis Haarbrink 2010-08-26 05:51:07

yeah ok i been up all night trying to get this right. and my solution didn't work cause when i tried to run "SELECT name FROM $ALL_TABLES " got this error"MySQL Error: #1052 - Column 'id' in field list is ambiguous"so i ended up making a search table with all files inside table and i just do a fulltext search on this table

dlaurent86 2010-08-26 06:26:52

Answer 4

+1 A:

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.

Col. Shrapnel 2010-08-26 05:44:55

Answer 5

+2 A:

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.

Konstantin Likhter 2010-08-26 05:52:26

Finally one guy that questions to overall solution.

Yves M. 2010-08-26 07:02:11

ansaurus

tags:

views:

answers:

Select from all tables

related questions