为什么mysqli_stmt_num_rows函数返回0？

Question

I am trying to create a login page, but I'm having some issues using prepared statements to secure the login. I have the following code:

$sql = "SELECT * FROM users WHERE user_email=?";
$stmt = mysqli_stmt_prepare($db, $sql);
mysqli_stmt_bind_param($stmt, "s", $email);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$resultCheck = mysqli_stmt_num_rows($stmt);

The problem occurs when checking if the result check variable is less than 1. It shouldn't be 0, but it is. I don't understand why, as the database has an email with the value [e-mail], but when trying to enter that the $resultCheck variable still returns 0. I'm guessing it has to do with the prepared statements.

Answer 1

The client has no idea how many rows are in the result until they are fetched.

You can make the client pre-fetch all rows of the result by using mysqli_stmt_store_result(). Then you can use num-rows.

$sql = "SELECT * FROM users WHERE user_email=?";
$stmt = mysqli_prepare($db, $sql);
mysqli_stmt_bind_param($stmt, "s", $email);
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
$resultCheck = mysqli_stmt_num_rows($stmt);

echo "result num_rows = $resultCheck
";

This echo correctly produces the answer "1".

But if you do use store-result, for some reason you can't also use get-result. So you can't use result methods like fetch_assoc — you have to bind_result into variables by reference and use fetch().

By the way, mysqli_stmt_prepare() takes a statement object as its first argument, not the $db connection. Whereas mysqli_prepare() takes a connection object. Again, a confusing usage of mysqli functions.

---

I don't like mysqli. It's hard to use and has confusing traps of inexplicable behavior. I don't like how bind_param and bind_result make my code seem cluttered.

I prefer using PDO. It's much easier.

$sql = "SELECT * FROM users WHERE user_email=?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$email]);
$result = $stmt->fetchAll();
$rowCount = $stmt->rowCount();