How to create Exceptions to handle statement with Mysqli

Question

I’m migrating from PDO to Mysqli and when I was doing some operation with the bank, the verification block was like this:

$query = $db->prepare("ppapapa sql sql");

try {
        $query -> execute();
        return $query -> fetchAll();

    } catch(PDOException $e) {
        die($e -> getMessage());
    }

What would this error handling be like with Mysqli?

Answer 1

You need to enable exceptions with:

mysqli_report(MYSQLI_REPORT_ALL);

Or just:

mysqli_report(MYSQLI_REPORT_STRICT);

Then you can use normally:

$query = $db->prepare("ppapapa sql sql");
try {
    $query -> execute();
    return $query -> fetchAll();
} catch(mysqli_sql_exception $e) {
    die($e -> getMessage());
}

Documentation of mysqli_report().

If you don’t want to do this you can handle the mistakes individually:

if (!mysqli->query($insadquery)) die(mysqli->errno . " - " . $mysqli->error);

I put in the Github for future reference.

Often this form is preferable.

Documentation of all mysqli.

Answer 2

The function that turns errors into exceptions in Mysqli is to use the function mysqli_report().

Since Mysqli is a specialized API, it has some interesting features, such as detecting common errors in the method prepare(). some of them are:

Virtually all are typos or some poorly formatted value.

1146 # Table 'nome' doesn't exist
1054 # Unknown column 'nome' in 'clause'
1064 # You have an error in your SQL syntax;

To catch these errors use the combination of MYSQLI_REPORT_STRICTand MYSQLI_REPORT_ERROR and let the prepare() within the catch. Other errors such as foreign key violation or repeat ids, are checked in the execute().

mysqli_report(MYSQLI_REPORT_STRICT|MYSQLI_REPORT_ERROR);

try {
    $query = $db->prepare("ppapapa sql sql");
    $query -> execute();
    $result = $query->get_result();
    return $result->fetch_all();
}catch(mysqli_sql_exception $e) {
    echo 'SQLState: '. $e->getCode() .' # '. $e->getMessage();
}

When to use MYSQLI_REPORT_ALL?

MYSQLI_REPORT_ALL should used for debugging and identifying bottlenecks, if a query does not use the Index, an Exception will be launched that will divert the flow to the catch even if no real mistake has occurred, which is quite indigestible for a system already in production.

Example of this Exception.

No index used in query/Prepared statement SELECT * FROM ...

MYSQLI_REPORT_ALL or MYSQLI_REPORT_INDEX provide good clues as to why some queries are slow, use when appropriate.