Is there ever a case to use mysql_num_rows

[lorenw]

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.

$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

[oddz]

Well one introduces a second trip to the db and replicated code and the other does not What do you think?

[lorenw]

kk, guess that answers that.

[transio]

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:

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.

[r937]

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

[lorenw]

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.

[transio]

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.