Counting MySQL Tables

[lewismichaelbbc]

Hi, I need some help. I am trying to show in the admin panel how many tables there are in a database...

My database is called paidtosi_crusad and I am wanting to count all of the tables in pts_ads and then return a number, with the amount, in a table.

Please could someone guide me?

Thanks

[sunfighter]

It is hard to know if you want to count the number of TABLES in your database or the number of ROWS in a table.
This gives you both. The number of tables and then their names in your database.
And then it gives you the number of rows in a table.

PHP Code:

<?php
require ('./inc/DB_connect.php'); // LINK TO YOUR DATABASE HERE DON'T USE MINE

$query = "SHOW TABLES FROM paidtosi_crusad";

$result    = mysql_query($query);
$num_of_tables = mysql_num_rows($result);
echo $num_of_tables."<br />";
while($showtablerow = mysql_fetch_array($result))
{
    echo $showtablerow[0]."<br />";
}

echo "========================<br />";
$result = mysql_query("SELECT * FROM pts_ads");
$num_rows = mysql_num_rows($result);
echo "$num_rows Rows in pts_ads";
?>

[Old Pedant]

If you are going to show the number of rows in one table, why not show the number of rows in *EACH* table???

And this is a terrible way to find the number of rows:

$result = mysql_query("SELECT * FROM pts_ads");
$num_rows = mysql_num_rows($result);

If you had millions of records in that table, it would take a *LONG* time to run!

So...

$query = "SHOW TABLES FROM paidtosi_crusad";
$result    = mysql_query($query);
$num_of_tables = mysql_num_rows($result);
echo $num_of_tables."<br />";
while($onetable = mysql_fetch_array($result))
{
    $tblname = $onetable[0];
    $sql = "SELECT COUNT(*) FROM `" . $tblname . "`";
    $tresult = mysql_query($sql);
    $tcount = mysql_fetch_array($tresult);
    echo $tblname . ": " . $tcount . " records<br/>\n";
}

Remembering, please, that I don't use PHP. So I could well have a PHP typo in that.

[Old Pedant]

Another way to do this, if you'd like that names of the tables and, say, how many columns are in each table:

$sql = "SELECT TABLE_SCHEMA, TABLE_NAME, COUNT(*) AS columnCount "
     . " FROM information_schema.COLUMNS "
     . " GROUP BY TABLE_SCHEMA, TABLE_NAME " 
     . " ORDER BY TABLE_SCHEMA, TABLE_NAME ";
$results = mysql_query( $sql );
while($onetable = mysql_fetch_array($result))
{
    $dbname = $onetable[0];
    $tblname = $onetable[1];
    $colcount = $onetable[2];
    echo $dbname . ".`$tblname . "`: " . $tcount . " columns<br/>\n";
}

The information_schema database contains many useful tables that will give you the META-DATA about your databases and tables.

[ahamadhussain]

Hai...
The sql coding worked properly... I have already worked the count coding...
SELECT COUNT(*) FROM tablename WHERE user=5