How can I add error handling to my SQL UPDATE function in PHP?

Question

Problem overview

I have a function that updates a user's username in my SQL database. I am trying to add some error handling (like a return true/false) but I don't know how to go about doing that.

The current function

function updateUid($conn, $usersUid, $rowId) {
    $sql = "UPDATE users SET usersUid = ? WHERE usersId = ?";
    $stmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt, $sql)) {
        //error - bad SQL call
        exit();
    }
    mysqli_stmt_bind_param($stmt, "ss", $usersUid, $rowId);
    mysqli_stmt_execute($stmt);
    return true;
    mysqli_stmt_close($stmt);
}

As you can see, right now it's just returning true without regard for weather or not the function worked.

What I've tried

I tried using mysqli_stmt_get_result($stmt) but I don't think I'm using it correctly:

function updateUid($conn, $usersUid, $rowId) {
    $sql = "UPDATE users SET usersUid = ? WHERE usersId = ?";
    $stmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt, $sql)) {
        //error - bad SQL call
        exit();
    }
    mysqli_stmt_bind_param($stmt, "ss", $usersUid, $rowId);
    mysqli_stmt_execute($stmt);
    $resultData = mysqli_stmt_get_result($stmt);
    if ($row = mysqli_fetch_assoc($resultData)) {
        return $row;
    } else {
        $result = false;
        return $result;
    }
    mysqli_stmt_close($stmt);
}

As far as I can tell, mysqli_stmt_get_result() is returning nothing, which makes sense since I'm not fetching data, but rather placing it.

Desired outcome

As previously stated, I would really like to be able to get a true/false return as to whether the function worked or not. What is the best method for testing the result of a SQL injection in PHP?

`mysqli_stmt_close($stmt);` will never be executed if it's after `return`. — Barmar, Mar 29 '21 at 15:23
Why would your function return true or false? That sounds like a terrible design. If the function worked then nothing should be returned. If it failed then also nothing should be returned and the code should trigger an exception, which mysqli does automatically — Dharman, Mar 29 '21 at 15:29
Please remove all the `if` statements you have there and enable automatic error reporting instead [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Mar 29 '21 at 15:29
`mysqli_stmt_close($stmt);` is redundant. Your statement is created inside a local scope and will never leak outside the function. You don't need to close it — Dharman, Mar 29 '21 at 15:32
If you are only starting to learn PHP then you should learn PDO instead of mysqli. PDO is much easier and more suitable for beginners. Start here https://phpdelusions.net/pdo & https://websitebeaver.com/php-pdo-prepared-statements-to-prevent-sql-injection — Dharman, Mar 29 '21 at 15:32
*"What is the best method for testing the result of a SQL injection in PHP?"* This sentence makes no sense. Please explain more clearly what exactly you want to do — Dharman, Mar 29 '21 at 15:33
What you're going to do with this true/false value from this function? — Your Common Sense, Mar 29 '21 at 15:46

score 1 · Accepted Answer · answered Mar 29 '21 at 15:34

mysqli_stmt_affected_rows() will tell you how many rows were updated.

<?php
function updateUid($conn, $usersUid, $rowId) {
    $sql = "UPDATE users SET usersUid = ? WHERE usersId = ?";
    $stmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt, $sql)) {
        //error - bad SQL call
        exit();
    }
    mysqli_stmt_bind_param($stmt, "ss", $usersUid, $rowId);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_affected_rows($stmt) > 0;
    mysqli_stmt_close($stmt);
    return $result;
}

Note that this can be 0 for two reasons:

No rows match $rowId
The usersUid in the matching row is already equal to $usersUid, so nothing changed.

See PHP, MySQL - can you distinguish between rows matched and rows affected? for how you can change the way you connect to the DB so that this returns the number of rows that were matched by the WHERE condition instead of the rows that were actually updated.

How can I add error handling to my SQL UPDATE function in PHP?

Problem overview

The current function

What I've tried

Desired outcome

1 Answers1