How to get total number of rows in a MySQL database?

Question

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

possible duplicate of Get record counts for all tables in MySQL database — Vamsi Krishna B
– Vamsi Krishna B, Commented Apr 5, 2014 at 3:07

Community · Accepted Answer · 2017-05-23 12:33:14Z

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

edited May 23, 2017 at 12:33

CommunityBot

11 silver badge

answered Apr 5, 2014 at 2:58

Aziz Saleh

2,7071 gold badge19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MrMarlow Over a year ago

Up-vote for actually editing reply to include the link to original post here on SO :)

user2864740 Over a year ago

+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 · Accepted Answer · 2014-04-05 03:05:20Z

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.

citizen404 · Accepted Answer · 2014-04-05 03:19:23Z

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

Collectives™ on Stack Overflow

How to get total number of rows in a MySQL database?

3 Answers 3

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Linked

Related