Fetching in prepared statements

Question

Im trying to convert the following to predicted statements. Can you please tell me where Im going wrong.

$userid = mysqli_real_escape_string($con, $_SESSION['usr_id']);
$user = mysqli_query($con, "SELECT * FROM users WHERE id = '" . $userid . "'");
$row = mysqli_fetch_array($user);

I have no luck trying to convert this. What I have so far:

$userid = mysqli_real_escape_string($db, $_SESSION['usr_id']);
$userinfo = $db->prepare("SELECT * FROM users WHERE id = ?");
$userinfo->bind_param("i", $userid);
$userinfo->execute();
$row = $userinfo->fetch_assoc();
$userinfo->close();

Further on in code (As for why I need this):

<input class="form-control" name="charname" value="<?php echo $row["charname"]; ?>" required/>

EDIT 1:

(I haven't tried localhost yet. But when I use the get_result() alternative it still doesnt work)

$userinfo = $db->prepare("SELECT * FROM users WHERE id = ?");
$userinfo->bind_param("i", $_SESSION['usr_id']);
$userinfo->execute();
$result = $userinfo->get_result();
$userinfo->close();

$row = $result->fetch_assoc();

When I change it back to this, it works.

$userid = mysqli_real_escape_string($con, $_SESSION['usr_id']);
$user = mysqli_query($con, "SELECT * FROM users WHERE id = '" . $userid . "'");
$row = mysqli_fetch_array($user);

EDIT 2:

Removed get_result(); in EDIT 1

$db is used to connect.

$db = new mysqli($servername, $username, $password, $dbname);

if (mysqli_connect_errno()) {
    printf("Error: %s\n", mysqli_connect_error());
    exit();
} 

-

var_dump($userinfo->execute());

Returns:

bool(true)

-

var_dump($result);

Returns:

object(mysqli_result)#4 (5) { ["current_field"]=> int(0) ["field_count"]=> int(11) ["lengths"]=> NULL ["num_rows"]=> int(1) ["type"]=> int(0) }

Answer 1

Look at this statement below,

$row = $userinfo->fetch_assoc();

$userinfo is a statement object, not a mysqli_result object. So you can't use it in your code like that. Use ->get_result() method to get the result set from the prepared statement and then fetch the row from the result set, like this:

$userinfo = $db->prepare("SELECT * FROM users WHERE id = ?");
$userinfo->bind_param("i", $userid);
$userinfo->execute();
$result = $userinfo->get_result();
$userinfo->close();

$row = $result->fetch_assoc();

Later, you can use this $row variable in your input element,

<input class="form-control" name="charname" value="<?php echo $row["charname"]; ?>" required/>

---

Sidenote(s):

• If you're using prepared statement, then you don't have to escape anything using mysqli_real_escape_string() function. You can directly use $_SESSION['usr_id'] in your ->bind_param() method, like this:

  $userinfo->bind_param("i", $_SESSION['usr_id']);
  

• ->get_result() method is available only with MySQL Native driver(mysqlnd), so it won't work if you don't have that particular driver installed.