4

I have many tables in my MYSQL database. I want to be able to echo the total number of all the rows in a database.

So basically I want to have it so the circled sum in the image below is echoed in PHP. enter image description here

This is my current code for just displaying the total rows from one table ($txid). I have tried replaced $txid with * but it doesnt work. Any help is greatly appreciated. Thanks. 

mysql_select_db($dbname);

$result = mysql_query("select count(1) FROM $txid");
$row = mysql_fetch_array($result);

$total = $row[0];
echo $total;

?>
user2864740
  • 60,010
  • 15
  • 145
  • 220
user3462992
  • 175
  • 2
  • 9
  • possible duplicate of [Get record counts for all tables in MySQL database](http://stackoverflow.com/questions/286039/get-record-counts-for-all-tables-in-mysql-database) – Vamsi Krishna B Apr 05 '14 at 03:07

3 Answers3

9

Use the schema:

SELECT SUM(TABLE_ROWS) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = '{DB_NAME}'

That is what phpmyadmin is using.

Source: Get record counts for all tables in MySQL database

Community
  • 1
  • 1
Aziz Saleh
  • 2,687
  • 1
  • 17
  • 27
  • Up-vote for actually editing reply to include the link to original post here on SO :) – MrMarlow Apr 05 '14 at 02:59
  • 1
    +1 While for "this is what phpmyadmin is using" - while *I* wouldn't use such an approach for an accurate count task (in particular, and especially for InnoDB the results may be incorrect), this does explain how to emulate the current observed behavior. – user2864740 Apr 05 '14 at 03:02
1

There is no way to "wildcard" across multiple tables - this is one reason why normalized [row-oriented] designs are good.

Instead, the query (or queries) must be written in such a way that that all the tables are specified manually - that is, there is a separate SELECT per table. These can then be grouped with UNION ALL and SUM'ed, or summed in PHP after running each query individually.

select SUM(i) as total
from (
  select count(*) as i FROM tx
  union all
  select count(*) as i FROM tx2
  -- repeated for each table
) counts

(The SQL can be generated dynamically in PHP or in MySQL from a list of the tables to query.)

If you only need a rough estimate, then the INFORMATION_SCHEMA TABLES.TABLE_ROWS table can be queried - and indeed this presents a row-oriented design. However, at least for InnoDB tables, this is not guaranteed to return the same results as a direct COUNT.

For InnoDB tables, the row count is only a rough estimate used in SQL optimization.

user2864740
  • 60,010
  • 15
  • 145
  • 220
1

Use the INFORMATION_SCHEMA database. It's always a nice way to obtain meta data information:

SELECT SUM(TABLE_ROWS)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'your_database';

And here's your updated PHP code:

mysql_select_db($dbname);

$result = mysql_query("SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$dbname'");
$row = mysql_fetch_array($result);

$total = $row[0];
echo $total;

Read more about INFORMATION_SCHEMA.TABLES

citizen404
  • 1,485
  • 1
  • 10
  • 19