->num_rows doesn't work correctly

Question

I don't understand why $amountOfUsers is showing as 0?

This used to work before I moved to the bind_param function... I was only using query() instad of prepare. But this is a lot safer, I just have trouble understand why this doesn't work, and how to fix it.

$stmt = $mysqli->prepare("SELECT id, expire, status, username FROM username WHERE username= ?");
$stmt->bind_param('s', $username);
$stmt->execute();

//Counting results. 0 = Invalid, 1 = Valid
$amountOfUsers = $stmt->num_rows;

The error I am getting is: $amountOfUsers isn't counting the number of results properly.

Try `mysqli_num_rows` instead of `num_rows` as in `$amountOfUsers = $stmt->mysqli_num_rows;` (http://php.net/manual/en/mysqli-result.num-rows.php) — Funk Forty Niner, Sep 10 '13 at 22:49
@Fred-ii- I tried that, I get: Undefined property: mysqli_stmt::$mysqli_num_rows in ... — Arian Faurtosh, Sep 10 '13 at 22:50
Is it possible that the user name doesn't exist in the table? — Gordon Linoff, Sep 10 '13 at 22:53
Why do so few people check the return value of their MySQL queries? How would you know if an error had occurred? Check the return value of your `$stmt->execute()`, and then fix whatever problem is (probably) occurring there. — , Sep 10 '13 at 22:55

FalconC · Accepted Answer · 2013-09-11T08:25:45.597

1

$stmt = $mysqli->prepare("SELECT id, expire, status, username FROM username WHERE username= ?");
$stmt->bind_param('s', $username);
$stmt->execute();
// Store the result (so you can get the properties, like num_rows)
$stmt->store_result();
// Get the number of rows
$amountOfRows = $stmt->num_rows;

// Bind the result to variables
$stmt->bind_result($id, $expire, $status, $db_username);
// Process the variables
while($stmt->fetch()) {
    printf("%d %s %s %s\n", $id, $expire, $status, $db_username);
}

edited Sep 11 '13 at 08:25

answered Sep 10 '13 at 22:51

FalconC

1,358
2
14
22

My next line is "$row = $stmt->fetch_assoc();" doesn't work either. – Arian Faurtosh Sep 10 '13 at 22:55
Is the top one better practice than the bottom block? – Arian Faurtosh Sep 11 '13 at 02:26
@Arian I have changed the code. Now it should be most efficient. – FalconC Sep 11 '13 at 08:26
How vital is $stmt->free_result();? – Arian Faurtosh Sep 11 '13 at 16:55
@Arian It just frees the memory. So it isn't really necessary, but it is recommended. – FalconC Sep 11 '13 at 17:07

score 0 · Answer 2 · answered Sep 11 '13 at 08:42

0

Sometimes things don't go according to plan. Checking result codes and errors available in your library is usually more efficient for troubleshooting than asking strangers, but hopefully this stranger can help... choose one of these patterns:

A:

$result = $stmt->execute();
if (!$result) { /* handle errors */ }

B:

$stmt->execute();
if ($stmt->errno != 0) { /* handle errors */ }

C (for development troubleshooting only, not code you would leave around):

$stmt->execute();
print_r($stmt->error_list);

More info here and associated pages: http://www.php.net/manual/en/mysqli-stmt.errno.php

answered Sep 11 '13 at 08:42

dkamins

21,450
7
55
59

P.S. My guess is that your table is not called "username" – dkamins Sep 11 '13 at 08:43
it is. it was working perfectly fine before I prepared my statement. I needed: $stmt->store_result(); – Arian Faurtosh Sep 11 '13 at 16:45

Your Common Sense · Answer 3 · 2013-09-11T08:42:06.883

I would never in my life understand why php users are so inclined to the number of rows returned.
Especially if used only as a flag... if any data returned!

Why not to take the very returned data and see?

$sql  ="SELECT id, expire, status, username FROM username WHERE username= ?s";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('s', $username);
$stmt->execute();
$res = $stmt->get_result();
$row = $res->fetch_assoc();
if ($row)
{
    // do whatever
}

I would never understand an inclination to long and windy codes as well.
Why not to get yourself an abstraction library and get everything in one single line?

$sql = "SELECT id, expire, status, username FROM username WHERE username= ?";
if ($row = $db->getRow($sql))
{
    // do whatever
}

PHP devs don't use `num_rows` as a flag to see if data is returned. In OP's case, he wants to know if there's _exactly_ one row with the username, and not zero rows (user isn't registered) or multiple rows (something wrong with the table). The latter returns data, but it shouldn't be used. — FalconC, Sep 11 '13 at 17:14

->num_rows doesn't work correctly

3 Answers3