Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17
  1. #1
    Senior Member iphong's Avatar
    Join Date
    Mar 2003
    Location
    Arlington, VA
    Posts
    751
    Member #
    916
    I'm learning PHP now and i have something to wonder. How to i make a count of number of rows in my table? example: i have a members table with each row is one username, so i want to count the number of members in my database. thank for all help.
    Vu Tuan Phong
    iPhongDesign

    http://iphong.com
    http://adstudio1.com

  2.  

  3. #2
    Senior Member Brak's Avatar
    Join Date
    Apr 2003
    Location
    San Francisco, CA
    Posts
    3,413
    Member #
    1217
    Liked
    2 times
    Code:
    $result = mysql_query("SELECT col_name FROM table_name WHERE 1");
    $count = mysql_num_rows($result);
    So handy, they made a nice little function for it
    Kyle Neath: Rockstar extraordinare
    The blog | The poetry site | The Spore site

  4. #3
    Senior Member iphong's Avatar
    Join Date
    Mar 2003
    Location
    Arlington, VA
    Posts
    751
    Member #
    916
    Ok thank for that!
    Vu Tuan Phong
    iPhongDesign

    http://iphong.com
    http://adstudio1.com

  5. #4
    Senior Member nsr81's Avatar
    Join Date
    Oct 2002
    Posts
    1,132
    Member #
    250
    Liked
    15 times
    or

    Code:
    $result = mysql_query("SELECT count(*) FROM table_name");
    list($count) = mysql_fetch_row($result);
    This is better because you can count the rows without having to select the actual data, which can be a problem if the amount of data is huge.
    There and Back Again :Ogre:

  6. #5
    Senior Member Brak's Avatar
    Join Date
    Apr 2003
    Location
    San Francisco, CA
    Posts
    3,413
    Member #
    1217
    Liked
    2 times
    does it actually do anything different? It seems to me it's doing the same thing... I don't see how it can be much of a difference in speed when mysql has to select the data no matter what (it has to count it somehow)

    BTW I'm very interested in php/mysql optimization.. just a curious question.
    Kyle Neath: Rockstar extraordinare
    The blog | The poetry site | The Spore site

  7. #6
    Senior Member iphong's Avatar
    Join Date
    Mar 2003
    Location
    Arlington, VA
    Posts
    751
    Member #
    916
    I have try [code: mysql_query("SELECT count(*) FROM table_name")] but it seems doesnt work to me.
    Vu Tuan Phong
    iPhongDesign

    http://iphong.com
    http://adstudio1.com

  8. #7
    Senior Member nsr81's Avatar
    Join Date
    Oct 2002
    Posts
    1,132
    Member #
    250
    Liked
    15 times
    @iphong:

    I'm not sure how you are using it in your script, but try issuing this query in phpMyAdmin:
    Code:
    SELECT COUNT(*) FROM table_name
    Copy and paste any errors you might be receiving.

    @Brak:

    I had read it in mySQL manual that count(*) is more optimized if you are just counting rows. After your query, I went looking for that page again, and also raised the question on NYPHP's mailing list. Here is what I got on this:
    mySQL manual:

    COUNT(*) is optimised to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example:

    mysql> SELECT COUNT(*) FROM student;

    http://www.mysql.com/doc/en/Group_by_functions.html
    From NYPHP members:
    - db's are optimized for this query so their return is like greased lightening
    - let the db do the work!
    You want to send as little information between the database and PHP as possible because it's faster. Having mySQL do the count means you're sending a number instead of a whole bunch of [data]. (This [might not be] such a big deal here, since the amount of [data seems] small.)
    Note: Brackets ( [ and ] ) indicate the change of wording I needed to make to fit the context.

    Hope this answers your question. Any other views on the issue are also welcomed
    There and Back Again :Ogre:

  9. #8
    Senior Member Brak's Avatar
    Join Date
    Apr 2003
    Location
    San Francisco, CA
    Posts
    3,413
    Member #
    1217
    Liked
    2 times
    So, another quick question - it says "and there is no WHERE clause" if there is one, would this change things?
    Kyle Neath: Rockstar extraordinare
    The blog | The poetry site | The Spore site

  10. #9
    Senior Member rjahrman's Avatar
    Join Date
    Feb 2003
    Posts
    180
    Member #
    705
    Originally posted by Brak
    does it actually do anything different? It seems to me it's doing the same thing... I don't see how it can be much of a difference in speed when mysql has to select the data no matter what (it has to count it somehow)
    Because mySQL keeps a count going of how many rows you have. When you use count(*), it just uses that number instead of looking in the database. You may not think much of this now, but when you're dealing with 500,000-row tables it's a life-saver.

  11. #10
    Senior Member rjahrman's Avatar
    Join Date
    Feb 2003
    Posts
    180
    Member #
    705
    Originally posted by Brak
    So, another quick question - it says "and there is no WHERE clause" if there is one, would this change things?
    That would count the number of rows meeting the condition. For example:

    select count(*) where people_eaters = 'purple';

    If some cells don't have values, you can also count how many of the cells in a column have values:

    select count(people_eaters)


Page 1 of 2 1 2 LastLast

Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 05:07 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com