Need php/mysql command that returns # of records in a table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
kfiralfia
Forum Newbie
Posts: 7
Joined: Thu Oct 09, 2003 10:10 pm

Need php/mysql command that returns # of records in a table

Post by kfiralfia »

I built an interactive website using a mysql database and php, and I need a quick and easy way of calculating the total number of users I have registered.

Since this number is approaching 2000, I'm looking for a command that will return the number of records in a specified table.

So far, I only know of the brute force method:

Code: Select all

function get_number_of_users() {
  $member_count = 0;
  $results_users=mysql_query("SELECT * FROM user_table");
    while ($line_users = mysql_fetch_array($results_users, MYSQL_ASSOC)) {
      $member_count++;
    }
  return $member_count;
 }
There's got to be a better way than this!

Thank you for your help.
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post by tim »

there is:

$sqlc = mysql_query("select * from users");
$carr = mysql_num_rows($sqlc);
echo "there are $carr users total";
kfiralfia
Forum Newbie
Posts: 7
Joined: Thu Oct 09, 2003 10:10 pm

Post by kfiralfia »

tim wrote:there is:

$sqlc = mysql_query("select * from users");
$carr = mysql_num_rows($sqlc);
echo "there are $carr users total";
Thanks tim...

However, won't this eat up a lot of memory/cpu cycles? Let's say I have 10,000 records in the user table, wouldn't the space necessary to store an array with 10,000 records be a lot? If so, is this the only way? I was thinking that perhaps the mysql database stores some sort of fixed latent variable that always records the total number of records within each table, and that there is a function that accesses this value, without having to query and return the entire table's contents... ?
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post by tim »

I used that script on a user database counting over 2k users - never had a slow load issue with it.

its late here and i'm running on lil sleep as is, so my other solution (whether its the best or not) would be to add a auto_increment column in your SQL table.

use this command: http://us2.php.net/mysql_insert_id with the above.

edit: the mysql_num_rows does not return an array, it returned form is a numeric value of the amount of rows of the mysql_query (using the SELECT command only.) I really suggest you try that.
Last edited by tim on Wed Apr 07, 2004 10:20 pm, edited 1 time in total.
kfiralfia
Forum Newbie
Posts: 7
Joined: Thu Oct 09, 2003 10:10 pm

Post by kfiralfia »

Actually, I'll stick with your original solution... the reason why I'm counting members this way as opposed to implementing an auto increment function each time someone registers, is that there are too many opportunities for the number to get off track (deleted members, bugs and what not)

... so doing it this way saves me the energy of implementing a membership count mechanism.

Thanks for the advice, I have implemented your original solution and it seems to be working without any change in speed.

-Kfir
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post by tim »

Yeah, its a handy command string for such uses. I didnt think speed would be a factor with it.

:)
kfiralfia
Forum Newbie
Posts: 7
Joined: Thu Oct 09, 2003 10:10 pm

Post by kfiralfia »

Actually, there is a more efficient solution:

Code: Select all

function get_total_membership() {
  $result=query("SELECT count(id) as count from user");
  $line = mysql_fetch_array($result, MYSQL_ASSOC);
  return $lineї'count'];
 }
This doesn't waste the step of transporting the results into memory...
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

If you just want to know how many records you have COUNT() is probably, as kfiralfia said, the better solution. If you use a normal SELECT and then mysql_num_rows() using

Code: Select all

SELECT * FROM table
means you select every column from every row, totally uneccessary, would be much better as:

Code: Select all

SELECT id FROM table
* just wastes the database's time and should be avoided in most cases.

Mac
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post by tim »

well i "was" thinking along those lines, but - if you delete a row from the table, the auto inc column doesnt reset by default.

Meaning - if you delete 30 people, your results will show 30+ more then what there actually is.

Just my way of thinking, sorry for not telling you that off the bat. :cry:
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

COUNT() counts how many records there are - I think you might be thinking of MAX() which would give you the highest ID value, so COUNT() will be fine no matter how many records are added or deleted :)

Mac
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post by tim »

twigletmac wrote:COUNT() counts how many records there are - I think you might be thinking of MAX() which would give you the highest ID value, so COUNT() will be fine no matter how many records are added or deleted :)

Mac
ahh, I was thinking MAX... oh well, late nights arent good for my brain. excellent job twig.
Post Reply