Counting Records

fire_cracker

Okay, here is my question of the day: "How Can you Count the Records in every table in a database?"

I manage a link list, and I want to tell my surfers how many links are on the website. I've been searching for quite a while for a code snipped that would help out, but I am not finding much usefull information.

The Database I want to count records for has 40 some tables. I thought about reading the table names into an array (which I can't figure out how to do), and then using the count() function on them, but I'm not getting that to work.

Any ideas?

hi-liter

maybe this is what you are trying to accomplish? didn't say what Database u use, so guessing MySQL...

http://www.mysql.com/doc/en/Counting_rows.html

edit:

never mind, sorry man, i'm done for the day it seems. ugh.
i should read... batting zero.

edit #2:

what if your SQL statement went like this example:

mysql> select student.student_name,COUNT(*)
from student,course
where student.student_id=course.student_id
GROUP BY student_name;

looks like student,course are two tables...

jimson

the count() function in mysql or the count() function in php

to use count() or sizeof() function in php

$a = mysql_fetch_array();
echo sizeof($a);

or
mysql_num_rows(resource);

assuming ur using mysql.

fire_cracker

I was planning on using the count function of mysql, but I need it to run on every table in the database, and keep a cumulative total. Still havn't figured out an easy way to do this.

jimson

use an array to populate ur sql statement and run it..
perhaps u can write a function which accept a database resource parameters / arguments.

stick_figure

Yeah, the way to do it would be to union up the tables with some similar field. Or you could sum() the unions of tables that count() their rows. That's really the only way. I think that the first method is more efficient because mysql is only making one long run through.

fire_cracker

Stick_Figure. Could you throw up a demo or something of what you are talking about. I'm still pretty new to the mysql world. Thanks 🙂

stick_figure

Sure. I use PostgreSQL, but I'm pretty sure this is standard.

SELECT count(*) FROM (
    SELECT something_similar FROM table1 UNION
    SELECT something_similar FROM table2
    ....
);

And you essentially plug all the rows together into one table with one field and count it up. I think that's the fastest way to do it.

Also, just to be a little more explicit, what I mean by something similar is an identical data type. Like int, varchar(x), or something else THE SAME. Otherwise UNION doesn't work.

fire_cracker

That would work, except that the names of the tables would be changing frequently, so I need it to dynamicly find the table names through SHOW TABLES or something along those lines.

stick_figure

Having tables change all the time could be considered bad database design, but I don't know what you're trying to do. I've never had to worry about tablenames changing on me. They're pretty static. I have no idea what SHOW TABLES is and psql doesn't like it. Can't help there...

pete_bisby

I managed to count the records in all tables in a specified database by using the following for MySQL:

	$db = mysql_connect("localhost", "mysql_user", "mysql_password");
	$tableList = mysql_list_tables("database");

for ($i=0; $i < mysql_num_rows($tableList); $i++) {
	$qry = "SELECT COUNT(*) AS recordCount FROM " . mysql_tablename($tableList, $i);
	$res = mysql_query($qry, $db);
	$row = mysql_fetch_array($res);
	printf("Table Name: %s   No. Of Records: %s\n", mysql_tablename($tableList, $i), $row["recordCount"]);
}

Try it and see what you think !

stick_figure

They don't have a function like that for PostgreSQL. Grrrr.... I'm commited to using a VIEW though. Those are awful nice. I mean, you have your user page, and you want their posts, and maybe some other calculations. So you make an efficient VIEW and you'll never have to worry again.

stolzyboy

<?

# Database
$db_name = "mydb";

# [url]http://www.php.net/manual/en/function.mysql-list-tables.php[/url]
$result = mysql_list_tables($db_name);

$total = 0;
while($row = mysql_fetch_row($result))
{
    $this_table = $row[0];

    $query = "select count(*) from " . $this_table;
    $count = mysql_query($query);
    # Increments the value of $total with the number of found rows.
    $total += mysql_result($count, 0);
}

echo "Total rows in all tables in $db_name is " . number_format($total);

?>

fire_cracker

alright guys. Got it figured out. Ended up being way more code then I expected lol. I was hoping there was a magical mySQL function that would do all the work for me (but there hardly ever really is).

Thanks for the help.