I know what you want.

You want a code snippet you can paste into your WordPress project so you run custom SQL against your WordPress database.

I’ll give it to you, but stick around if you want to learn more about why I started using PDO in my WordPress plugin alongside of $wpdb.

Here it is. This is the exact connection code I use in Independent Analytics. This has been running in production for thousands of customers on a wide range of both premium and budget hosting.

 1global $wpdb;
 2 
 3// Parse the DB_HOST using WordPress's specific style
 4// Supports IPv4, IPv6, and socket connections
 5$host_data = $wpdb->parse_db_host(DB_HOST);
 6 
 7if (is_array($host_data)) {
 8    list($host, $port, $socket, $is_ipv6) = $host_data;
 9} else {
10    // Redacted. Throw an error or something
11}
12 
13// Wrap the IPv6 host in braces as required
14if ($is_ipv6 && extension_loaded('mysqlnd')) {
15    $host = "[$host]";
16}
17 
18// Generate either a socket connection string or TCP connection string
19if (isset($socket)) {
20    $connection_str = 'mysql:unix_socket=' . $socket . ';dbname=' . DB_NAME . ';charset=' . DB_CHARSET;
21} else {
22    $connection_str = 'mysql:host=' . $host . ';dbname=' . DB_NAME . ';charset=' . DB_CHARSET;
23 
24    if (isset($port)) {
25        $connection_str .= ';port=' . $port;
26    }
27}
28 
29// Open the connection
30$pdo = new PDO($connection_str, DB_USER, DB_PASSWORD);
31 
32// Do something with the connection
33$rows = $pdo->query('SELECT * FROM some_table')->fetchAll();
34 
35// Close the connection
36$pdo = null;

Code Breakdown

While the code above is pretty straightforward, it is quite a bit of code to simply set up a PDO connection to your WordPress database.

Could it be shorter? Sure. You could use this:

1// Works in some cases...
2$data_source = 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME . ';charset=' . DB_CHARSET;
3$pdo = new PDO($data_source, DB_USER, DB_PASSWORD);

The code above works… in some cases. It won’t work for connections using a custom port value, it won’t work for IPv6 connections, and it won’t work for socket connections. Not great.

All that extra code serves a valuable purpose, so let’s break it down starting from the top.

Add WPDB To Scope

1global $wpdb;

First up, you’ll want to use the global keyword to add $wpdb to scope.

We’re not going to use the $wpdb connection for our queries, but we will take advantage of its parse_db_host method to properly parse the DB_HOST constant.

Parse The Host

1$host_data = $wpdb->parse_db_host(DB_HOST);
2 
3if (is_array($host_data)) {
4    list($host, $port, $socket, $is_ipv6) = $host_data;
5} else {
6    // Redacted. Throw an error or something
7}

Now it’s time to to parse DB_HOST and check if it contains a valid host value. That’s where parse_db_host comes into play. We pass in the host string and get back an array of extracted values.

For a socket connection, $socket will store the socket path.

For a TCP/IP connection, $host will store the host and $port will store the port. If it’s a IPv6 connection, $is_ipv6 will be set to true.

Now we can use some conditionals to look at those four variables and setup our connection string correctly.

Handle IPv6 Hosts

1if ($is_ipv6 && extension_loaded('mysqlnd')) {
2    $host = "[$host]";
3}

IPv6 hosts require a bit of special attention if you’re using PHP’s MySQL Native Driver (mysqlnd). You’ll need to wrap the host in a pair of brackets if it’s an IPv6 address. So an IPv6 address such as ::1 would become [::1].

Build The Connection String

1if (isset($socket)) {
2    $connection_str = 'mysql:unix_socket=' . $socket . ';dbname=' . DB_NAME . ';charset=' . DB_CHARSET;
3} else {
4    $connection_str = 'mysql:host=' . $host . ';dbname=' . DB_NAME . ';charset=' . DB_CHARSET;
5 
6    if (isset($port)) {
7        $connection_str .= ';port=' . $port;
8    }
9}

Now it’s time to build the connection string. For socket connections, unix_socket needs to be set to the file path stored in $socket. For TCP/IP connections, host needs to be set to the value of $host and port will optionally be set if a non-default port value was used.

Open The Connection

1$pdo = new PDO($connection_str, DB_USER, DB_PASSWORD);

Now a connection can be established. This is done by creating a new instance of PDO and providing the host, the database user, and the database password.

Run Some Query

1$rows = $pdo->query('SELECT * FROM some_table')->fetchAll();

The connection is open and you can now query your WordPress database using PDO! The PDO public interface is more than I’m going to cover here, but this example show you’d go about fetching all the rows from a particular table.

I’d recommend (The Only Proper) PDO Tutorial if you’re interested in learning more about PDO.

Close The Connection

1$pdo = null;

You can close the database connection by setting the variable that stores the PDO instance to null.

I’d recommend creating a single PDO connection to use throughout the lifecycle of your public. The connection will automatically be closed as the PHP process completes.

What About WPDB?

I don’t have anything against WPDB. It’s fine. My plugin uses both WPDB and PDO, and I still write new code using each.

My issue with WPDB came up as I started to write longer and more complex queries. Maybe I’ll dive into that in a separate post.

That’s it for now. Enjoy.