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…
- It’s a very crucial piece of infrastructure—very little would work properly without it.
- 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.
- 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:
- 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.
- 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:
- WordPress’s function library, which is stocked with functions—like the methods of the
WP_Queryclass—that run SQL commands for you without you having to understand it, and
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?
SELECTstatement 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
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
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,
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:
SELECT * FROM wps_postsmeans “we’re in the
wps_poststable and we’re going to get every column of each row we fetch.”
WHERE post_content LIKEmeans “we’re only going to fetch rows where the contents of the
post_contentcolumn matches a pattern we set.”
'%WordPress%'is the pattern.
And let’s break down the pattern itself:
- The two
'characters start and end the string we’re looking for.
- The two
%characters are wildcard characters. They mean “Anything, of any length (including zero length), goes here.”
WordPressis 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:
WHERE Clause with
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%';
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
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.
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!
Image credit: Dan Olson