How to create Exceptions to handle statement with Mysqli

Asked

Viewed 950 times

3

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?

php mysqli exception

  • But why are you migrating? PDO is native and more recommended, because if you need to change DBMS you don’t need to change the code, only the connection class.

  • 1

    I read that it loses in performance to Mysqli and as I will only use SQL, you understand...

  • @Marcelogomes, who said you were the most recommended? In practice, apart from some generic software made for everyone to use, nobody needs to exchange databases. People invent this "requirement".

  • I believe it is worth reviewing, if I am not mistaken the loss of performance between PDO and Mysqli is between 2% - 4%, is only a suggestion if the concern is only performance.

  • 2

    I understand the portability thing, but I don’t see the upside, 'cause I’m not changing the bank guy...

  • @bigown I also thought so until a customer asked the application to use postgres...... the rule is always uncoupling..... or I’m wrong?

  • 1

    @Marcelogomes there is no rule. Just because a client asks, it does not mean that it should be done. I have never seen a case where the database was well chosen and needed to change. Changing it is not a good reason to do it. And essentially every database can do anything other people can. In general the necessary change is far from being just the object of connection, unless you have used the lowest common denominator, then the application has gone very bad to meet an unlikely requirement. I’ve seen who did it the right way, but it’s expensive. It’s not as simple as they say.

  • @NGTHM4R3 You can accept an answer if it solved your problem. You can vote on all the posts on the site as well. Did any help you more? Something needs to be improved?

Show 3 more comments

2 answers

5

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.

4

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.

Browser other questions tagged php mysqli exception

You are not signed in. Login or sign up in order to post.