I'm getting a connection error with mysqli_stmt_init but no errors are displaying

Question

I am stumped. I just need someone to look over my code and give me ideas as to why the connection isn't working. It works for all other update functions I have (similar code/copy pasted), just not this one.

I must also add that it runs and updates the database. My issue is with getting the results at the end. I traced back the error to the connection and it just doesn't make any sense anymore.

/**
 * Update Avatar Function
 */
function updateAvatar($conn, $username, $avatar) {
    // setup a query
    $sql = "UPDATE users SET avatar = ? WHERE username = ?;";

    // prepare a statement to send a sanitized query
    $stmt = mysqli_stmt_init($conn);
    
    // check for a fail in case the query is faulty
    if (!mysqli_stmt_prepare($stmt, $sql)) {
        header("location: ../admin/account.php?error=stmtfailed");
        exit();
    }

    // bind our data to the statement and execute
    mysqli_stmt_bind_param($stmt, "ss", $avatar, $username);
    mysqli_stmt_execute($stmt);

    // save the avatar
    $avatar = json_decode($avatar, true);
    move_uploaded_file($avatar["tmp_name"][0], '../assets/images/uploads/' . $avatar["name"][0]);

    // grab the returned data
    $resultData = mysqli_stmt_get_result($stmt);

    // try to grab the row and return it
    if (!$row = mysqli_fetch_assoc($resultData)) {
        return $row;
    }

    // close the statement
    mysqli_stmt_close($stmt);

    // send the user to the account page with a success message
    header("location: ../admin/account.php?success=update");
}

I think you should remove the `!` from this statement, unless I'm misreading it: `if (!$row = mysqli_fetch_assoc($resultData))`. If that fails, or returns no results, there's nothing to return then? — Chris Haas, Dec 22 '21 at 19:31
@ChrisHaas That whole `if` statement should be removed as it makes no sense. I assume this is the error OP is talking about — Dharman, Dec 22 '21 at 19:32
Yeah, I now reading the SQL I see there's an `UPDATE`. Maybe they want `mysqli_stmt_affected_rows()`? — Chris Haas, Dec 22 '21 at 19:33
mysqli_stmt_get_result: `For successful queries which produce a result set, such as SELECT, SHOW, DESCRIBE or EXPLAIN, mysqli_stmt_get_result() will return a mysqli_result object. For other successful queries, mysqli_stmt_get_result() will return false.` Therefore `$resultData` will be false, and `mysqli_fetch_assoc($resultData)` would most likely throw an error instead of doing whatever you think you want it to do — aynber, Dec 22 '21 at 19:35

score 1 · Answer 1 · answered Dec 22 '21 at 19:37

It looks like you have been watching that infamous tutorial on Youtube. Your code is way too complicated and you got confused with what you are doing.

First of all, make sure you have enabled mysqli error reporting. Just add mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); before connecting to the database and then you don't need to check for success of each function call.

The main problem in your code is that you are trying to call mysqli_stmt_get_result on an UPDATE query, which never produces a result. Just remove all of that code.

function updateAvatar(mysqli $conn, $username, $avatar): never {
    // setup a query
    $sql = "UPDATE users SET avatar = ? WHERE username = ?;";
    $stmt = mysqli_prepare($conn, $sql);

    // bind our data to the statement and execute
    mysqli_stmt_bind_param($stmt, "ss", $avatar, $username);
    mysqli_stmt_execute($stmt);

    // save the avatar
    $avatar = json_decode($avatar, true);
    move_uploaded_file($avatar["tmp_name"][0], '../assets/images/uploads/' . $avatar["name"][0]);

    // send the user to the account page with a success message
    header("location: ../admin/account.php?success=update");
    // always exit after header location
    exit;
}

I'm getting a connection error with mysqli_stmt_init but no errors are displaying

1 Answers1