Counting values in multiple tables.

[Anon]

Hi fellow php-programmers!

For one of my projects I want to count the nr. of entries in my tables. I know how to do this for 1 table:
SELECT count(*) FROM foo1;

But now I want to do this for 3(or more) tables together. These tables have the exact same layout. Is merge/union of these table and then count(*) from the new (temporary) table a correct way of solving this?

Hope somebody outthere can help me. Any input is very much appreciated!

Tom

[Anon]

I don't think so.
You may be able to do a join and count(field) on fields that are unique to each table (but you probably don't have any if the layouts are the same.

I think doing three (or more) queries is the simplest.

[trellie]

It's dificult... but by subsequently executing the folowing sql statements you can get what you want:

select @t1:=count(field) from table1;
select @t2:=count(field) from table2;
select @t3:=count(field) from table3;

and than as the final statement:

select @t1+@t2+@t3 as total;

where 'total' will represent the total number of rows from table1, table2 and table3 combined.

however, i doubt if this will be faster then using php...