...

View Full Version : Counting MySQL Tables



lewismichaelbbc
01-12-2013, 12:32 PM
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
01-12-2013, 05:50 PM
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
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
01-12-2013, 08:57 PM
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
01-12-2013, 09:28 PM
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
01-17-2013, 03:39 PM
Hai...
The sql coding worked properly... I have already worked the count coding...
SELECT COUNT(*) FROM tablename WHERE user=5



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum