MySQL Basics: Writing Custom SELECT Statements in phpMyAdmin

Here’s probably the oddest analogy I’ll write this month: for many WordPress developers, MySQL is a bit like our spinal column.

How, you ask? Well…

1. It’s a very crucial piece of infrastructure—very little would work properly without it.
2. Despite its importance, it may not get mentioned all that often. This is partly because we benefit from some very good work to put it behind other layers, so that we don’t have to think about it regularly.
3. Unless you really know what you’re doing, you probably are, and probably should be, pretty scared of working with it.

Every good doctor understands vertebrae, and this week we’re exploring some helpful, practical, safe uses of MySQL, beginning with running SELECT statements in the phpMyAdmin web client.

Quick Introduction to MySQL and phpMyAdmin

MySQL is the technical system that governs the WordPress database.

By default, every WordPress site stores the vast majority of its data—post content, user information, permalinks, and more—in a MySQL database. This database is stored and accessed quite differently from the main files in the WordPress filesystem (like PHP, JS, and CSS files)—it can even be stored on a different computer.

So for a WordPress developer, the quickest definition of MySQL is “the technical system that governs the WordPress database”: in other words, how that database is created, structured, stored, accessed, and changed. This means two pieces:

1. The details of the database’s technical structure: how rows, columns, and tables come to be stored as bits on a web server. This structure is called MySQL.
2. A programming language, called SQL, for accessing and changing database contents.

Learning how to “write MySQL,” as we’re doing today, means learning the SQL programming language and applying it in a MySQL environment: in other words, learning to write chunks of SQL code (called SQL statements or SQL queries) to access or change the contents of your WordPress site’s MySQL database.

Sounds Important. Why Haven’t I Learned This Already?

SQL is a pretty scary-looking language. Let’s take a peek:

SELECT t2.Artist, t1.title, t3.genre FROM cds AS t1
    -> LEFT JOIN genres AS t3 ON t1.genreID = t3.genreID 
    -> LEFT JOIN artists AS t2 ON t1.artistID = t2.artistID 
    -> WHERE (t3.genre = 'Pop');

Yikes! By the way, running faulty SQL queries is a great way to wipe or corrupt your database, rendering your site useless.

People have put a lot of effort into letting you do things in SQL without knowing MySQL itself.

Because it’s so stark, people have put a lot of effort into letting you do things in SQL without knowing SQL itself, and this means that you can go a long way in WordPress without learning it directly. Two SQL-hiders stand out in particular:

1. WordPress’s function library, which is stocked with functions—like the methods of the WP_Query class—that run SQL commands for you without you having to understand it, and
2. phpMyAdmin.

Enter phpMyAdmin

phpMyAdmin is software that exists on your web server. If you’ve got cPanel hosting, you access it from there:

phpMyAdmin’s function is simple: to create a visual browser and editor for your databases. It looks like this:

In the image above, we’re viewing the posts table of a WordPress site’s database. You’ve likely browsed through phpMyAdmin to, for example, manually reset a user’s password.

So I Don’t Have to Learn SQL?

Everything phpMyAdmin does is a wrapper for one or another SQL command.

Not so fast! Here’s the critical thing to understand about phpMyAdmin: everything it does is a wrapper for one or another SQL command. Viewing your posts table is a simple and common SQL command—so simple and common that phpMyAdmin’s authors let you just click on that table in the sidebar menu to do it.

But there are lots of not-so-simple commands that you may need to run, and which phpMyAdmin doesn’t have a default for. This is where learning SQL directly—and running it in phpMyAdmin’s MySQL code editor—comes in.

Sample Project: “WordPress is Fun”

For today’s example, we’ll pretend we’re creating a new site, wordpressisfun.org. That site will hold some of WPShout’s existing content, but only those articles that are about WordPress being fun—in other words, posts that contain both the word WordPress and the word fun.

How do we find which articles, of the many on our site, pass the test?

SQL’s SELECT Statement

SQL’s SELECT statement retrieves information from the database.

SQL has many statement types (or query types). One of the most useful, and safest, is SELECT, which simply retrieves information from the database.

SELECT * FROM wps_posts;

will retrieve every column (* means “everything”/”all columns” here) from all rows in wps_posts. This is the command phpMyAdmin runs for you when you click on wps_posts in the sidebar menu.

Let’s start with that in phpMyAdmin’s MySQL code editor, accessible via “SQL” along the top menu:

Running this (with “Go” in the bottom right) gets us our whole table back, as we expect:

Creating Conditions with the SQL WHERE Clause

For this example, we don’t want our whole wps_posts table. We want only specific posts—that is, specific rows—in that table, and that’s where SQL’s WHERE comes in.

As an example, let’s say we only want posts whose content is not null. We’d write:

SELECT * FROM wps_posts WHERE post_content IS NOT NULL;

And let’s say we also only want posts that are already published—no drafts, revisions, etc. Published posts always have the string publish as their value in the post_status column, so now we write:

SELECT * FROM wps_posts WHERE post_content IS NOT NULL AND post_status = 'publish';

As you see, WHERE takes multiple requirements, separated by AND, and it only gives back rows that meet all the requirements.

Searching Through Strings with the SQL LIKE Operator

Our actual job is a bit trickier than finding post_status = 'publish';, because we don’t want an exact match between two strings. Instead, we want to see if one string—the string in the post_content column—contains two other strings, WordPress and fun.

For that, we’ll need a tricky tool: SQL’s LIKE operator plus its % wildcard character. To search for WordPress looks like this:

SELECT * FROM wps_posts WHERE post_content LIKE '%WordPress%';

Let’s break this SELECT statement down:

1. SELECT * FROM wps_posts means “we’re in the wps_posts table and we’re going to get every column of each row we fetch.”
2. WHERE post_content LIKE means “we’re only going to fetch rows where the contents of the post_content column matches a pattern we set.”
3. '%WordPress%' is the pattern.

And let’s break down the pattern itself:

1. The two ' characters start and end the string we’re looking for.
2. The two % characters are wildcard characters. They mean “Anything, of any length (including zero length), goes here.”
3. WordPress is the literal characters W, o, r, d, P, r, e, s, s in that order.

Together, this pattern means: “Anything, followed by the string WordPress, followed by anything.” Together with the rest of the SELECT statement, this will give us back every record whose post_content contains the string WordPress, no matter what else it contains:

Chaining the WHERE Clause with AND

We know we want both WordPress and fun in our statement. Fortunately, phpMyAdmin’s AND clause lets us ask for that:

SELECT * FROM wps_posts WHERE post_content LIKE '%WordPress%' AND post_content LIKE '%fun%';

With the AND operator, we’ve set up two conditions: post_content LIKE '%WordPress%' post_content LIKE '%fun%'. Every row we get back must meet both of these conditions.

And now, to filter out post drafts, revisions, and the like, we’ll ask for only published posts:

SELECT * FROM wps_posts WHERE post_content LIKE '%WordPress%' AND post_content LIKE '%fun%' AND post_status = 'publish';

Nitty-Gritty String Comparison, and Using SQL’s OR Clause

The statement above gets us close to what we want, but if I was actually trying to find posts containing “WordPress” and “fun,” I’d feel like I had more work to do. So stop reading if you’re already happy with what you’ve learned, but if you want more let’s dive in!

First let’s notice that posts containing the words “WordPress” and “refund” are going to show up in our results, even if the word “fun” isn’t actually in there. Remember, we’re asking for “(anything)fun(anything),” and refund matches our request.

So let’s put a space before “fun”:

SELECT * FROM wps_posts WHERE post_content LIKE '%WordPress%' AND post_content LIKE '% fun%' AND post_status = 'publish';

That’s better, but we’d still get posts containing “WordPress” and “PHP function”—but not “fun”—since “PHP function” matches “(anything) fun(anything).”

Here’s where things get complicated: we can’t just write '% fun %'. Why? Because this would match “WordPress is fun for kids,” but not “WordPress is fun!” The ! isn’t the empty space we’re looking for.

The good-enough solution I came up with looks like this:

SELECT * FROM wps_posts
WHERE post_content LIKE '%WordPress%'
AND (
	post_content LIKE '% fun %' OR
	post_content LIKE '% fun, %' OR
	post_content LIKE '% fun? %' OR
	post_content LIKE '% fun! %' OR
	post_content LIKE '% fun. %' OR
	post_content LIKE '% fun" %'
)     
AND post_status = 'publish';

Notice the parentheses, and the repeated OR clause. The logic here works as it would in other programming languages: of the several things inside the parentheses and separated by OR, only one needs to be true for the query to match.

The query above captures the word “fun” followed either by a space, or by the most common types of punctuation that you see in sentences. Running it narrows our results to five, each of which really does contain the words “WordPress” and “fun”:

If this was my project, these are the posts I’d migrate over.

Coming Soon: REGEXP

Does the statement above look hideously inefficient to you? Good, you’ve been paying attention! A better way to do string matching is SQL’s REGEXP function, which is its own small world of complexity. Covering it is on our plate, so stay tuned.

Now You Know Some SQL!

I hope this post has given you a sense of the basic use and rhythms of SQL, and how to start to make basic custom SELECT statements against a WordPress MySQL database inside phpMyAdmin. If you have any thoughts or questions, please query us in the comments below!