How to count the number of records in tables?

I know there are queries like:

SELECT COUNT(*) FROM teble WHERE field = "U"

But I need to count the total number of records in forty tables. Of course, you can execute 40 queries in a loop and add the result, but perhaps there is a more beautiful solution?


Answer 1, authority 100%

If you need to count the number of values ​​stupidly of all tables…

// .... 
$res = mysql_query('SHOW TABLES');
//    
while($row = mysql_fetch_row($res)) {
    //       
    $queryArray[] = '(SELECT COUNT(*) FROM ' . $row[0] . ')';
}
//   ...
$countQuery = implode(' + ', $queryArray);
// AS total      mysql_fetch_assoc
$query .= 'SELECT (' . $countQuery . ') AS total';
$res = mysql_query($query);
$res = mysql_fetch_assoc($res);
//   -       
echo $res['total'];

Who needs to easily remake this code to count certain values ​​in the 1st query..

In general, in real development, I would make a view and not take a steam bath 🙂


Answer 2, authority 100%

select sum(cnt) as cnt 
from 
(SELECT count(*) as cnt FROM tab1 
UNION ALL 
SELECT count(*) as cnt FROM tab2 
UNION ALL 
SELECT count(*) as cnt FROM tab3
) as t

Hold)