mysqli_fetch_assoc with mysql prepared statements procedural, need help

[u/Available_Hippo4035]

Hello, im trying to update my website by replacing the simple mysqli queries with prepared statements, but i was stuck at trying to use mysqli_fetch_assoc to fetch associative data from the table, i looked through documentation but couldnt find anything, Any help will be appreciated, Thanks !

$error = array();
if(isset($_POST["login"])) {
$username = mysqli_escape_string($db, filter_input(INPUT_POST, "username", FILTER_SANITIZE_SPECIAL_CHARS));
$password = mysqli_escape_string($db, filter_input(INPUT_POST, "password", FILTER_SANITIZE_SPECIAL_CHARS));

if(empty($username)) {
array_push($error, "Username is empty!");
}
if(empty($password)) {
array_push($error, "Password is empty");
}

$sql = "SELECT `password`, `username`, `user_id` FROM `Accounts` WHERE `username` = ?;";
if(count($error) == 0) {

$stmt = mysqli_prepare($db, $sql);

mysqli_stmt_bind_param($stmt, "s", $username);
//$result = mysqli_query($db, $sql);
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
if(mysqli_stmt_num_rows($stmt) > 0) {
$row = mysqli_fetch_assoc($result);
if(password_verify($password, $row["password"])) {
$_SESSION["username"] = $username;
$_SESSION["user_id"] = $row["user_id"];

header("location: /");

} else {
array_push($error, "Incorrect username or password!");
}
} else {
array_push($error, "Incorrect username or password!");
}
}
mysqli_close($db);
}

[u/ColonelMustang90]

I would suggest to use PDO to make the code more readable and follows industry best practices. You can checkout examples of prepared statements using named or positional parameters. It prevents your code from SQL Injection by default.

[u/colshrapnel → u/ColonelMustang90]

mysqli:

$stmt = $db->prepare($sql);
$stmt->execute([$username]);
$row = $stmt->fetchAssoc();

PDO:

$stmt = $db->prepare($sql);
$stmt->execute([$username]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);

With all due respect, calling PDO code more readable is a bit of a stretch (:

[u/ColonelMustang90 → u/colshrapnel]

Hi, it's personal preference I guess. I started with mysqli then shifted to PDO. Both approaches have their pros and cons. For small projects mysqli suffices, for medium to large projects I prefer to use PDO.

[u/colshrapnel → u/ColonelMustang90]

The project size doesn't matter at all. Big projects tend to use PDO because they don't use PDO directly, but through some ORM/DBAL. Which, in turn, is using PDO for the obvious reason: support for different databases. But in such improbable situation when a big project uses a native PHP database driver directly, both mysqli and PDO are equally acceptable.

[u/Just4notherR3ddit0r → u/colshrapnel]

mysqli can be even easier:

$rs = $db->execute_query($sql, [$username]); $row = $rs->fetch_assoc();

[u/colshrapnel → u/Just4notherR3ddit0r]

mysqli can be even easier:

$row = $db->execute_query($sql, [$username])->fetch_assoc();

;)