mysqli_num_rows Returns An Incorrect Number Of Rows

[JustinK101]

Ok, I am not sure what's going on here, maybe a bug but here is the code:

 

$sql = "SELECT count(email)
		            AS counter 
				  FROM users
				 WHERE email = '" . $email . "'
				   AND password = '" . $password . "'";

$result = mysqli_query($sql) or die("Oops");
echo mysqli_num_rows($result);

 

Consistently returns 10, but there is only 1 row returned. the table users has a primary key of email, so there can only be one row with a given email address. Something very strange going on.

 

 

[angelcool]

...mmm, from my perspective your query seems OK, but for the heck of trouble shooting change:

 

count(email)

 

to:

 

*

 

or:

 

email

 

 

Also, does

echo mysqli_num_rows($result);

prints 10, is that what you meant?

 

Run the query on the shell; Worse case scenario I'll think you have a design logic problem.

 

good luck

 

Angel.

 

[printf]

The result is unbuffered so the actual count will not be returned until you retrieve all the rows...

 

change...

 

$result = mysqli_query($sql) or die("Oops");

 

to...

$result = mysqli_query ( $sql, MYSQLI_STORE_RESULT ) or die ( "Oops" );

 

 

Then mysli_num_rows() will work right. When doing COUNT() always use * MYSQLI_STORE_RESULT *, when returning bigger result sets that you need to while(loop) always use * MYSQLI_USE_RESULT *

 

Other notes...

 

When you do a COUNT(?) you are guaranteed a result, so it's better to do...

 

/* procedural style */

$result = mysqli_query ( 'SELECT COUNT(field_to_count_or_*) AS total FROM table...', MYSQLI_STORE_RESULT );

$found = mysqli_fetch_assoc ( $result );

echo $found['total'];

 

 

[JustinK101]

printf,

 

That's crazy you have to add an extra parameter to mysqli_query() for mysqli_num_rows() to work properly. So I have all this logic stored in a db abstraction function, how do I determine if I want to use MYSQLI_STORE_RESULT or MYSQLI_USE_RESULT? Could I just always use MYSQLI_STORE_RESULT without problems/performance hits?

 

By the way, does this only apply to mysqli, if I was using mysql standard would the mysql_num_rows() work fine? Thanks.

[Mchl]

I'm surprised your code works at all

mysqli_query() needs to have db connection handle as first argument.

[angelcool]

Good catch Mchl!

 

Yeah connection handle is missing.

 

mysqli_query($db_link,$sql);

mysql_query($sql,$db_link);

 

 

[Mchl]

While we're at it: mysqli can be used in object oriented way. Makes it impossible to forgot about connection (apart from other advantages). Try it.

[JustinK101]

Sorry guys, I just forgot to put the link, I have it. Still issues though with the wrong number of rows returned.

 

mysqli_query($db_connection, $sql);

[Mchl]

I'm checking this code (well... a bit different, because I'm querying different table obviously) and it echoes 1 as it should.

Maybe you have some other code, that echoes '0' after that?

[angelcool]

Yeah, I always thought it was not the query;

Problem resides in either PHP code or MySQL stored data/table design.

As I said earlier, try running the query from command line.

 

If result is as it should, then it is a PHP code problem, otherwise MySQL problem.

[Mchl]

Try echoing the query just before running it

 

$sql = "SELECT count(email)
                     AS counter
                 FROM users
                WHERE email = '" . $email . "'
                  AND password = '" . $password . "'";

echo $sql;
$result = mysqli_query($sql) or die("Oops");
echo mysqli_num_rows($result);

 

you will see if the query is well formed. You can even run it from console or phpMyAdmin to see if it will return 1 row only

[JustinK101]

Scouring the net, I found this:

 

"The use of mysqli_num_rows() depends on whether you use buffered or unbuffered result sets. In case you use unbuffered resultsets mysqli_num_rows() will not correct the correct number of rows until all the rows in the result have been retrieved. "

 

So this seems to be the case, something related to the unbuffered result. Very odd though. When I get home tonight I will do more tests.

[angelcool]

Have you tried running the query in the console?  ???

[JustinK101]

I don't have console access but I ran it in phpmyadmin SQL window and only 1 row returned.

[Mark Baker]

Is it just me being stupid, or has SQL always worked this way:

 

SELECT COUNT() as counter

    will always return a single row that countains the count value of rows that match the selection criteria.

mysqli_num_rows($result) will give 1, and the value of that single result (counter) will be 10

 

SELECT *

    will return the actual rows of data that match the selection criteria.

mysqli_num_rows($result) will give 10

 

[Mchl]

The OP reports that mysqli_num_rows($result) returns 10 for SELECT COUNT() as counter.

[PFMaBiSmAd]

If the query returns 1 row when executed directly, it is highly likely that something in the php code after that point is responsible for the 0 after the 1. Post more of the relevant code.