How to return failed MySQL query in PHP

Question

I have two queries below. Only one is queried depending on the $methodtype variable.

if ($methodtype == 'group'){

    $stmt = $connection->prepare("SELECT group_id FROM usergroup_list WHERE usergroup = ?");
    $stmt->bind_param('s', $usergroup);
    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        foreach ($row as $value) {
            $newgroupid = $value;
        }
    }
    $result->free_result();

    $stmt = $connection->prepare("UPDATE `usergroup_privs` SET `status` = ? WHERE group_id = ? and function = ?");
    $stmt->bind_param('iss', $privbvalue, $newgroupid, $privname);

} elseif ($methodtype == 'user') {

    $stmt = $connection->prepare("SELECT user_id FROM user_list WHERE username = ?");
    $stmt->bind_param('s', $usergroup);
    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        foreach ($row as $value) {
            $newgroupid = $value;
        }
    }
    $result->free_result(); 

    $stmt = $connection->prepare("UPDATE `user_list` SET `44` = ? WHERE user_id = ?");
    $stmt->bind_param('ii', $privbvalue, $newgroupid);

}

I am then running a second block of code, to either output the changed values, or a message instructing the query has failed (suggesting an error inside the SQL).

if ($stmt->execute()) {
    echo json_encode([true, 'Changed ' . $privname . ' to ' . 
strtoupper($privvalue)]);
} else {
    echo json_encode([false, 'Delete Unsuccessful - SQL Error']);
}

$stmt->close();

However the second part doesn't seem to work. If there is a mistake within the SQL the page just generates a 500 error. Possibly I have followed a couple of guides wrongly, but from what i have seen this should be working?

Any help to clarify this would be much appreciated!

check your logs for php errors, with 500 theres usually a phperror causing the 500 — sietse85, Oct 04 '18 at 10:08

score 0 · Accepted Answer · answered Oct 04 '18 at 11:44

If you're using PDO (Example above looks like one), try setting the PDO mode to Exception by using this code when creating the connection $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); and then use a try & catch block to catch the exception.

Refrence : How to view query error in PDO PHP

How to return failed MySQL query in PHP

1 Answers1