How to count all entries in a mysql database ?

[chrisdee]

I wonder how I can count all entries in a mysql database with PHP, not just in a table, but the whole database ?

[bpat1434]

run individual counts on each table in the database, then add them together. Or do subqueries and add all the results together:

SELECT
    (SELECT COUNT(*) FROM `table1`) AS cnt1,
    (SELECT COUNT(*) FROM `table2`) AS cnt2,
    (SELECT COUNT(*) FROM `table3`) AS cnt3
FROM `table1`

I don't particularly see any other way around it. MySQL may have a query you can run, but I didn't find it in my short perusal.

[chrisdee]

I nearly managed to do what I'm after with the script below. The problem is that it lists out the number of entries in each table, and not the total sum of entries from all the tables like I would like it to do
Note that I havent posted the connection part.

while ($check = mysql_fetch_row($result_tbl)) {
 $query = "select id from $check[0]";
 $result = mysql_query($query) or die(mysql_error());
 $num_rows = mysql_num_rows($result);
 if(eregi("home|css|familietre|links|stats|telefonliste|text", $check[0])) {} else {
 echo $num_rows." "; # $check[0]           

}}

[bpat1434]

There's a simpler way, and you won't run all those giant queries. Just use the format I gave you above. You can even refine it to something like this:

<?php

$database = 'database_name';

$conn = mysql_connect('localhost', 'username', 'password');
mysql_select_db($database);

$result = mysql_query("SHOW TABLES");

$query = 'SELECT (';

while($row = mysql_fetch_row($result))
{
    $query .= "\n (SELECT COUNT(*) FROM `{$row[0]}`) +";
}

$query = substr($query, 0, -1)."\n".') AS `cnt`';

$result = mysql_query($query);
$row = mysql_fetch_assoc($result);

echo 'There are '.$row['cnt'].' rows in '.$database.'<br />';
echo 'Query Run: <pre>'.$query.'</pre>';

That'll give you an output like:

There are 136 rows in database_name
Query Run:

SELECT (
 (SELECT COUNT(*) FROM `table_1`) +
 (SELECT COUNT(*) FROM `table_2`) +
 (SELECT COUNT(*) FROM `table_3`) +
 (SELECT COUNT(*) FROM `table_4`) +
 (SELECT COUNT(*) FROM `table_5`) +
 (SELECT COUNT(*) FROM `table_n`) 
) AS `cnt`

To answer your query though... outside of the while loop you have there set a variable $counter to 0:

$counter = 0;
while($check = mysql_fetch_row($result_tbl)) {

Then where you have echo $num_rows." "; // $check[0] add $num_rows on to $counter:

$counter += intval($num_rows);

Then outside the while loop, echo out your result:

echo $counter;

Full example:

$counter = 0;	
while ($check = mysql_fetch_row($result_tbl))
{
	$query = "select id from $check[0]";
	$result = mysql_query($query) or die(mysql_error());
	$num_rows = mysql_num_rows($result);
	if(eregi("home|css|familietre|links|stats|telefonliste|text", $check[0])) {} else {
		$counter += $num_rows;
	}
} 
echo $counter;

Also, if you're only looking for a select few tables, I'd suggest you put select query and other stuff inside the if() with the eregi in it. e.g.:

$counter = 0;	
while ($check = mysql_fetch_row($result_tbl))
{
	if(eregi("home|css|familietre|links|stats|telefonliste|text", $check[0]))
	{
		// Do nothing here.
	}
	else
	{
		$query = "select id from $check[0]";
		$result = mysql_query($query) or die(mysql_error());
		$num_rows = mysql_num_rows($result);
		$counter += $num_rows;
	}
} 
echo $counter;

Hope that helps.

[chrisdee]

Very good help indeed. Thank you very much🙂