Results 1 to 15 of 15
  1. #1

    What is the best way to count SQL rows in PHP?

    I am creating a SQL Query that doesn't need to return any results from the database. All I need to do is count how many rows there are. Is there a "preferred" way of doing this, or is this the best way:

    $sql = mysql_query("SELECT id FROM table WHERE stuff > 123");
    $sql_count = MYSQL_NUMROWS($sql);

    I have a lot of these to do, so I wanted to make sure this is the most efficient way to do a SQL count in PHP.
    TextAdMarket - http://www.TextAdMarket.com
    Supply & Demand Advertising

  2. #2
    Join Date
    Dec 2007
    Location
    Lebanon
    Posts
    413
    it is mysql_num_rows($sql); and it is the fastest way

  3. #3
    Join Date
    Feb 2003
    Location
    AR
    Posts
    2,381
    What?

    SELECT COUNT(*) as number FROM table WHERE whatever > 1234;

    http://forums.mysql.com/read.php?115,55854,55854

    If all you're doing is counting the rows, use mysql's built in function to do the grunt work, rather than fetching every single row and counting them.
    Last edited by ThatScriptGuy; 02-09-2008 at 08:03 PM.

  4. #4
    Join Date
    Dec 2007
    Location
    Lebanon
    Posts
    413
    Quote Originally Posted by kcackler View Post
    What?

    SELECT COUNT(*) as number FROM table WHERE whatever > 1234;

    http://forums.mysql.com/read.php?115,55854,55854

    If all you're doing is counting the rows, use mysql's built in function to do the grunt work, rather than fetching every single row and counting them.
    yeah you're absolutely right cause that's done internally in mysql, I'm sorry I didn't remark that it needn't returning any results

  5. #5
    Join Date
    May 2007
    Location
    Orange Country, CA
    Posts
    138
    DO NOT use
    Code:
    SELECT COUNT(*) as number FROM table WHERE whatever > 1234;
    Instead ONLY count single field elements (keys preferably)
    Code:
    SELECT COUNT(id) as number FROM table WHERE whatever > 1234;
    Blesta - Professional Billing Software
    We are about creating good experiences
    Trial - Demo | 866.478.7567 | Twitter @blesta

  6. #6
    Are you sure that makes a difference?

    It used be the case in Oracle that "SELECT COUNT(1) FROM..." was faster than "SELECT COUNT(*) FROM...", but that hasn't been true in a long time. The SQL compiler built into Oracle can recognize you're doing a count and avoid doing a lot of heavy lifting not needed.

    I'd be surprised if MySQL didn't do the same...

    -Bill

  7. #7

  8. #8
    Join Date
    Dec 2007
    Location
    Lebanon
    Posts
    413
    but that will select only 1 row true? so if I want for example all the ids I can't use it?

  9. #9
    Join Date
    Feb 2003
    Location
    AR
    Posts
    2,381
    Right - You wouldn't use a limit when counting everything...

  10. #10

  11. #11
    Join Date
    Aug 2001
    Location
    Central USA
    Posts
    200

    Not really true...

    Quote Originally Posted by Codelphious View Post
    DO NOT use
    Code:
    SELECT COUNT(*) as number FROM table WHERE whatever > 1234;
    Instead ONLY count single field elements (keys preferably)
    Code:
    SELECT COUNT(id) as number FROM table WHERE whatever > 1234;
    This is not really true. The only time COUNT(*) vs COUNT(id) makes a difference is when your table does not have an index defined, in which case you're designing your tables wrong. 99% of the time, your table will have an 'id' type column, which is defined as a primary key - an index column. The COUNT(*) function relies on the index column, and will not incur any additional overhead than COUNT(id) to execute.
    InvoiceMore - Online Billing & Invoicing
    phpDataMapper - Object-Oriented PHP5 Data Mapper ORM

  12. #12
    Join Date
    Feb 2003
    Location
    AR
    Posts
    2,381
    Thank you for your clarification Czaries. He had me worried that I'd been doing it wrong for 5 years...scary

    Kevin

  13. #13
    Join Date
    Aug 2001
    Location
    Central USA
    Posts
    200
    Quote Originally Posted by kcackler View Post
    Thank you for your clarification Czaries. He had me worried that I'd been doing it wrong for 5 years...scary

    Kevin
    No problem... Just remember that "COUNT(*)" != "SELECT *". If all you are asking for is a integer count of the records, the specific columns are irrelevant. With SELECT, you are only asking FOR those specific columns so they are very relevant. That is the key difference between the two functions, and they are optimized accordingly.
    InvoiceMore - Online Billing & Invoicing
    phpDataMapper - Object-Oriented PHP5 Data Mapper ORM

  14. #14
    Join Date
    Dec 2007
    Location
    Lebanon
    Posts
    413
    that's obvious

  15. #15
    Join Date
    Feb 2003
    Location
    AR
    Posts
    2,381
    My sarcasm was too subtle in my post, I guess.

    I do appreciate you clarifying for the benefit of everyone here at WHT...The only reason I didn't (I knew that his post was incorrect) was because I just didn't feel like arguing at the time.

    Again, though - Thank you for correcting that mis-information for everyone's benefit.

Posting Permissions

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