SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,101
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Is there ever a case to use mysql_num_rows

    Hi all,

    I have googled and know that SELECT COUNT(*) is more efficient than mysql_num_rows. My question, is there ever a case to use mysql_num_rows.
    This is what I'm looking at.

    Code:
    $q = "SELECT name, email FROM users WHERE state = 'Florida'"
    $res = mysql_query($q) or die(mysql_error()); 
    	if(mysql_numrows($res) == '0'){
    	// no results, don't do anything
    	}
    	else{
    		while ( $row = mysql_fetch_assoc($res) ) { 
    		echo  $row["name"].' '.$row["email"];
    		}
            }
    
    Or does this make more sense?
    
    $q = "SELECT COUNT(*) as ucount FROM users WHERE state = 'Florida'"
    $res = mysql_query($q) or die(mysql_error()); 
    $row = mysql_fetch_assoc($res);
        if ($row["ucount"] == '0'){
        // no results, don't do anything
        }
        else{
        $q = "SELECT name, email FROM users WHERE state = 'Florida'"
        $res = mysql_query($q) or die(mysql_error()); 
    		while ( $row = mysql_fetch_assoc($res) ) { 
    		echo  $row["name"].' '.$row["email"];
    		}
    
        }
    This is something that I have been wondering about and looking forward to a final answer.

    Thank you
    Loren
    What I lack in acuracy I make up for in misteaks

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    16 Post(s)
    Tagged
    4 Thread(s)
    Well one introduces a second trip to the db and replicated code and the other does not What do you think?
    The only code I hate more than my own is everyone else's.

  3. #3
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,101
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    kk, guess that answers that.
    What I lack in acuracy I make up for in misteaks

  4. #4
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You only want to use a separate COUNT query when getting a count on a query condition that you're going to LIMIT in your results, e.g. for pagination.

    Example:

    Code:
    SELECT COUNT(1) FROM users WHERE state = 'Florida';
    
    SELECT name, email FROM users WHERE state = 'Florida' ORDER BY email ASC LIMIT 0,10
    If all you want is a count on the records you've already retrieved, mysql_num_rows is more efficient.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by transio View Post
    You only want to use a separate COUNT query when getting a count on a query condition that you're going to LIMIT in your results, e.g. for pagination.
    actually, it's not necessary there

    use SQL_CALC_FOUND_ROWS in the SELECT query and then use the FOUND_ROWS function after the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,101
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Straight from PHP.net >> mysql_num_rows, the very first comment at the bottom.
    Note: If you already have a $result, use mysql_num_rows() on it otherwise use SQL count(). Don't SELECT data just for a count.
    That was the answer I needed.

    Thank you for your replies.
    What I lack in acuracy I make up for in misteaks

  7. #7
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    actually, it's not necessary there

    use SQL_CALC_FOUND_ROWS in the SELECT query and then use the FOUND_ROWS function after the query
    Nice tip. I'll have to try that out.


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
  •