Using fetch_assoc on prepared statements (php mysqli)

Question

I'm currently working on a login script, and I got this code:

$selectUser = $db->prepare("SELECT `id`,`password`,`salt` FROM `users` WHERE `username`=?");
$selectUser->bind_param('s', $username);
$selectUser->execute();

if ($selectUser->num_rows() < 0)
    echo "no_user";
else
{
    $user = $selectUser->fetch_assoc();
    echo $user['id'];
}

Here's the error I get:

Fatal error: Uncaught Error: Call to undefined method mysqli_stmt::fetch_assoc()

I tried all sorts of variations, like:

$result = $selectUser->execute();
$user = $result->fetch_assoc();

and more... nothing worked.

and if I can't install it, what other options do I have to achieve the same result? — Naxon, Jun 24 '16 at 11:53
If you don't have the mysqlInd driver, this solution worked a treat for me: http://stackoverflow.com/a/28622062/1617737 — ban-geoengineering, Feb 17 '17 at 10:10

score 17 · Answer 1 · answered Jun 24 '16 at 11:58

That's because fetch_assoc is not part of a mysqli_stmt object. fetch_assoc belongs to the mysqli_result class. You can use mysqli_stmt::get_result to first get a result object and then call fetch_assoc:

$selectUser = $db->prepare("SELECT `id`,`password`,`salt` FROM `users` WHERE `username`=?");
$selectUser->bind_param('s', $username);
$selectUser->execute();
$result = $selectUser->get_result();
$assoc = $result->fetch_assoc();

Alternatively, you can use bind_result to bind the query's columns to variables and use fetch() instead:

$selectUser = $db->prepare("SELECT `id`,`password`,`salt` FROM `users` WHERE `username`=?");
$selectUser->bind_param('s', $username);
$selectUser->bind_result($id, $password, $salt);
$selectUser->execute();
while($selectUser->fetch())
{
    //$id, $password and $salt contain the values you're looking for
}

If you get a `Call to undefined method mysqli_stmt::get_result` error look into this answer: https://stackoverflow.com/a/8343970/2056125 — mhellmeier, Feb 25 '19 at 23:29

score 0 · Answer 2 · edited May 23 '17 at 11:46

1) you need the mysqlInd driver.

The variable $db is of type mysqli_stmt, not mysqli_result. The mysqli_stmt class doesn't have a method fetch_assoc() defined for it.

You can get a mysqli_result object from your mysqli_stmt object by calling its get_result() method. For this you need the mysqlInd driver installed!

Alternative try this

    $selectUser = $db->prepare("SELECT `id`,`password`,`salt` FROM `users` WHERE `username`=?");
    $selectUser->bind_param('s', $username);

     $selectUser->execute();
    $selectUser->bind_result($id, $password,$salt);

    while ($selectUser->fetch()) {
        printf("%s %s %s\n", $id, $password,$salt);
    }

for more info about this Reference link

Your Common Sense · Answer 3 · 2016-06-24T12:41:44.190

Now talk of alternatives.

PDO, unlike mysqli, never have a problem like this. It can fetch you an array out of a prepared statement without the need of installing any additional modules.

$stmt = $db->prepare("SELECT `id`,`password`,`salt` FROM `users` WHERE `username`=?");
$stmt->execute([$username]);
$user = $stmt->fetch();

if (!$user) {
    echo "no_user";
} else {
    echo $user['id'];
}

See, it works exactly the way you would expect and require two times less code to write. Not to mention other wonderful features.

Using fetch_assoc on prepared statements (php mysqli)

3 Answers3

Linked

Related