Mysqli's features you probably would like to know about
In the recent years mysqli gained rather a bad reputation, to which I contributed myself. However, mysqli greatly evolved over time and became much more programmer-friendly nowadays. Needless to say that it is still a robust, well-maintained extension which supports all the options provided by the original Mysql C API.
Being overshadowed by both old mysql ext (which had a very limited functionality) and PDO (which is often chosen as a substitution), mysqli remains in obscurity as people seldom volunteer to explore its facilities. So here is the list of options you would probably like to know about which are either make mysqli on par with PDO or even provide some unique functionality:
-
the cornerstone option which, similar to PDO's
PDO::ERRMODE_EXCEPTION
, tells mysqli to throw exceptions by default, relieving you from the toilsome approach when you need to check every function call and retrieve themysqli_error()
result manually. Just add the following line to your mysqli connection code and all database errors will be reported to you automatically!mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
it accepts two bitmask flags of which
MYSQLI_REPORT_ERROR
enables the error reporting andMYSQLI_REPORT_STRICT
tells mysqli to throw exceptions; -
in those times of strict typing, all database data returned as string looks like atavism. And although prepared statements already return the typed data, the regular
query()
returns all data as string. But here is a certain driver option to the rescue! Just add$db->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);
and have its results typed as well! Check the result online (I am not affiliated with this site, just using its unique feature of providing the database connection for the usual PHP online shell). The good news, you don't need to hassle with mysqli_init/real_connect as it would have been with most of the other options;
-
mysqli_info() is a unique function which returns separate results for the the number of rows found and affected by a DML query. In any other driver, including PDO, you can have either one or another but not both at once. Although it returns a string, a simple code could be written to parse its result into separate variables;
$db->query("update test set i=2");
$pattern = '~Rows matched: (?<matched>\d+) Changed: (?<changed>\d+) Warnings: (?<warnings>\d+)~';
preg_match($pattern, $db->info, $matches);
$info = array_filter($matches, "is_string", ARRAY_FILTER_USE_KEY); -
in the upcoming PHP 8.1 it will be possible to send variables directly into
execute()
, exactly the same way as it can be done in PDO, thanks to the effort of Kamil Tekiela and Nikita Popov;$mysqli->prepare('INSERT INTO users(name) VALUES(?)')->execute(['Dharman']);
-
although not as versatile as PDO's
fetchAll()
but still a very handy mysqli_fetch_all() function that returns an array that consists of all the rows returned by the query, either as enumerated or associative arrays;$users = $db->query("select * from users")->fetch_all(MYSQLI_ASSOC);
-
mysqli_set_charset() is the only proper way to set the connection charset, as opposed to various SQL, such as
SET NAMES
etc. Also make sure you are usingutf8mb4
as a parameter for this function instead of a widespread but limitedutf8
subset;$db->set_charset('utf8mb4');
Now, I would say the only two features are lacking: named placeholders and a prepared-statement-in-one-go function, similar to postgres' pg_query_params(). And although the former is hardly possible, the latter can be easily implemented as a userland helper function, making prepared queries as simple and concise as regular ones.
Related articles:
- Mysqli tutorial (how to use it properly)
- How to connect properly using mysqli
- How to report errors in mysqli
- How to check whether a value exists in a database using mysqli prepared statements
- Why mysqli prepared statemens are so hard to use?
- Mysqli helper function
- Authenticating a user using mysqli and password_verify()
- How to get the number rows that has been actually changed
- Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given.
- Mysqli examples
- How to run INSERT query using Mysqli
- How to use mysqli properly
Add a comment
Please refrain from sending spam or advertising of any sort.
Messages with hyperlinks will be pending for moderator's review.
Markdown is now supported:
>
before and an empty line after for a quote