Mysqli's features you probably would like to know about

1. Comments (1)

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 the mysqli_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 and MYSQLI_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 using utf8mb4 as a parameter for this function instead of a widespread but limited utf8 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.