Mysqli "select" statement produces no results - but "update" statement with the same "where" works. Why?

Question

In the following code, I run two queries, one to pull the first and last names of a user from the database, and a second to write to a different field in the same entry in the database. The first query produces no results (and if I were to uncomment the $rowCount line and the line below, the script would abort there 100% of the time). The second query works perfectly.

Anyone have a theory about why?

$db = new mysqli($db_host, $db_user, $db_pass, $db_name, $db_port);

$user = get_post_var('email');
$user = preg_replace('/[^A-Za-z0-9@._-]/', '', $user);
$code = substr(str_shuffle("0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"), 0, 20);

($stmt = $db->prepare('select first, last from users where user = ?'));
$stmt->bind_param('s', $user);
$stmt->execute();
$stmt->bind_result($first,$last);
//$rowCount = mysqli_num_rows($stmt); 
//if($rowCount == 0) { fail("19","forgot"); } else {}
$stmt->close();

($stmt = $db->prepare('update users set reset = ? where user = ?'));
$stmt->bind_param('ss', $code, $user);
$stmt->execute();
$stmt->close();

mail( "$user", "Example.org Password Reset Code", "Dear $first $last,\n\n Please visit the following url to reset your password:\n http://www.example.org/reset.php?c=$code\n\nSincerely,\nSender", "From: noreply@example.org" );
fail('18',"forgot");

$db->close();   

Answer 1

In order to use num_rows on prepared statements you need to call store_result first.

The manual also mention the above.

here is a simple example:

<?php
// Your database info
$db_host = 'localhost';
$db_user = '';
$db_pass = '';
$db_name = '';
$code = substr(str_shuffle("0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"), 0, 20);

if (!isset($_POST['email']))
{
    die('Please fill in the email field.');
}

if (!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL))
{
    die('Invalid email address');
}

$con = new mysqli($db_host, $db_user, $db_pass, $db_name);
if ($con->connect_error)
{
    die('Connect Error (' . mysqli_connect_errno() . ') '. mysqli_connect_error());
}

$sql = "SELECT first, last FROM users WHERE user = ?";
if (!$result = $con->prepare($sql))
{
    die('Query failed: (' . $con->errno . ') ' . $con->error);
}

if (!$result->bind_param('s', $_POST['email']))
{
    die('Binding parameters failed: (' . $result->errno . ') ' . $result->error);
}

if (!$result->execute())
{
    die('Execute failed: (' . $result->errno . ') ' . $result->error);
}

$result->store_result();
if ($result->num_rows == 0)
{
    die('No username found...');
}

$result->bind_result($first, $last);
$result->fetch();

// After using fetch, we can print the data
echo $first, " => ", $last;

$result->close();

$update = 'UPDATE users SET reset = ? WHERE user = ?';
if (!$stmt = $con->prepare($update))
{
    die('Update query failed: (' . $con->errno . ') ' . $con->error);
}

if (!$stmt->bind_param('ss', $code, $_POST['email']))
{
    die('Update binding parameters failed: (' . $stmt->errno . ') ' . $stmt->error);
}

if (!$stmt->execute())
{
    die('Update execute failed: (' . $stmt->errno . ') ' . $stmt->error);
}

$stmt->close();
$con->close();

if (mail($_POST['email'], "Example.org Password Reset Code", "Dear $first $last,\n\n Please visit the following url to reset your password:\n http://www.example.org/reset.php?c=$code\n\nSincerely,\nSender", "From: noreply@example.org"))
{
    echo "Email sent";
}
else
{
    echo "Failed to send email";
}