variables are not output from mysqli statement after binding

Question

I'm trying to select things from my database and echo them. For some reason the only thing I can echo is the email. The id and the username doesn't show up. I don't get any errors, they just don't appear.

 $sql = "SELECT id, email, username FROM users WHERE email = ?";
    if ($stmt = mysqli_prepare($link, $sql)) {
      mysqli_stmt_bind_param($stmt, "s", $param_email);
      $param_email = $email;
      if (mysqli_stmt_execute($stmt)) {
        mysqli_stmt_store_result($stmt);
        if (mysqli_stmt_num_rows($stmt) == 1) {
          mysqli_stmt_bind_result($stmt, $id, $email, $username);
          $error = 0;
          $to = $email;
          echo "id:".$id;
          echo $username;
          echo $email;
        } else {
         //some stuff
        }
      } else {
        $error = 1;
      }
    }

What Am I missing?

You need to call `mysqli_stmt_fetch()` to actually fetch the data. Read the [manual for mysqli_stmt_bind_result](https://www.php.net/manual/en/mysqli-stmt.bind-result.php), it already explains this and gives example code. N.B. Hopefully this is obvious, but the `$email` value is appearing because this was already populated before you ran the query. — ADyson, May 26 '20 at 13:07
Thanks guys, I really missed that. How can I accept your answers? — kristof12301, May 26 '20 at 13:09
That's not answers but comments. And the question is considered off topic according to site rules — Your Common Sense, May 26 '20 at 13:13
@YourCommonSense on what basis do you feel it's off-topic? It's not a totally unreasonable question - a lot of questions on this site could probably have been resolved by the original asker reading the manual a bit more closely. But frankly the multitude of commands in mysqli can be a bit baffling, and the official documentation isn't amazing. — ADyson, May 26 '20 at 13:15
@YourCommonSense a typo would be spelling something wrong or leaving out a semi-colon. Not knowing that a specific command is needed isn't a typo, IMHO. And there's enough code for anyone experienced with mysqli to be able to understand the problem. — ADyson, May 26 '20 at 13:18
@ADyson comeon, the OP [perfectly knows](https://stackoverflow.com/questions/61012180/problems-with-foreign-key-ids) how to fetch. and to that extent the man is not that bad. — Your Common Sense, May 26 '20 at 13:19
@ADyson when you forget to call a function you already knows, it's exactly a typo-like problem. A question with a generalized title "Can't echo" won't help anyone else which makes this question rather a deadweight for the site. — Your Common Sense, May 26 '20 at 13:24

score 2 · Accepted Answer · answered May 26 '20 at 13:42

The reason why you cannot see any values fetched from the database is because you forgot to call fetch() after bind_result().

However, you are overcomplicating things. You do not need so many if statements and you should not use bind_result() if you have more than one column. This gets very messy. Use get_result() to fetch the results and then fetch the single row you need(assuming email is unique and your SELECT returns 0 or 1 records).

$stmt = $link->prepare("SELECT id, email, username FROM users WHERE email = ?");
$stmt->bind_param('s', $email);
$stmt->execute();
$result = $stmt->get_result()->fetch_assoc();

if ($result) {
    $error = 0;
    $to = $result['email'];
    echo "id:".$result['id'];
    echo $result['username'];
    echo $result['email'];
} else {
    $error = 1;
}

There is no need for mysqli_stmt_num_rows. The data itself tells you if something was returned or not. This also protects you from a similar problem when using unbuffered queries. It is easy to accidentally omit mysqli_stmt_store_result().

Never check the return value of mysqli_stmt_execute() or mysqli_prepare(). This is a terrible and messy practice. Ensure you have error reporting switched on. See How to get the error message in MySQLi?

variables are not output from mysqli statement after binding

1 Answers1