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 for its support for multiple DBMS), mysqli remains in obscurity as people seldom volunteer to explore its facilities. So here is a 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 boresome approach when you need to check every function call manually 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 tells mysqli to report errors and MYSQLI_REPORT_STRICT makes it an Exception instead of a Warning. 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; And even better news: starting from PHP 8.1 you don't have to bother even adding this setting to your code as it's turned on by default!

• starting from PHP 8.1 it is 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']);

• starting from PHP 8.2, mysqli got a function that allows prepare and execute in one go, thanks Kamil Tekiela and Craig Francis!

  $sql = 'SELECT * FROM users LIMIT ?,?';
  $users = $mysqli->execute_query($sql, [$limit, $offset])->fetch_all(MYSQLI_ASSOC);

  even PDO doesn't have such a handy function yet, asking you to do prepare, execute and fetch in three separate calls!

• 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);

• in our 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 there is a certain driver option to the rescue! Just add the following line to your connection code

  $db->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);

  and have all the results properly typed! 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).

• 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);

• 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 feature that makes mysqli inferior to PDO is lack of support of named placeholders and some useful fetch modes. However, although seeing the former is hardly possible, the latter can be easily implemented as user-defined functions.