...

View Full Version : Number of Rows in DB



thesavior
07-06-2007, 07:49 AM
Im looking for a simple way to find the number of rows in a db without having to loop through each table counting the rows up.

Is there a mysql function that does this?

felgall
07-06-2007, 07:57 AM
You can get a COUNT(*) of the number of rows in a table. The rows in different tables are not normally related in a way that makes sense to accumulate a total. There is also a matter of which tables you count if counting all the rows in the database as the only tables visible from a particular userid are those that the particular user has access to and will never be all of the tables in the database.

dhomba
07-06-2007, 09:09 AM
yes use count(*)

e.g: select count(*) as count from tablename;

thesavior
07-06-2007, 05:13 PM
I dont want to just find the number of rows in 1 table though. I want to find all the rows in every table in a database (that the current mysql user has access too.)

felgall
07-06-2007, 10:44 PM
To do that you will have to do the same call for each table since the user probably doesn't have access to read the table that contains the list of tables that would be required in order to set up the select that would return the total count.

phpandmysql
07-07-2007, 01:01 AM
Im looking for a simple way to find the number of rows in a db without having to loop through each table counting the rows up.

Is there a mysql function that does this?


yes use count(*)

e.g: select count(*) as count from tablename;


I dont want to just find the number of rows in 1 table though. I want to find all the rows in every table in a database (that the current mysql user has access too.)


This might do it. Might not be exactly what you want but you can modify it: If you take out the first echo I made it will only show the total number of rows in your entire database.

I took dhomba's suggestion with the count() and used the show tables as an array, so your not really looping as I see you didn't want to do.





$sql = "show tables";
$result = mysql_query($sql);
while ($rows = mysql_fetch_array($result))
{
$NUMROWS = mysql_query("select count(*) from $rows[0]");
$COUNTER = mysql_fetch_array($NUMROWS);

// this will show the number of rows next to each table. //
echo "<br> $rows[0] $COUNTER[0]";

$running_total = $running_total + $COUNTER[0];
}
echo ("<br>Total rows = $running_total");



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum