SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru DeNasio's Avatar
    Join Date
    May 2001
    Posts
    830
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Counting members

    Hello,

    I have a table called "Members" in which I store the information of all the members of my site. Is there an easy way to count the total members in a table?

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use COUNT()


    PHP Code:
    $result mysql_query("select COUNT(idfield) as totalnum from members");
    $totalnum mysql_result($result0); 
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    Texas
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the way I do is is like this:

    PHP Code:
    $result mysql_query("SELECT * FROM users ORDER BY user_id DESC");
    $num_rows mysql_num_rows ($result);

    echo 
    "There are $num_rows members!"
    $num_rows is the number of members in the database. And of course you use that after you connect to the database.
    Mark -
    www.magicscripts.net

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    magicscripts, the only problem I have with that code is, why would you want to pull all the records and all the fields for each record just to get the total number of records. YOu are pulling way more information than you need to. When you use COUNT() you are only returning one record with one field in it. So let's say your table has 15 fields and the table has 10,000 records in it. So you would need to retrieve all 10,000 records just to get a count. That is not very efficient.

    This is exactly what COUNT() was designed for, an efficient method to get the number of records in a table.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freddydoesphp
    Use COUNT()


    PHP Code:
    $result mysql_query("select COUNT(idfield) as totalnum from members");
    $totalnum mysql_result($result0); 
    he could probably use COUNT(*) which is faster than COUNT(idfield), right? i assume since he wants to count the members he wants to count the total rows in the table and not rows with a non NULL idfield (which is what COUNT(idfield) is doing).
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  6. #6
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    By idfield I meant whatever non null unique key field he is using in his table, in which case COUNT(idfield) would be faster than COUNT(*) because COUNT(*) still needs to grab all fields for the record, right?
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  7. #7
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freddydoesphp
    By idfield I meant whatever non null unique key field he is using in his table, in which case COUNT(idfield) would be faster than COUNT(*) because COUNT(*) still needs to grab all fields for the record, right?
    no, COUNT(*) is optimized to return very fast. AFAIK it doesn't even look at the rows. but if you give it a column to COUNT it then has to look at every row to see if any rows are NULL. it's stated in the manual somewhere...

  8. #8
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  9. #9
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Interesting, that is some excellent bit of knowledge, and something I did not know, thanks. BTW I ran some benchmark tests against the theory and sure enough COUNT(*) was faster. Here are the results.

    COUNT(*)
    Code:
    Server Software:        Apache/1.3.12                                      
    Server Port:            80
    
    Concurrency Level:      100
    Time taken for tests:   4.903 seconds
    Complete requests:      2000
    Failed requests:        0
    Total transferred:      1116824 bytes
    HTML transferred:       613438 bytes
    Requests per second:    407.91
    Transfer rate:          227.78 kb/s received
    
    Connnection Times (ms)
                  min   avg   max
    Connect:        0    22   114
    Processing:    57   198  3005
    Total:         57   220  3119

    COUNT(idfield)
    Code:
    Server Software:        Apache/1.3.12                                      
    Server Port:            80
    
    Concurrency Level:      100
    Time taken for tests:   5.287 seconds
    Complete requests:      2000
    Failed requests:        0
    Total transferred:      1119960 bytes
    HTML transferred:       616080 bytes
    Requests per second:    378.29
    Transfer rate:          211.83 kb/s received
    
    Connnection Times (ms)
                  min   avg   max
    Connect:        0    22   106
    Processing:    69   211  3054
    Total:         69   233  3160
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  10. #10
    code addict Abstraction's Avatar
    Join Date
    Apr 2001
    Location
    Des Moines, IA
    Posts
    346
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now, why is it faster?

  11. #11
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Did you read the link Dr. Larry Pepper posted above, it tells you why.

    From mysql.
    COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values. COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause.
    I think its because it doesn't care if any fields are null or not it just counts rows, where count(idfield) would search for null fields along the way. I think. Where is MattR when we need him.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  12. #12
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freddydoesphp
    I think its because it doesn't care if any fields are null or not it just counts rows, where count(idfield) would search for null fields along the way. I think. Where is MattR when we need him.
    that sounds right to me. i don't even know if it looks at the rows (for COUNT(*) i mean). i thought maybe it keeps the track of the number of rows somewhere. maybe the index file perhaps?

    and thanks for the benchmarks! it's cool to see proof.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •