WordPress.org

Ready to get started?Download WordPress

Make WordPress Core

Tagged: mysql Toggle Comment Threads | Keyboard Shortcuts

  • Gary Pendergast 6:20 am on April 7, 2014 Permalink | Log in to leave a Comment
    Tags: , , , mysql,   

    MySQL in WordPress 3.9 

    In WordPress 3.9, we added an extra layer to WPDB, causing it to switch to using the mysqli PHP library, when using PHP 5.5 or higher.

    For plugin developers, this means that you absolutely shouldn’t be using PHP’s mysql_*() functions any more – you can use the equivalent WPDB functions instead.

    mysql_query()

    There are a few different options for replacing the query functions, depending on what you want to do:

    As a drop in replacement to run a query that you don’t expect a return value from (i.e., an INSERT, UPDATE or DELETE query), use $wpdb->query(). This will always return the number of rows effected by the query.

    Alternatively, $wpdb->insert(), $wpdb->update(), $wpdb->delete() and $wpdb->replace() are all helper functions that will automatically escape your data, then generate and run the queries for you. Ideally, you should never need to write an SQL statement!

    mysql_fetch_*()

    If you have a SELECT query, for which you’d normally do a mysql_query() followed by a mysql_fetch_*(), WPDB lets you combine this into one function call.

    To get all of the results from a query that returns more than one row, use $wpdb->get_results() to return an array of objects containing your data.

    There are also some shortcut functions for common usage:

    If you only need a single row from your query, $wpdb->get_row() will return just the data object from that row.

    If you only need a single column from a single row, $wpdb->get_var() will return only that field.

    And if you need a single column, $wpdb->get_col() will return an array of all the data from that column.

    mysql_real_escape_string()

    For a drop in replacement, you can use esc_sql(). That said, we strongly recommend switching to $wpdb->prepare(), instead. We have a pretty thorough tutorial available for $wpdb->prepare().

    mysql_insert_id()

    If you need to get the Insert ID from the last query, $wpdb->insert_id is where you need to look.

    Updating your plugin to use WPDB will also future proof it for if we make changes to how WordPress connects to the database – we’ll always maintain backwards compatibility with the current WPDB interface.

    For more reading, check the WPDB Codex page, and #21663.

    If you’re using MySQL in a way that I haven’t covered here, please post it in the comments, we’d be happy to help you out!

     
    • Samuel Wood (Otto) 6:23 am on April 7, 2014 Permalink | Log in to Reply

      Note: $wpdb->escape() is deprecated. Please use esc_sql() instead. Or $wpdb->prepare(), of course.

      Also note that $wpdb->escape() is not a proper replacement for mysql_real_escape_string() to begin with, as it only does weak escaping.

    • Doug Wollison 12:09 pm on April 7, 2014 Permalink | Log in to Reply

      I though I heard it was going to switch to PDO, not MySQLi?

      • Gary Pendergast 12:30 pm on April 7, 2014 Permalink | Log in to Reply

        That’s still on the cards for a future release, but it will be a significantly bigger project. The primary goal with this change was to stop using ext/mysql on PHP 5.5+, where it’s deprecated.

    • Brian Layman 4:10 pm on April 7, 2014 Permalink | Log in to Reply

      Wow! Huge change, and with the MySQL extension being deprecated in PHP 5.5 it’s a good one. I am trying to remember if 5.5 REQUIRES MySQLi to be built in. Does anyone know? Should you also throw in a check for the existence of mysqli_connect?

      Also, are there plans to start preparing queries? (Not WP prepare but the database meaning of prepare.)

      • Gary Pendergast 3:06 am on April 10, 2014 Permalink | Log in to Reply

        We do check that mysqli_connect() exists before trying to use it. (See wpdb::__construct().)

        There are no plans for adding statement prepare in the near future, though it is something I would like to get to!

    • webaware 10:46 pm on April 7, 2014 Permalink | Log in to Reply

      Plugin writers sometimes call mysql_get_server_info() to get the raw version information that $wpdb->db_version() strips out. Since the database handle isn’t public (nor is the mysqli indicator), there isn’t a way to do this in 3.9-beta3. I’ve opened a trac in hopes we can get a new method added to class wpdb so that plugin writers can still access this raw version information:

      https://core.trac.wordpress.org/ticket/27703

      • Gary Pendergast 5:28 am on April 8, 2014 Permalink | Log in to Reply

        To summarise the ticket, for anyone using mysql_get_server_info() – the best option is to have a switch in your code for mysqli, we’ll look at expanding $wpdb->db_version() at a later date.

        if ( empty( $wpdb->use_mysqli ) ) {
        	$ver = mysql_get_server_info();
        } else {
        	$ver = mysqli_get_server_info( $wpdb->dbh );
        }
        
    • Claudio 8:51 pm on April 13, 2014 Permalink | Log in to Reply

      I use mysql_connect to test DB connectivity. How should I replace it for WP3.9/PHP5.5 compatibility?
      Thanks!

      • Gary Pendergast 12:13 am on April 14, 2014 Permalink | Log in to Reply

        To test the connection, you can use $wpdb->check_connection(), which will check that the connection is up, and try to reconnect if it isn’t.

        This is particularly useful for long running cron jobs, where the MySQL connection might drop out due to inactivity, but there’s no actual problem with the server.

    • Ross Seddon 6:08 am on May 5, 2014 Permalink | Log in to Reply

      My web site which was designed using a standard off shelf theme I’m advised by our web site managers they site is providing the response “Access denied for user ‘www-data’@’localhost’ (using password: NO).” They advise
      Quote: “the one statement in there is exactly the issue: For plugin developers, this means that you absolutely shouldn’t be using PHP’s mysql_*() functions any more – you can use the equivalent WPDB functions instead.”

      The developer how wrote the plugin that is used site wide on your site for the skin, uses this no longer available method. All there work needs to be updated to use the correct method of connecting to the database. We know what is required, just that the plugin / skin is none of our work, and it is time consuming to fix.”

      We been down now for 3 weeks in terms of accessing site. Is what your referring to this thread relative to my site problem site http://www.totallyoutdoors.com.au

      how time consuming is this problem?

      Thanks

    • lwall 1:07 pm on May 15, 2014 Permalink | Log in to Reply

      I am having troubles with admin permissions at different points.

      When clicking on “Posts”, I get error “Invalid post type”, or when trying to create a new post, there is no save box/button.

      It also happens when trying to change options in one of my themes in other places with plugins. I get “You do not have sufficient permissions to access this page.”

      For the most part, the back-end is functional, the front-end is fully functional.

      I am using appengine 1.9.3 and wordpress 3.9, python 2.7.6.

      I have uninstalled 1.9.3 and updated to 1.9.4, I have also accepted WordPress’ request to install 3.9.1, the problem persists.

      I have installed the 1.9.3, 3.9, 2.7.6 configuration on a different machine where appengine was never installed before and the same problem occurs.

      I am discarding plugins because there were no plugins in the separate machine.

      I had appengine 1.9.0 and 1.9.3 working with WordPress 3.8.1. The problem started a few days ago after a number of upgrades (from 1.9.3 to 1.9.4, and into wordpress 3.9.1).

      Could this extra layer to WPDB be the source of this?

    • Michael Simpson 1:49 pm on July 11, 2014 Permalink | Log in to Reply

      For a plugin, I would like to be able to do unbuffered queries (MYSQLI_USE_RESULT). This is to handle cases when there are a lot of rows being returned and I want to keep the memory footprint down. wpdb doesn’t support this well and it would be nice if it would.

      For now, I have to create a new wpdb object and directly call mysqli_query($wpdb->dbh, $sql, MYSQLI_USE_RESULT); It is not using MySQLi, then I need to call mysql_unbuffered_query($sql, $wpdb->dbh);

      To know which one to call, I would like to consult $wpdb->use_mysqli but I cannot because it is private and there is no accessor method. So I have to copy the code from wp-db.php to determine it.

      For a start, it would be nice to be able to access $wpdb->use_mysqli. Even better would be an API on $wpdb to do unbuffered queries.

      Thanks.

      • Gary Pendergast 2:02 pm on July 11, 2014 Permalink | Log in to Reply

        You can access $wpdb->use_mysqli directly, because of the wpdb::__get() magic getter.

        There’s unlikely to ever be an API in WPDB for doing unbuffered queries. There are too many gotchas that will just cause maintenance problems.

  • Andrew Nacin 6:09 am on July 14, 2010 Permalink
    Tags: mysql   

    Here’s some stats on MySQL usage, which is feeling left out with all the PHP talk.

    • 94.3% of sites are at least MySQL 5.0.
    • The first 5.0.x version with real usage is 5.0.22, with 93.6% of installs at this or above.
    • 90.8% of sites are at least MySQL 5.0.44.

    If we choose to bump to 5.0.x, we I imagine it would be best to identify a version between 5.0.22 and 5.0.44 based on stability, features, and usage.

    • Joomla is jumping from 3.23 to 5.0.4 — 94.3% of WP installs are this or above.
    • Drupal is jumping from 4.1 to 5.0.15 — 94.2% of WP installs are this or above.

    Oddly, neither of those show any real usage — 5.0.4 is in the double digits, and 5.0.15 is in triple digits.

     
c
compose new post
j
next post/next comment
k
previous post/previous comment
r
reply
e
edit
o
show/hide comments
t
go to top
l
go to login
h
show/hide help
shift + esc
cancel