SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Enthusiast Guni's Avatar
    Join Date
    Jun 2001
    Location
    London (mostly)
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    extracting result from COUNT()

    Hi guys

    I have a general question about extracting the result from COUNT().

    I am using the code below and it's working fine but is there another option. How would you do it?


    //Count all Users

    $result = mysql_query("SELECT COUNT(*) FROM Users");
    $row = mysql_fetch_array($result);
    echo ("<p>There are $row[0] users in the database.</p>\n");

    Thanks

    Guni(x)

  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)
    Either alias it or use mysql_fetch_row() or use mysql_result

    PHP Code:
    $result mysql_query("SELECT COUNT(*) FROM Users"); 
    $row mysql_fetch_row($result); 
    echo (
    "<p>There are $row[0] users in the database.</p>\n"); 
    or

    PHP Code:
    $result mysql_query("SELECT COUNT(*) as total FROM Users"); 
    $row mysql_fetch_array($result); 
    echo (
    "<p>There are "$row['total'], " users in the database.</p>\n"); 
    or

    PHP Code:
    $result mysql_query("SELECT COUNT(*) FROM Users"); 
    $count mysql_result($result0); 
    echo (
    "<p>There are $count users in the database.</p>\n"); 
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    SitePoint Enthusiast Guni's Avatar
    Join Date
    Jun 2001
    Location
    London (mostly)
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool man. Thanks.

    Guni(x)

  4. #4
    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)
    just me, the performance freak, being picky again. i wouldn't use mysql_result() b/c it's slower than fetch_row() or fetch_array().
    - 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

  5. #5
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Where did you come up with that?

    From php.net
    When working on large result sets, you should consider using one of the functions that fetch an entire row (specified below). As these functions return the contents of multiple cells in one function call, they're MUCH quicker than mysql_result(). Also, note that specifying a numeric offset for the field argument is much quicker than specifying a fieldname or tablename.fieldname argument.
    Since he is only after one column and one row, I would think mysql_result() would be faster in this case.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  6. #6
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh pleeeeeeese!

    Worrying about the efficiency of extracting an int from a one row, one column result set!



    I look forward to seeing the benchmark results!


  7. #7
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You knew it was coming:


    Test1:
    PHP Code:
    <?
    $db 
    mysql_connect("""""") or DIE(mysql_error());
    mysql_select_db("catalog");
    $sql "SELECT COUNT(*) FROM products";
    $result mysql_query($sql);
    $row mysql_fetch_row($result);
    $count $row[0];
    print 
    $count;
    ?>
    Code:
    Document Length:        302 bytes
    
    Concurrency Level:      100
    Time taken for tests:   4.511 seconds
    Complete requests:      1000
    Failed requests:        0
    Total transferred:      549000 bytes
    HTML transferred:       302000 bytes
    Requests per second:    221.68
    Transfer rate:          121.70 kb/s received
    
    Connnection Times (ms)
                  min   avg   max
    Connect:        0     1    16
    Processing:    58   427   689
    Total:         58   428   705
    Test2:
    PHP Code:
    <?
    $db 
    mysql_connect("""""") or DIE(mysql_error());
    mysql_select_db("catalog");
    $sql "SELECT COUNT(*) as total FROM products";
    $result mysql_query($sql);
    $row mysql_fetch_array($result);
    $count $row[total];
    print 
    $count;
    ?>
    Code:
    Document Length:        304 bytes
    
    Concurrency Level:      100
    Time taken for tests:   4.228 seconds
    Complete requests:      1000
    Failed requests:        0
    Total transferred:      551000 bytes
    HTML transferred:       304000 bytes
    Requests per second:    236.52
    Transfer rate:          130.32 kb/s received
    
    Connnection Times (ms)
                  min   avg   max
    Connect:        0     5    44
    Processing:    52   394  3243
    Total:         52   399  3287

    Test3:
    PHP Code:
    <?
    $db 
    mysql_connect("""""") or DIE(mysql_error());
    mysql_select_db("catalog");
    $sql "SELECT COUNT(*) as total FROM products";
    $result mysql_query($sql);
    $count mysql_result($result0);
    print 
    $count;
    ?>
    Code:
    Document Length:        305 bytes
    
    Concurrency Level:      100
    Time taken for tests:   4.053 seconds
    Complete requests:      1000
    Failed requests:        0
    Total transferred:      552000 bytes
    HTML transferred:       305000 bytes
    Requests per second:    246.73
    Transfer rate:          136.20 kb/s received
    
    Connnection Times (ms)
                  min   avg   max
    Connect:        0     3    27
    Processing:    32   379  3315
    Total:         32   382  3342


    There you have it, although there are so many factors affecting this sort of test, that I thnk its too minimal a difference to really tell. However array and row are definitely not any faster, in fact they appear to be slower.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  8. #8
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And the conclusion -> for practical purposes it doesn't matter!

    BTW, can you tell me how you ran those benchmarks - I've got no idea how to benchmark PHP, or anything really. Would like to do some benchmarking of my scripts.

  9. #9
    SitePoint Enthusiast Stallion's Avatar
    Join Date
    Jan 2001
    Location
    Cumberland, RI, US
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think the benchmark was done with ApacheBench, which is supposed to be included with Apache.

    Regarding the results, its funny that fetch_array would be quicker than fetch_row. fetch_array returns twice the number of values, as it provides both numeric and associative arrays.
    /* Chris Lambert - chris@php.net
    WhiteCrown Networks, CTO - Web Application Security
    vBulletin, Security Programmer - Instant Community
    */

  10. #10
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Like I said, its probably not the best tool to be judging off of. Since it fluxuates according to net congestion, server load and other factors. Point being they are all pretty much the same. I think mysql_fetch_row() and mysql_fetch_array() are about the same because you can specify to only return the associative keys in the array by doing mysql_fetch_array(MYSQL_ASSOC); mysql_result() is good for when you only need to pull one row one column from the database.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  11. #11
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,810
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    <---- who thought he was at last getting a handle on all this, watches idly as the ascii glides gracefully over his head........
    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS

  12. #12
    ********* *********** Jason Donald's Avatar
    Join Date
    Jun 2000
    Location
    Melbourne, Victoria, Australia
    Posts
    182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You guys are amazing. :)

  13. #13
    SitePoint Enthusiast Guni's Avatar
    Join Date
    Jun 2001
    Location
    London (mostly)
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hehe yeah :-)

  14. #14
    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)
    hmm, interesting... i too was surprised to see array() faster than row(). but as you said it can vary. you know, rather than using mysql_fetch_array(MYSQL_ASSOC), you can just use mysql_fetch_assoc().

    hey freddy would that bench program run on the Win version of Apache or not do you think? probably not but i thought i'd ask.

  15. #15
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It doesn't come with the windows distro, I suppose if you compiled your own version of apache on windows using Visual C++ or cygwin or something you may be able to cinlude it, but its not standard on windows.

    you can just use mysql_fetch_assoc().
    Of course I hate when people throw out lame lines like I am about to do, but this function is only available on PHP 4.0.3 and up. For many on shared servers, they might not have that high a version. So for backward compatibility, mysql_fetch_array(MYSQL_ASSOC) is probbaly the best bet. But then again I think any sysad worth a damn would have the latest version always.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  16. #16
    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
    It doesn't come with the windows distro, I suppose if you compiled your own version of apache on windows using Visual C++ or cygwin or something you may be able to cinlude it, but its not standard on windows.
    i meant like if someone (like you ) sent me the file, would it run on Windows? oh wait, is it actually compiled IN Apache? i was under the impression that it was a seperate file in the bin folder.


    Of course I hate when people throw out lame lines like I am about to do, but this function is only available on PHP 4.0.3 and up. For many on shared servers, they might not have that high a version. So for backward compatibility, mysql_fetch_array(MYSQL_ASSOC) is probbaly the best bet. But then again I think any sysad worth a damn would have the latest version always.
    sorry about that, i didn't notice that in the manual. i'm using other things in my scripts that use feature that are only in .0.3+. i know my host has the latest version and i'm hoping if i ever change that they'll have kept up also.

    those hosts that don't even simply update PHP must just be lazy!

  17. #17
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why don't I just try and move it to windows and see if it works. I'll be right back.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  18. #18
    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
    Why don't I just try and move it to windows and see if it works. I'll be right back.
    so did you try it yet?

  19. #19
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh yeah sorry. Nope it doesn't work, as I suspected. However there are other bebchmarking tools out there that can be run on windows. Check http://www.hotfiles.com
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  20. #20
    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
    Oh yeah sorry. Nope it doesn't work, as I suspected. However there are other bebchmarking tools out there that can be run on windows. Check http://www.hotfiles.com
    ok. i just looked on Hotfiles and didn't find anything.

    is there anything in particular that you know of? if not, don't worry about it. i'll just continue putting things i wanna test in a for loop.


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
  •