PHP Code to get Total Rows?

Quick question.. What's the easiest way to get the total number of rows in a table/database? I want to have a php script that just displays the total rows. Something that isn't too resource intensive or inefficient (since it will be executed every few minutes to get updates).

Thanks!

 

$query = mysql_query("SELECT COUNT(*) AS total FROM table_name") OR die(mysql_error());
$result = mysql_fetch_assoc($query);

echo $result['total'];

PHP:

 

mysql_num_rows() is an alternative to this.

Regards,

 

Thanks guys! Trying it out now..

 

Works like a charm! Now we can put up a live member count on our site. Thanks!

 

How resource intensive do you guys think this is? Can I run something like this every minute (or every 30 seconds) without any major impact?

 

How many rows are in the table?

 

There are 500,000 rows in the table with about 500-1,000 added every day.

 

It shouldn't be expensive regardless of the table size. MySQL already "knows" how many rows are in a table. As long as you're counting all the rows without any where clauses it doesn't require any computation.

 

Thanks Jnestor!

 

mysql_num_rows() is the best way to do this, however the first example is a good way as well.

 

actually, for simply counting the number of rows, using mysql_num_rows() would be a waste, because to use that you'd first need to SELECT all rows (therefore loading the data in all those rows to memory).

'SELECT COUNT(*)' is the cheapest in terms of resources usage and is the most logical query to use.

 

How about this one:

$yourQuery = mysql_query ("select * from tabel name", $connection) or die ("the error is:" . mysql_error());
$numAllRow = mysql_num_rows ($yourQuery);

echo "$numAllRow";

Regards...

 

Thanks! Select Count(*) seems the fastest. I now have it on all the www.t35.com pages (the member count at the top). Thanks guys!

 

I have a related question, but dealing with related tables. I have 1 table for users, and another for votes. When someone votes for a user, an entry is made in the votes table with the user's ID.

Is there an easy and elegant way to query which users have the 10 most votes and how many each of the top 10 got?

I'm sure I could do some ugly, nested recursive search (look at the users table, and for each ID in the users table, count the number of votes, then build an array that keeps track of votes, etc.) but it's stupid and ugly and overhead intensive.

Is there some way to query the votes table and count the number of rows for each id and return the results on the top 10? I feel like it's there, but my brain can't do it. HELP!

 

SELECT user_id, COUNT(*) AS n
FROM vote
GROUP BY user_id
ORDER BY n DESC
LIMIT 10;

Code (markup):

 

Its preferable to use count(primary_key) instead of count(*)

MyISAM handles this OK but it can be crunch time if youre using InnoDB.

Just a tip

 

GROUP BY!!

I knew there was a way! Thank you, it works like a charm, and it's so simple.

Life saver...
-Caleb