0

I currently have 2 tables in my database. (will have 50 when site is complete) On the main page I will have a counter that shows the total number of records displayed on my site/in my database. I am currently able to display the number of rows in each table. I need for this number to be the total number of records in ALL tables in the database. Here is my current code.

Query:

// Query for recently added. Total # of rows in table resources
$mysqli = new mysqli("localhost","root", "", "rnddb");

$query = $mysqli->prepare("SELECT * FROM `resources`");
$query->execute();
$query->store_result();

$recentlyadded = $query->num_rows;

Display:

<?php echo $recentlyadded; // Total # of rows ?>

As of right now this gives me back the total # of rows in the resources table. I need the query to run on all tables and give me back that number combined. Any help would be greatly appreciated! Thanks.

user2106354
  • 27
  • 2
  • 7
  • I saw that one but isn't related the my issue. – user2106354 Feb 19 '14 at 01:37
  • 2
    That is an EXTREMELY bad way to get the row count, because you are requiring the ENTIRE table be sent to PHP. This will fail as soon as your table starts getting to be bigger... Instead, use ``SELECT COUNT(*) FROM `tablename` `` – Niet the Dark Absol Feb 19 '14 at 01:37
  • If a count of all rows in all tables makes any sense it implies that the tables all have similar data in them, which implies in turn that there should be only _one_ table with all the data in. –  Feb 19 '14 at 01:38
  • How would I use the `SELECT COUNT(*) FROM `tablename``? – user2106354 Feb 19 '14 at 01:38

2 Answers2

0

One way you can do it is if your user has access to the command "SHOW TABLES" you can run this command as it will return all tables in your database.

You can run the SQL

SHOW TABLE

and it will return a list of all the tables.. Then you just need to cycle through them either using a foreach or a while statement depending on how you gathered them.

while($table = $query->fetch_array(MYSQLI_ASSOC)) {
  // do another sql
  $sql = "SELECT COUNT(*) FROM ". $table; // or something like that
}
Tutelage Systems
  • 1,724
  • 1
  • 9
  • 6
0

You could achieve this by querying the INFORMATION_SCHEMA database and the TABLES table therein.

Something like this may work:

SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA IN (DATABASE())

Where DATABASE() returns the current database you're using, so if your database is named "mydb" then DATABASE() would return "mydb".

You could do something similar for any database just by replacing that function like this:

SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA IN ('otherdb')

Important:

  1. For InnoDB tables, the row count is only a rough estimate used in SQL optimization.
  2. Querying the INFORMATION_SCHEMA.TABLES table is fast for small tables, but the query may take longer when TABLE_SCHEMA is db with big tables (more than a few GB).
  3. I think it opens the full table file, maybe someone can expand this.

You could learn more here: http://dev.mysql.com/doc/refman/5.5/en/information-schema.html

ediardo
  • 147
  • 5