list table names and row counts in a database

[webgodjj]

Hi,

I am trying to list all the tables with their row counts in a databse... I have been racking my brain on how to do this. So far I have figured out that there is a function mysql_list_tables(), however, this is depreciated and should not be used. So this is my code so far, but I have no idea on how to include my while statments to output a list like so

TABLE NAME  COUNT
table1           1204
table2           1053
table3           5042

<?php
include ("config.php");
if (!mysql_connect('$host', '$user', '$pass')) {
** echo 'Could not connect to mysql' . mysql_error;
** exit;
}
$result=mysql_query('SHOW TABLES FROM $db') or die(mysql_error);
?>

[Redprince]

Should be pretty easy:
- get all tables with 'SHOW TABLES'
- loop over the tables from step #1
- "SELECT COUNT(*) FROM foobar", where 'foobar' is the current table name
- show the data

[webgodjj]

I'm very close to getting this one, however, I can figure out what to add here...

I need the names of the tables in the database as well as each row count... so far this is what I got...

I believe the problem is figuring out what the row-> value is supposed to be?

<?php
include ("config.php");

if (!mysql_connect("$host", "$user", "$pass")) {
    echo 'Could not connect to mysql' . mysql_error();
    exit;
}
$result=mysql_query("SHOW TABLES FROM $db") or die(mysql_error());
while ($row = mysql_fetch_object($result)) {
    $num_rows = mysql_num_rows($result);
    echo $row->$db;
    echo " has $num_rows Rows\n";
}
mysql_free_result($result);
?>

[Redprince]

mysql_connect()
mysql_select_db()
$db_result = mysql_query("SHOW TABLES");
while($table = mysql_fetch_row($db_result))
{
  $db_count = mysql_query(("SELECT COUNT(*) AS `cnt` FROM `" . $table[0] . "`"));
  $cnt = mysql_fetch_assoc($db_count);
  echo "Table '" . $table[0] . "' has " . $cnt['cnt'] . " rows!<br />";
}
mysql_close()

Not tested but should work.

[webgodjj]

Thanks a bunch... this worked out great!

Cheers!

[Aska]

There's also a very useful query: "show table status" which gives you the table name, type, rows, data_length, max_data_length, index_length, auto_increment, create/update time etc.

And did I mention it's pretty fast as well?