Page 1 of 1

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

Posted: Wed Apr 07, 2004 9:58 pm
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.

Posted: Wed Apr 07, 2004 9:59 pm
by tim
there is:

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

Posted: Wed Apr 07, 2004 10:09 pm
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... ?

Posted: Wed Apr 07, 2004 10:13 pm
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.

Posted: Wed Apr 07, 2004 10:20 pm
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

Posted: Wed Apr 07, 2004 10:22 pm
by tim
Yeah, its a handy command string for such uses. I didnt think speed would be a factor with it.

:)

Posted: Wed Apr 07, 2004 11:17 pm
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...

Posted: Thu Apr 08, 2004 9:19 am
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

Posted: Thu Apr 08, 2004 4:20 pm
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:

Posted: Fri Apr 09, 2004 3:13 pm
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

Posted: Fri Apr 09, 2004 5:01 pm
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.