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_STRICT
and 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.
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.
– Marcelo Gomes
I read that it loses in performance to Mysqli and as I will only use SQL, you understand...
– Thiago
@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".
– Maniero
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.
– Thyago ThySofT
I understand the portability thing, but I don’t see the upside, 'cause I’m not changing the bank guy...
– Thiago
@bigown I also thought so until a customer asked the application to use postgres...... the rule is always uncoupling..... or I’m wrong?
– Marcelo Gomes
@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.
– Maniero
@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?
– Maniero