首次尝试MySQLi准备语句

Question

I'm trying to re-write some mysqli queries as prepared statements (my first attempt at this). The first two commented lines are the old query that worked just fine. The remainder is my attempt to write a prepared statement:

//$sql = "SELECT hashed_password FROM Administrators WHERE user_name='$username'";
//$result = mysqli_query($link, $sql);

//switch to prepared statement
$stmt = mysqli_stmt_init($link);
$result = false;
if (mysqli_stmt_prepare($stmt, 
    'SELECT hashed_password FROM Administrators WHERE user_name=?')) 
{

    /* bind parameters for markers */
    mysqli_stmt_bind_param($stmt, "s", $username);

    /* execute query */
    $result = mysqli_stmt_execute($stmt);

    /* close statement */
    //mysqli_stmt_close($stmt);
}

if (!$result) {
    mysqli_close($link);
    die("Error running query " . mysqli_error($link));
}

if (mysqli_num_rows($result) == 0) {
        mysqli_close($link);
        echo "No such user";
        die();
    }

The error occurs is revealed in the last line, but obviously traces back to one of the earlier lines.

Can somebody tell me what I am doing wrong? I took this directly from the PHP docs, but I'm missing something, obviously. Thank you!

EDIT: Thanks to people who pointed out that I forgot to assign the return value from the query. I've incorporated those comments and modified my code. However, I'm still not able to successfully run the query. Now I get this error:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /home/content/07/11347607/html/RevRunning/scripts/administration/login.php on line 53 No such user

Trust me, the user that I've entered in the UI does exist, so something is still wrong with my code. Thanks again for any help.

Answer 1

After much trial and error, it looks like I had a couple of problems.

First, this is wrong:

/* execute query */
$result = mysqli_stmt_execute($stmt);

There is a command for binding the results to a variable. The correct approach is this:

/* execute query */
mysqli_stmt_execute($stmt);

/* Bind results to variable */
mysqli_stmt_bind_result($stmt, $result);

My other problem was in interpreting the return value (i.e. what is stored in $result). When I was using non-prepared statements, the return value was a dataset that I needed to unwrap by first using mysqli_fetch_array($result). I could test the number of rows in this dataset with mysqli_num_rows. In this situation at least, the return value was just a single string, but I still had to unwrap it with mysqli_fetch_array.

Not with prepared statements, though. The return value now is simply a string, so all I need to do to test if I got something back is something like this:

if (!stmt) { // do before closing $stmt
    mysqli_close($link);
    echo "Query Error";
    die();
}

and

if (!$result) {
    mysqli_close($link);
    echo "No results returned";
    die();
}

This is just based on my own testing and finding a successful solution. If I stated anything incorrectly or if anyone can give a better answer, please feel free to comment.

Thanks for everybody's help.