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.

Results 1 to 9 of 9

Thread: count() problem

  1. #1
    Senior Member assassingod's Avatar
    Join Date
    Nov 2002
    Posts
    112
    Member #
    307
    I'm having some problems counting the number of movies in my DB, here is my current code:
    PHP Code:
                $countquery "select count(*) as movie from movies";
                
    $countmovies = @mysql_query($countquery);
                
    $totalmovies number_format($countmovies['movie']); 
    The problem is that the result I always get is 0, instead of 2.

    Any ideas?
    assassingod - vB.org Moderator :ichatsmile:

  2.  

  3. #2
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Looks good to me. I might suggest changing the alias name to "movie_count" instead of "movie" just for legibility and try using COUNT(column_name) instead of COUNT(*) for performance.

    Other than that, I would check to see that you actually HAVE 2 rows in your table. Run another query "SELECT row_name FROM movies" and see that you actually get two rows from it.

  4. #3
    Senior Member assassingod's Avatar
    Join Date
    Nov 2002
    Posts
    112
    Member #
    307
    Ok, i'll try that - thanks
    assassingod - vB.org Moderator :ichatsmile:

  5. #4
    Senior Member nsr81's Avatar
    Join Date
    Oct 2002
    Posts
    1,132
    Member #
    250
    Liked
    15 times
    hey transio, I thought it was settled a while ago that count(*) is better than count(column) if there is no WHERE clause.

    http://www.webdesignforums.net/showt...&threadid=6658

    ffdcsite: mysql_query function returns an resource identifier, you have to fetch the data from there yourself.
    PHP Code:
    $countquery "select count(*) as movie from movies";
                
    $countmovies mysql_fetch_array(mysql_query($countquery));
                
    $totalmovies number_format($countmovies['movie']); 
    There and Back Again :Ogre:

  6. #5
    Senior Member seanmiller's Avatar
    Join Date
    Sep 2003
    Location
    Glastonbury, UK
    Posts
    868
    Member #
    3263
    Liked
    1 times
    That was a slightly different thread, in that the question was should one use count(*) or the mysql_num_rows function....

    The truth is that count(col) or count(*) is unlikely to make a lot of difference in terms of performance, but assuming that MySQL adheres to the ANSI standard the two could give different answers.

    The reason is that count(col) only counts rows where there is *data* in that column... count(*) will return the total number. So if you have a column that may or may not be populated (eg. telephone number) then count(*) will tell you how many rows there are on the table, count(telno) how many rows there are on the table with a telephone number entered.

    For the record, that previous thread is actually not 100% correct because it has overlooked the fact that count(*) does not need to look at the table at all - it can ascertain the number of rows directly from the data dictionary, or in the case of queries with a 'where' clause on an indexed column, the appropriate index. So, in summary, I would agree that using mysql_num_rows is an inefficient way to count rows *unless* you are actually going to select them anyway (eg. conditional logic depending on how many rows are returned).

    Sean

  7. #6
    Senior Member assassingod's Avatar
    Join Date
    Nov 2002
    Posts
    112
    Member #
    307
    nsr81;
    Cheers, I didnt think about using mysql_fetch_array (I usually do as well)

    Using count(col) would mess up the count since I may want to delete some rows within the table. (I think anyway)
    assassingod - vB.org Moderator :ichatsmile:

  8. #7
    Senior Member nsr81's Avatar
    Join Date
    Oct 2002
    Posts
    1,132
    Member #
    250
    Liked
    15 times
    Originally posted by seanmiller
    For the record, that previous thread is actually not 100% correct because it has overlooked the fact that count(*) does not need to look at the table at all - it can ascertain the number of rows directly from the data dictionary, or in the case of queries with a 'where' clause on an indexed column, the appropriate index. So, in summary, I would agree that using mysql_num_rows is an inefficient way to count rows *unless* you are actually going to select them anyway (eg. conditional logic depending on how many rows are returned).
    I thought that's where the performance comes in. count(*) does not scan the table and therefore is probably a little bit faster. Also, your example is not applicable if the col(telno) can be null and you just want a total number of clients (including those who don't have a telephone number).

    I'm not sure if you went through the whole thread, but that fact was mentioned by rjahrman in his first post in that thread
    There and Back Again :Ogre:

  9. #8
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    Originally posted by transio
    Looks good to me. I might suggest changing the alias name to "movie_count" instead of "movie" just for legibility and try using COUNT(column_name) instead of COUNT(*) for performance.
    In MySQL, selecting COUNT(*) without a WHERE clause is very efficient. It just gets crippled when you add a WHERE.
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  10. #9
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    The query SELECT COUNT(indexed_primary_key_column) is significantly faster than SELECT COUNT(*).

    Similarly to how SELECT indexed_primary_key_column would be faster than SELECT *

    Granted, you won't see any differences in small tables with 4 columns and 100 rows, but when you get into reporting and complex joins, you will quickly notice the difference. It's just a good practice to get in to.


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:31 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com