Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using fetch_assoc on prepared statements (php mysqli)

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.

like image 212
Naxon Avatar asked Jun 24 '16 11:06

Naxon


People also ask

What is the use of Fetch_assoc () in PHP?

Definition and Usage The fetch_assoc() / mysqli_fetch_assoc() function fetches a result row as an associative array. Note: Fieldnames returned from this function are case-sensitive.

Which function is used in Mysqli with prepared statements?

$stmt->bind_param("sss", $firstname, $lastname, $email); This function binds the parameters to the SQL query and tells the database what the parameters are. The "sss" argument lists the types of data that the parameters are.

What is the difference between Fetch_assoc and Fetch_array?

fetch_array returns value with indexing. But Fetch_assoc just returns the the value. here array location 0 contains 11 also this location name is 'id'. means just returns the value.

Can we use prepared statement for select query in PHP?

You must always use prepared statements for any SQL query that would contain a PHP variable. To do so, always follow the below steps: Create a correct SQL SELECT statement.


1 Answers

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
}
like image 69
dimlucas Avatar answered Sep 21 '22 13:09

dimlucas