Use of mysql_connect in WordPress

Take a look at this code, found inside a PHP example:

$connection = mysql_connect("localhost","1101readonly","spring1101read");
// select a database

mysql_select_db("wordpress", $connection);

$result = mysql_query("SHOW TABLES;",$connection);
//$result = mysql_query("SELECT * FROM wp_users;", $connection);
//$result = mysql_query("SELECT wp_users.display_name FROM wp_users;", $connection);

while($row = mysql_fetch_array($result,MYSQL_ASSOC))
  {
    foreach($row as $attribute)
      {
        print "$attribute\n";
      }
  }

// generally don't need it but may need for some error conditions
mysql_close($connection);

This looks familiar, it’s a basic MySQL query example, found on a University of Minnesota Morris server. But what’s wrong with it?

The General WordPress rule for mysql_connect:

When making an SQL query in a WordPress environment, use the WPDB class, do not use  mysql_connect

There is never a valid reason to use mysql_connect in WordPress, and it should never be used in general PHP.

But What Could Go Wrong?

Sure it’s not the best but better to be safe than sorry? Lets carry on and when we need to change, lets change

– someone who later went out of business

WordPress provides a class that handles the different database environments WordPress runs on, and you should use this.

By using mysql_connect and other functions from this extension, you miss out on the hard work and effort of many core developers who worked on the WPDB class. Things you might miss out on or run into include:

  • Database caching plugins won’t cache your queries
  • Security checks ran by WPDB and security plugins aren’t performed
  • Plugins and filters that intercept queries aren’t called

These can cause compatibility issues with plugins and performance losses.

But that’s not all. Ignoring the WordPress ecosystem entirely, the mysql extension has been deprecated. Newer versions of PHP will either throw strict warnings, or not include the extension at all. It’s recommended instead that mysqli or PDO be used.

So How Do I Make A Database Query?

You should use the global $wpdb object to make your query, but if you’re using a query to find posts or users, you have 2 additional options:

  • WP_Query
  • WP_User_Query

Other functions exist for querying posts, categories/terms, users, etc.

Using WPDB

Here is a general SQL query:

global $wpdb;
$existing = $wpdb->get_results(
	"SELECT post_id FROM ".$wpdb->postmeta." WHERE meta_key = 'mymetakey' and meta_value = '".$value."'",ARRAY_A
); // note: always escape values, @see wpdb::prepare
if ( !empty( $existing ) ) {
	$posts = array();
	foreach( $existing as $index => $value ) {
		$posts[] = reset( $value );
	}
}

This query searches for posts that have a meta key and value, and returns a list of post IDs. There are other methods as well as get_results that can be used to retrieve individual rows, columns, or values.

wpdb also provides the table names of the standard WordPress tables. This way you can avoid issues such as hardcoding wp_posts when someone has used a different table prefix, or when code is running on a multisite and the blog ID is included in the table names.

WordPress has a primary wpdb object at $wpdb, but you can create your own if you need to connect to a different user/database/server, e.g.

$remote = new wpdb( $dbuser, $dbpassword, $dbname, $dbhost );
$existing = $remote->get_results( "SHOW TABLES" , ARRAY_A );

For more information on using the wpdb class, see here.

Using WP_Query

WP_Query can find posts and is what powers the main post loop. e.g.:

View the code on Gist.

Some people will suggest you use query_posts instead, but this is a mistake. WordPress will attempt to speed up post queries by caching data, so running the same WP_Query loop twice will not send unnecessary queries to the database.

But I Can Not Use WPDB

Although I’ve shown this is not the case, you may be insistent, or have an unusual use case. In this case, mysql_connect is still not what you should be using. The PHP community developed far better solutions long ago. Examples include:

  • PDO
  • Doctrine 2
  • Propel
  • Medoo

And many others. Libraries such as Doctrine 2 will auto-optimise your queries, provide security protection, and provide an OO interface that can be easier to use than arbitrary SQL queries on a level far better than WPDB provides.

For example, here is a query using Medoo:

// Include Medoo
require_once 'medoo.php';
 
// Initialize
$database = new medoo('my_database');
 
// Enjoy
$database->insert('account', [
	'user_name' => 'foo'
	'email' => 'foo@bar.com',
	'age' => 25,
	'lang' => ['en', 'fr', 'jp', 'cn']
]);

PDO as a standard PHP extension also abstracts the type of SQL server, allowing you to use the same code to interface with MySQL, MSSQL, PostgreSQL, and many others.

Further Reading

2 thoughts on “Use of mysql_connect in WordPress

  1. Pingback: 워드프레스에서 mysql_connect 사용 | Word Cracker

Leave a Reply

Your email address will not be published. Required fields are marked *