Testing the Success of MySQL Queries: Modifying Database Table Data

When performing database operations, it's crucial to verify the success of queries. This article explores how to determine if a MySQL query has successfully modified data in a database table.

Consider the following PHP code snippet used for deleting an article from a database:

if($cmd == "deleterec"){
    $deleteQuery = "DELETE FROM AUCTIONS1 WHERE ARTICLE_NO = ?";
    if($delRecord = $con->prepare($deleteQuery)){
        $delRecord->bind_param("s", $pk);
        $delRecord->execute();
        $delRecord->close();
        echo "true";
    } else {
        echo "false";
    }
}

The initial approach only checked if the SQL statement was prepared correctly, which is insufficient to confirm the deletion of the record. To rectify this, consider the following modification:

...
echo ($delRecord->affected_rows > 0) ? 'true' : 'false';
$delRecord->close();

By checking the number of affected rows (i.e., $delRecord->affected_rows), you can reliably determine if any rows were modified by the query. If the result is greater than 0, the query was successful; otherwise, an error occurred.

It's important to note that this modification does not account for potential issues or error handling during the query execution. Further examination would be required to address any such scenarios.