Mysqli code to fetch one result and return it

Question

I have a login site where users login with their emails. Each is assigned an auto-increment user_id. I want to fetch the associated user_id from the database and store it in the session so that when they submit content, their user_id can be gotten from the session and submitted with the content. However, my "get_user_id" function is not working to look up the user id from their email address. I've tried using all sorts of mysqli commands from fetch_row, get_result, bind_result, and nothing will return the user_id.

    function get_user_id($email) {
    //$email = mysqli_real_escape_string($this->conn, $email);
    $query = "SELECT id from users where email=?";
    if ($stmt = $this->conn->prepare($query)){
        $stmt->bind_param('s',$email);
        if($result = $stmt->execute()){
            $row=$result->fetch_row();
            return $row['id'];
        } else return "no ID returned.";
    }
}

The error message I am getting now is:

Fatal error: Call to a member function fetch_row() on a non-object

Presumably you've checked the database to make sure the email you are using exists? — Orbling, May 28 '13 at 13:56
Yep! Email address exists. The get_user_id step happens after they log in with their email address (OR create an account) so the email has already been verified at this point. — alsoALion, May 28 '13 at 13:59
If the prepare fails, nothing is returned judging by that code. You might want to output the error message, `$mysqli->error` - do the same for the no ID returned on `execute()` failure. — Orbling, May 28 '13 at 14:04

score 0 · Answer 1 · answered May 28 '13 at 13:57

0

You can write you own fetch result method so which can take parameters to return a single column or full row.

answered May 28 '13 at 13:57

a4arpan

1,828
2
24
41

RickN · Accepted Answer · 2013-05-28T14:09:31.907

The variable $result will contain a boolean - a TRUE or FALSE.

You're trying to call the function fetch_row() on that boolean, which won't work.

If you're using prepared statements, you should use bind_result() and then the fetch() function on $stmt.

Change $row=$result->fetch_row(); to$stmt->fetch(); and in stead of using the variable $row, call bind_result(), as described in the example here: http://php.net/manual/en/mysqli-stmt.fetch.php

Your Common Sense · Answer 3 · 2019-11-04T10:27:17.263

function get_user_id($email) {
    $query = "SELECT id from users where email=?";
    $stmt = $this->conn->prepare($query);
    $stmt->bind_param('s',$email);
    $stmt->execute();
    $stmt->bind_result($id);
    $stmt->fetch();
    return $id;
}

with PDO the code would be 2 times shorter

function get_user_id($email) {
    $query = "SELECT id from users where email=?";
    $stmt = $this->conn->prepare($query);
    $stmt->execute(array($email));
    return $stmt->fetchColumn();
}

Mysqli code to fetch one result and return it

3 Answers3