Using PHP’s mysqli extension with WordPress

At the moment the database object implemented in WordPress (wpdb) relies on the mysql extension (mysql_connect). As PHP 5.5 will not support the mysql extension anymore and it’s highly recommended to use the mysqli extension with MySQL versions 4.1.3 or later, I was looking for a way to use the mysqli extension with WordPress without too much hacking.

Fortunately WordPress offers a way to include own code before the database object (wpdb) get’s initialized. This is found in wp-includes/load.php in function require_wp_db().

The function searches for a file named db.php in wp-content and includes it if it exists. If the $wpdb object exists after including db.php, WordPress will not create the default $wpdb object.

So my idea was to extend the original class wpdb (found in wp-includes/wp-dp.php), overwrite all methods which make use of PHP’s mysql_* functions and replace them with the mysqli_* counterparts (as far as possible). Before the class gets defined, the script checks if the server is ready to use mysqli. So far it checks if PHP version is >= 5 and if the function mysqli_connect exists.

I was not sure if it would work, but the first try worked like a charm with my current WordPress project, which has several active plugins. To check if the class is active, I put those lines in my plugin for that project:

The result is
object(mysqli)#2 (18) { ["affected_rows"]=> int(1) ["client_info"]=> string(78) "mysqlnd 5.0.8-dev - 20102224 [...]
what shows that $wpdb is working with an mysqli object internally.

With the constants DB_PORT and DB_SOCKET you can configure a custom db port and socket to use for the connection.

But to make this clear: This is just a test and not suitable for productive use right now.

There are some ToDo’s. Functions mysql_num_fields and mysql_free_result have no counterparts for mysqli. The constants MYSQL_NEW_LINK and MYSQL_CLIENT_FLAGS are not supported yet. For the first try I simply used mysqli_connect to test it. In future versions we could try to use mysqli_real_connect instead to support the client flags. And there are definitely some more powerful options to boost the MySQL performance with the mysqli extension. I did not have the time to think about yet.
If you want to try it for yourself, get the file db.php and put in your wp-content directory.

Feel free to improve it and let me know your experiences.

Get it at https://github.com/ifeelweb/mysqli-for-WordPress

Tags: , , ,

10 Responses to “Using PHP’s mysqli extension with WordPress”

  1. Holyfield April 9, 2013 at 19:08 #

    Good work!

    I’m amazed that there is no any useful information about MySQLi on WordPress Codex etc.

    I’m going to test this db layer on WordPress 3.5.2 to use MySQLi.

    Please not that mysql_free_result is very important to release resources when using stored procedures!

    • Timo April 9, 2013 at 20:46 #

      Thanks for your response. Before I wrote the wrapper, I was amazed too. I could not find any serious approach for using MySQLi with WordPress.
      I’ll try to find a workaround for the two unresolved items. My test installation runs fine for several weeks now.

  2. Holyfield April 14, 2013 at 02:54 #

    Hi!

    I finally got the moment to test your script! It works fine :)

    Haven’t tested yet with MySQL stored procedures, but I will soon. For sure updated mysql_free_result method is required I guess, as calling stored procedures always require proper recourses cleaning.

    ON PHP 5.5.0 mysql will be deprecated, which means, that WordPress dev team should replace current db layer anyway.

    For Joomla user can choose at setup time the desired db layer a long time ago.

  3. swiffer April 18, 2013 at 12:19 #

    Hey,

    you worte about two missing functions in mysqli but both of them seem to be available through PHP 5

    mysqli_num_fields -> http://php.net/manual/en/mysqli-result.field-count.php
    mysqli_free_result -> http://php.net/manual/en/mysqli-result.free.php

    I added both of them to my script and it works without any problem..

  4. Bill June 28, 2013 at 06:13 #

    Very nice work.

  5. Jan Reilink October 18, 2013 at 15:28 #

    Thank you for the mysqli db class. I’m currently testing/using it on my own site (in production…) and all seems to work just fine. With PHP 5.5 btw :)

Leave a Reply

16 − fourteen =