Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
You can access the WordPress database directly. We'll look at other and better ways of accessing data, such as accessing posts and pages through the loop and other units. But there might come a time when you need to go down there and get right into the data, and you don't want to use the WordPress API. In such cases, you have the ability to use the wpdb class and SQL to get your data directly. So, if you recall, we built this dbinfo plugin. It creates a little Dashboard Widget that output some information about the database.
We're going to use this plugin to do some further querying. To get a single field of data, we use the get_var method of the wpdb class. However, if we want to get more data, we have a couple other classes we can refer to. We have a class called get_col, which will get an entire column of data. So, if we want to get, for example, all the e-mails for a user in the database, so we'll create a variable called User Emails, and we'll call get_col, and then we type in our SQL.
So we'll say 'SELECT user_e-mail FROM,' and it's always a best practice to use
the variables in the wpdb class for the table names, just in case they change
in future versions.
Oops, I misspelled this.
Make sure you spell this correctly, user_e-mails.
So now we're going to take that data and just output it using the
So, go ahead and say echo var_dump($user_e-mails).
Go ahead and save it and refresh, and you can see it outputs these two variables. In addition to get_col, we also have get_ row, which will return a single row of data. So, if you wanted to see your user information, you could say "Your user info:" and then we can use my--I'm just creating a variable here--user_data equals wpdb, and then we use get_row and we say, SELECT *--which is every field-- FROM, and again we use the properties users, WHERE ID is equal to, and then I can get my own user ID by referring to this current_user object we created up here, saying current_user->ID.
Now, there is an optional variable you can append to this, which will tell whether you want to get your values in numbers or as an associative array. So, if we append ARRAY_N, that will then get our data as an associative array. So when we output it-- I've got a little L in there instead of a semicolon-- so let's go ahead and refresh, Your user info, Array. Oh! I forgot to wrap this in var_dump.
Important piece there, because that will actually parse and output the data; otherwise it just outputs either the variable value, if it's a simple variable, or the type of variable it is. So let's go ahead and refresh. So you can see it's numeric: 0, 1, 2. If instead I want to get the field names and have it set up as an associative array, I just change its value to ARRAY_A, and now I get the value. Usually with queries, this is a more user-friendly way to get it, but it does give you the option to get it in other ways if you want to.
The last one I want to look at is getting an entire result set. So, I'm going to go into the Terms database and get all of the post terms in the database. Let's copy this so it's bolded. We can go ahead, create a new variable called terms, and set it equal to $wpdb->get_results SELECT * FROM, and again, we concatenate on terms, and we don't need to specify.
We'll let it take the default. And then we can actually just echo it right here;
you don't need to create another.
So, go ahead and save it and refresh it, and you can see here is our terms.
Now, I didn't wrap it in
, so, it does not look as nice and pretty as this
does, but you get the idea.
We get all of our information, and it's basically an array of variables, and
inside there's an associative array.
So the wpdb class gives us a few different ways to get data out of the database.
In this video, we learned how to use this class to access either a single data point, a column of data, a row of data, or a full record set. Remember, it's a best practice to access the database through the built-in functions if you can. However, you do have these available to you, especially if you create your own tables in the WordPress database.
Get unlimited access to all courses for just $25/month.Become a member