Professional WordPress: Design and Development, 3rd Edition (2015)
Chapter 6. Data Management
WHAT’S IN THIS CHAPTER?
· Understanding the WordPress database
· Learning about database table relationships
· Working with the WordPress database class
· Debugging custom queries
Almost every website on the Internet today is connected to a database that stores information about that website. WordPress is no different and is powered by a MySQL database backend. This database stores all of the data for your website, including your content, users, links, metadata, settings, and more. This chapter covers how data is stored, what data is stored, and how to work with that data in WordPress to help you build amazing websites.
DATABASE SCHEMA
The default installation of WordPress contains 11 database tables. WordPress prides itself on being very lightweight and the database is the foundation for this. The database structure is designed to be very minimal yet allow for endless flexibility when developing and designing for WordPress. To understand the database schema, it helps to view a database diagram.
Figure 6.1 shows an overview of the WordPress database structure and the tables created during a standard WordPress installation. Keep in mind that plugins and themes have the ability to create custom tables. WordPress Multisite also creates additional tables so your WordPress database may contain more tables than just the default WordPress tables.
Figure 6.1 WordPress database diagram
When a new major release of WordPress is launched, a few database changes are usually made. These changes are usually very minor, such as changing a table field data type or removing a field that is no longer in use. Backward compatibility is a major focus for the WordPress development community so any changes made to the database are highly scrutinized and will rarely affect active plugins and themes. The Codex features a very thorough database changelog you can reference when a new version of WordPress is released:http://codex.wordpress.org/Database_Description#Changelog.
The table structure in WordPress is very consistent. Each table in your database contains a unique ID field, which is the primary key of the table. Each table also contains one or more indexes on fields, which improves the speed of data retrieval when executing queries against the data. As you saw in Chapter 5, each trip through the Loop in a theme is going to generate at least one, and perhaps several, queries to extract posts, pages, and their related metadata or comments.
The most important field in every table is the unique ID field. This field is not always named ID but is an auto-incrementing field used to give each record in the table a unique identifier. For example, when you first install WordPress, a default post is created titled “Hello world!” Because this is the first post created in the wp_posts table, the ID for this post is 1. Each post is given a unique ID that can be used to load post-specific information and can also be used as the joining field against other tables in the database.
There is one caveat to this, which has to do with post revisions, attachments, and custom post types. Each one of these entries is saved as a new record in the wp_posts table so they each gets its own unique ID, which means your published post IDs may not be sequential. For example, your first post may have an ID of 1, whereas your second post may have an ID of 15. It all depends on how many additional entries have been created between each post.
TABLE DETAILS
Currently, 11 database tables have been created for WordPress. Following is a list of those tables and details on what data they store:
· wp_commentmeta—Contains all metadata for comments.
· wp_comments—Contains all comments within WordPress. Individual comments are linked back to posts through a post ID.
· wp_links—Contains all links added via the Link Manager section. The table still exists, but core functionality was deprecated in WordPress 3.5. For more information, visit http://codex.wordpress.org/Links_Manager.
· wp_options—Stores all website options defined under the Settings Screen. Also stores plugin options, active plugins and themes, and more.
· wp_postmeta—Contains all post metadata (custom fields).
· wp_posts—Contains posts of all types (default and custom post types), pages, media records, and revisions. Under most circumstances, this is the largest table in the database.
· wp_terms—Contains all taxonomy terms defined for your website, mapping their text descriptions to term numbers that can be used as unique indexes into other tables.
· wp_term_relationships—Joins taxonomy terms with content, providing a membership table. It maps a term such as a tag or category name to the page or post that references it.
· wp_term_taxonomy—Defines the taxonomy to which each term is assigned. This table allows you to have categories and tags with the same name, placing them in different named taxonomies.
· wp_users—Contains all users created in your website (login, password, e-mail).
· wp_usermeta—Contains metadata for users (first/last name, nickname, user level, and so on).
Each database table has a specific purpose within WordPress. The next section breaks down some of the more common tables and looks at some examples of working with each.
WordPress Content Tables
To retrieve all of your website content, you’ll be accessing the wp_posts table. This table stores all of your posts, pages, attachments, revisions, and more. Attachment records are stored in this table, but the actual attachments are not. They are physically stored on your hosting server as a standard file. The following SQL query is an example of how to extract all of your posts from the database, and is the short form of what happens in the default WordPress Loop:
SELECT * FROM wp_posts
WHERE post_type = 'post'
AND post_status = 'publish'
ORDER BY post_date DESC
This query selects all records from wp_posts with a post_type of 'post'. The post_type field designates what type of content you are viewing. To return all pages, just change that value to 'page'. In this example, you want published posts only, so make sure post_status is set to 'publish'. You are also ordering your table records by post_date descending, so your posts will be displayed in reverse chronological order. Querying data and what tools are available to help you do so are discussed later in this chapter.
Let’s explore some of the more useful fields in the wp_posts table. You already know your ID field contains your post’s unique ID. The post_author field is the unique ID of the author of the post. You can use this to retrieve author-specific data from the wp_users table. The post_date is the date the post was created. The post_content field stores the main content of your post or page and post_title is the title of that content.
One very important field is the post_status field. Currently, eight different post statuses are defined in WordPress:
· publish—A published post or page.
· inherit—A post revision.
· pending—Post that is pending review by an administrator or editor.
· private—A private post.
· future—A post scheduled to publish at a future date and time.
· draft—A post still being created that has not been published.
· auto-draft—A post revision that WordPress saves automatically while you are editing.
· trash—Content is in the trash bin and can still be recovered.
Post status comes into play when contributor roles are used to limit a post creator’s ability to post or edit existing content. As with almost everything in WordPress, custom post statuses can be created by plugins and themes.
NOTE The use of roles is discussed in Chapter 12, and their impact on content management workflow is discussed in Chapter 15.
The post_type is also stored in the wp_posts table. This value is what distinguishes different types of content in WordPress: posts, pages, revisions, menus, and attachments. Since the release of WordPress 2.9, custom post types can be created, which opens the door to endless possibilities when defining custom content in WordPress.
The wp_users table contains data for your registered member accounts. Again, you see the ID field indicating the unique identifier for user records. The user_login is the username of the user. This is the value the user must enter when logging in to WordPress. Theuser_pass field contains the encrypted user password. The registered user’s e-mail is stored in the user_email field. The user_url field contains the member’s website and the user registration date is saved in user_registered.
Next you will explore the wp_comments table. This table stores all of the comments, pingbacks, and trackbacks for your website.
Viewing the comment records, you’ll notice the ID field is named comment_ID. Even though this field is not named ID, it is still the unique identifier for this record in the table. The comment_post_ID is the unique ID of the post the comment was added to. Remember that by default you don’t have to be logged in to make comments in WordPress. For this reason, you’ll see similar fields as in your users table.
The comment_author field stores the name of the commenter. If the comment is a pingback or trackback, it will contain the name of the post that sent the ping. The comment_author_email contains the commenter’s e-mail address, and his or her website is stored incomment_author_url. Another important field is the comment_date, which is the date the comment was created. This field is used to display your post comments in the correct order.
WordPress Taxonomy Tables
Terms, relationships, and taxonomies are broken into three distinct tables to allow many-to-one relationships between categories, tags, items in custom taxonomies, and posts. These relationships are hierarchical and multi-valued. While you could add an array of tag or category identifiers to each row in the wp_posts table, for example, that approach puts an explicit limit on the number of descriptive relationships for each post while also wasting space allocated for tags or categories that may not be assigned.
If you create a category called “scary stories,” and put four posts in that category, all three taxonomy-related tables are updated:
· One row in the wp_terms table defines “scary stories” and its slug, or diminutive form, used in URLs. This relationship gets a unique identifier (key) useful for matching the term to other tables.
· One row in the wp_term_taxonomy table maps “scary stories” to the “category” taxonomy. This relationship also gets a unique key, representing the combination of “scary stories” in “category.” If you also create a custom taxonomy and have a “scary stories” entry in it, there will be a different row in the wp_term_taxonomy table for that mapping, along with its unique key.
· Four rows in the wp_term_relationships table map the “scary stories in category” identifier to the post identifiers for each of the posts that are in the category.
The workhorse operator in working with taxonomy tables is the SQL JOIN, sometimes referred to as the “product” of two (or more) tables. A JOIN builds a temporary table with each row in one table mapped to every row in the second and successive tables; then theWHERE part of a JOIN operation selects those rows where specific fields in each row match. To find all of the posts in the “scary stories” category, WordPress first finds the identifier for this term and taxonomy pair, selects the appropriate rows from thewp_term_relationships table, and then does a JOIN on the wp_posts and the selected rows from the relationships table: That last JOIN is SQL-ese for “extract all of the posts with identifiers in this list” where the list is computed on-the-fly.
Figure 6.2 shows a graphical representation of the joins between the wp_posts table and taxonomy tables in WordPress.
Figure 6.2 Taxonomy tables relationship
While this makes the SQL for selecting content associated with a particular tag or category more complex, requiring the use of a multi-table JOIN operations to implement the “name in a taxonomy in a relationship” matching, it is powerful in allowing content to be given rich and multi-valued descriptions, and for category, taxonomy, and tag names to have independent name spaces.
WORDPRESS DATABASE CLASS
WordPress features an object class with method functions for working with the database directly. This database class is called wpdb and is located in wp-includes/wp-db.php. Any time you are querying the WordPress database in PHP code, you should use the wpdb class. The main reason for using this class is to allow WordPress to execute your queries in the safest way possible.
Simple Database Queries
When using the wpdb class, you must first define $wpdb as a global variable before it will be available for use. To do so, just drop this line of code directly preceding any $wpdb function call:
global $wpdb;
One of the most important functions in the wpdb class is the prepare() function. This function is used for escaping variables passed to your SQL queries. This is a critical step in preventing SQL injection attacks on your website.
WARNING All queries should be passed through the prepare() function before being executed.
The prepare() function accepts a minimum of two parameters:
$wpdb->prepare( $query, $value1 );
The $query parameter is the database query you want to run. The $value1 parameter is the first value you want to replace in the query. You can add additional value parameters as needed. Let’s look at an example:
<?php
global $wpdb;
$field_key = "address";
$field_value = "1428 Elm St";
$wpdb->query( $wpdb->prepare( "INSERT INTO $wpdb->my_custom_table
( id, field_key, field_value ) VALUES ( %d, %s, %s )", 1,
$field_key, $field_value ) );
?>
This example adds data into a non-default, custom table in WordPress that you would have previously created. When using prepare(), make sure to replace any variables in your query with %s for strings, %d for integers, and %f for floats. Then list the variables as parameters for the prepare() function in the exact same order. In the preceding example, %d represents 1, %s represents $field_key, and the second %s represents $field_value. Examples throughout this section all use the prepare() function, which highlights its importance when working with database queries.
Notice that this example uses $wpdb->my_custom_table to reference the table in WordPress. This translates to wp_my_custom_table if wp_ is the table prefix. This is the proper way to determine the correct table prefix when working with tables in the WordPress database.
NOTE When installing WordPress, you can set a custom database table prefix. By default, this is wp_, but many people choose to change this prefix for security purposes. Using $wpdb-> is the correct way to determine what this table prefix is for any WordPress installation.
The wpdb query() method is used to execute a simple query. This function is primarily used for SELECT statements. Despite its name, it’s not only for SQL SELECT queries, but will execute any SQL statement against the database. Here’s a basic query function example:
<?php
global $wpdb;
$wpdb->query( $wpdb->prepare( " DELETE FROM $wpdb->my_custom_table WHERE
id = %d AND field_key = %d ", 1, 'address' ) );
?>
As you can see, you execute your query using the wpdb class query() function to delete the field "address" with an ID of 1. Although the query() function allows you to execute any SQL query on the WordPress database, other database object class functions are more appropriate for SELECT queries. For instance, the get_var() function is used for retrieving a single variable from the database:
<?php
global $wpdb;
$comment_count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*)
FROM $wpdb->comments
WHERE comment_approved = %d;", 1 ) );
echo '<p>Total comments: ' . $comment_count . '</p>';
?>
This example retrieves a count of all approved comments in WordPress and displays the total number. Although only one scalar variable is returned, the entire result set of the query is cached. It’s best to try and limit the result set returned from your queries using aWHERE clause to only retrieve the records you actually need. In this example, all comment record rows are returned, even though you display the total count of comments. This would obviously be a big memory hit on larger websites.
Complex Database Operations
To retrieve an entire table row, you’ll want to use the get_row() function. The get_row() function can return the row data as an object, an associative array, or a numerically indexed array. By default, the row is returned as an object, in this case an instance of the per-post data. Here’s an example:
<?php
global $wpdb;
$thepost = $wpdb->get_row( $wpdb->prepare( "SELECT *
FROM $wpdb->posts WHERE ID = %d", 1 ) );
echo $thepost->post_title;
?>
This retrieves the entire row data for post ID 1 and displays the post title. The properties of $thepost object are the column names from the table you queried, which is wp_posts in this case. To retrieve the results as an array, you can send in an additional parameter to the get_row() function:
<?php
global $wpdb;
$thepost = $wpdb->get_row( $wpdb->prepare( "SELECT
* FROM $wpdb->posts WHERE ID = %d", 1 ), ARRAY_
print_r ( $thepost );
?>
By using the ARRAY_A parameter in get_row(), your post data is returned as an associative array. Alternatively, you could use the ARRAY_N parameter to return your post data in a numerically indexed array.
Standard SELECT queries should use the get_results() function for retrieving multiple rows of data from the database. The following function returns the SQL result data as an array:
<?php
global $wpdb;
$liveposts = $wpdb->get_results( $wpdb->prepare( "SELECT ID, post_title
FROM $wpdb->posts WHERE post_status = %d ", 'publish' ) );
foreach ( $liveposts as $livepost ) {
echo '<p>' .$livepost->post_title. '</p>';
}
?>
The preceding example is querying all published posts in WordPress and displaying the post titles. The query results are returned and stored as an array in $liveposts, which you can then loop through to display your query values.
The WordPress database class also features specific functions for UPDATE, INSERT, and DELETE statements. These three functions eliminate the need for custom SQL queries because WordPress will create them for you based on the values passed into the function. Here is how the insert() function is structured:
$wpdb->insert( $table, $data, $format );
The $table variable is the name of the table you want to insert a value into. The $data variable is an array of field names and data to be inserted into those field names. The final parameter is $format, which defines an array of formats to be mapped to each of the values in $data. So, for example, if you want to insert data into the post meta table, you would execute this:
<?php
global $wpdb;
$wpdb->insert(
$wpdb->postmeta,
array(
'post_id' => '1',
'meta_key' => 'address',
'meta_value' => '1428 Elm St.'
),
array(
'%d',
'%s',
'%s'
)
);
?>
In this example you execute the insert() function, passing in three variables through an array. Notice how you set post_id, meta_key, and meta_value as the three fields you are inserting. You can pass any field available in the table you are inserting with data to insert into that field. The final value is the formatting array, which works very much like the prepare() function formatting we discussed earlier in this chapter. The array contains a format for each of the three values being inserted into the database.
The update() function works very similarly to the insert() function, except you also need to set the $where clause and $where_format variables so WordPress knows which records to update and how to format:
$wpdb->update( $table, $data, $where, $format, $where_format );
The $where variable is an array of field names and data for the SQL WHERE clause. This is normally set to the unique ID of the field you are updating, but can also contain other field names from the table.
<?php
global $wpdb;
$wpdb->update(
$wpdb->postmeta,
array(
'meta_value' => '333 Wonderview Ave'
),
array(
'post_id' => '1',
'meta_key' => 'address'
),
array(
'%s'
),
array(
'%d',
'%s'
)
);
?>
In the preceding example, you execute the update() function to update the post metadata inserted in the previous example. Notice that the third parameter you send is an array containing your WHERE clause values, in this case the post ID and meta key name. The preceding query updates the post meta address value for post ID 1. Remember that you can send multiple values through the WHERE parameter when updating a table record. The final two array parameters are used to format the values being updated.
The delete() function is used to delete data from a WordPress database table. This function is structured similarly to the previous two functions, shown here:
$wpdb->delete( $table, $where, $where_format );
The $where parameter sets the WHERE clause in your query, which determines what data to delete. The $where_format parameter is used to set the format of the values you are passing to the function. As an example, let’s delete the post address meta data you created in the previous examples:
$wpdb->delete(
$wpdb->postmeta,
array(
'post_id' => '1',
'meta_key' => 'address'
),
array(
'%d',
'%s'
)
);
WARNING Any time you are deleting data, it is very important you test your code thoroughly on dummy data to verify it works as expected. Running untested code on a production website could have disastrous results.
The insert(), update(), and delete() functions shown do not need to be wrapped with the prepare() function. These functions actually use the prepare() function after concatenating the query from the values passed to the functions. This is a much easier method than manually creating your INSERT, UPDATE, and DELETE queries in WordPress.
Dealing with Errors
Any time you are working with queries, it’s nice to see error messages. By default, if a custom query fails, nothing is returned so it’s hard to determine what is wrong with your query. The wpdb class provides functions for displaying MySQL errors to the page. Here’s an example of using these functions:
<?php
$wpdb->show_errors();
$liveposts = $wpdb->get_results( $wpdb->prepare("SELECT ID, post_title
FROM $wpdb->posts_FAKE WHERE post_status = 'publish'") );
$wpdb->print_error();
?>
The show_errors() function must be called directly before you execute a query. The print_error() function must be called directly after you execute a query. If there are any errors in your SQL statement, the error messages are displayed. You can also call the $wpdb->hide_errors() function to hide all MySQL errors, or call the $wpdb->flush() function to delete the cached query results.
The database class contains additional variables that store information about WordPress queries. Following is a list of some of the more common variables:
var_dump( $wpdb->num_queries ); // total number of queries ran
var_dump( $wpdb->num_rows ); // total number of rows returned by the last query
var_dump( $wpdb->last_result ); // most recent query results
var_dump( $wpdb->last_query ); // most recent query executed
var_dump( $wpdb->col_info ); // column information for the most recent query
Add the preceding code directly after you execute a query to see the results. This is very useful when determining why a database query isn’t working as expected.
Another very powerful database variable is the $queries variable. This stores all of the queries run by WordPress. To enable this variable, you must first set the constant value SAVEQUERIES to true in your wp-config.php file. This tells WordPress to store all of the queries executed on each page load in the $queries variable. First drop this line of code in your wp-config.php file:
define( 'SAVEQUERIES', true );
Now all queries will be stored in the $queries variable. You can display all of the query information like so:
var_dump( $wpdb->queries ); // displays all queries executed during page load
This is especially handy when troubleshooting slow load times. If a plugin is executing an obscene number of queries, that can dramatically slow down load times in WordPress. Remember to disable the SAVEQUERIES constant option when you are finished viewing queries because storing all queries can also slow down load times.
The database query class is a major asset when working with the WordPress database directly, as you will see when developing a plugin or building a more complex Loop. All of the previously mentioned database class functions use specific escaping techniques to verify that your queries are executed in the safest manner possible. To borrow from Randall Munroe’s “Little Bobby Tables” xkcd joke (xkcd #327), you don’t want a user handcrafting an input item that contains DROP TABLES as a malicious SQL injection, resulting in the loss of your WordPress database tables. The query preparation and escaping functions ensure that inputs don’t become SQL functions, no matter how craftily they’re set up. It is essential that you follow these methods for querying data to ensure your website is the most efficient and uses the safest techniques possible.
DIRECT DATABASE MANIPULATION
There may be times when you want to work with the WordPress database data directly. This can include accessing custom database tables created by a plugin or theme. To do this, you’ll need to use SQL to query the data from the MySQL database. Remember that the WordPress APIs provide access to all of the WordPress tables and only very occasionally will you need to access the tables directly. All example queries in this chapter use the wp_ prefix for tables, but your database tables may use a different prefix as defined in your wp-config.php file when installing WordPress.
One of the most common methods for working with a WordPress database directly is to use phpMyAdmin. As described in Chapter 3, phpMyAdmin is a free software tool provided by most hosting companies for administering MySQL databases through a web interface. Most of the examples in this section involve direct interaction with MySQL, and you’ll need to use an SQL command line for their execution. Figure 6.3 shows the default database view using phpMyAdmin.
Figure 6.3 phpMyAdmin viewing a WordPress database
To run SQL statements in phpMyAdmin, simply click the SQL tab across the top. Here you can execute any queries against your WordPress database. We always recommend creating your query directly in phpMyAdmin first before moving it over to your PHP scripts. The reasoning behind this is that debugging SQL statements is much faster directly in phpMyAdmin than it is using PHP code in WordPress. Once you have perfected your query, you can use it in your PHP code and you can be confident the results will be as expected. In the examples that follow, you’ll be using raw SQL queries. Remember that if you want to run these queries in a theme or plugin, you’ll need to wrap the queries in the WordPress database class.
One of the most commonly accessed tables is the wp_posts table. Remember that this table stores all posts, pages, custom post types, revisions, and even attachment records. The different types of content are defined by the post_type field. WordPress 2.9 introduced the ability for developers to define custom post types, which is discussed in greater detail in Chapter 7. This means that additional post_type values may exist in this field. To view all post revisions in your database, you can run this query:
SELECT * FROM wp_posts
WHERE post_type = 'revision'
This returns all records in wp_posts that are of a revision post_type. You can modify the preceding query to view all post attachments that have been uploaded to WordPress:
SELECT guid, wp_posts.* FROM wp_posts
WHERE post_type = 'attachment'
This example places the field guid as the first value to be returned in the query. The guid field contains the full URL of the attachment file on the server.
The wp_options table contains all of the settings saved for your WordPress installation. Options saved in this table are saved with an option_name and option_value. Therefore, the actual field name you call will always be those two names, rather than a specific field based on the option value. Following are two extremely important records in this table:
SELECT * FROM wp_options
WHERE option_name IN ( 'siteurl','home' )
This query returns two records, one where option_name is home and another where option_name is siteurl. These are the two settings that tell WordPress what the domain of your website is. If you ever need to change your website’s domain, you can run a query to update these two values like so:
UPDATE wp_options
SET option_value = 'http://yournewdomain.com'
WHERE option_name IN ('siteurl','home')
Once this query runs, your website will instantly run under the new domain. Remember that this only updates the website’s domain in WordPress. Attachment URLs in posts and pages will also need to be updated to point to the new domain. Plugins can also store the domain information, so be sure to test in a development environment before updating a production website. If you access the old domain, you will be redirected to the new one. If you were logged in, your cookies and session will be invalidated and you will have to log in again. This is a great technique if you built a new website under a subdomain (for example, http://new.example.com) and are updating the URLs to push the website live.
The wp_options table contains other very important fields. To view all active plugins on your website, you can view the active_plugins option_name like so:
SELECT *
FROM wp_options
WHERE option_name = 'active_plugins'
The options table also stores all options defined by plugins. Most plugins activated in WordPress will have some type of settings page. These settings are generally saved in wp_options so the plugins can retrieve these settings as needed. For example, the Akismet plugin stores an option named akismet_spam_count that stores the total number of spam comments. You can view this option by running the following query:
SELECT * FROM wp_options
WHERE option_name = 'akismet_spam_count'
The wp_users table contains all of the users you currently have set up in WordPress. If you allow open registration on your website, new users will be created in this table as they join your site. The wp_users table stores very important user information including username, password, e-mail, website URL, and date registered. Say you want to export all of your users’ e-mail addresses. You can easily do so by running the following query:
SELECT DISTINCT user_email
FROM wp_users
Now you can easily export all of the e-mail addresses loaded into WordPress! Another common query used in wp_users is to reset a user’s password. You can do this in a couple of different ways, but if you are absolutely locked out of WordPress, you can always reset the password directly in the database. To do so, you need to update the user_pass field from the MySQL command line:
UPDATE wp_users
SET user_pass = MD5('Hall0w33n')
WHERE user_login ='admin'
LIMIT 1;
Running this query resets the admin password to Hall0w33n. Notice how you wrap the new password in MD5(). This converts the password to an MD5 hash. Since WordPress 2.5, passwords are now salted and hashed using the phpass encryption library rather than MD5. Not to worry, however, because WordPress is built to detect MD5 hash passwords and convert them to phpass encryption instead. So the preceding query will successfully reset your password in WordPress.
The wp_comments table stores all comments submitted to your website. This table contains the comment, author, e-mail, website URL, IP address, and more. Here’s an example query for displaying comments:
SELECT wc.* FROM wp_posts wp
INNER JOIN wp_comments wc ON wp.ID = wc.comment_post_ID
WHERE wp.ID = '1554'
This query returns all comments for post ID 1554. Another important field in wp_comments is the user_id field. If a user is logged in to your website and posts a comment, this field will contain his or her user ID. Consider the following code, which displays all comments left by the user admin:
SELECT wc.* FROM wp_comments wc
INNER JOIN wp_users wu ON wc.user_id = wu.ID
WHERE wu.user_login = 'admin'
In the database diagram in Figure 6.1, the arrows show the relationships between each table. This is incredibly useful when writing custom queries to retrieve data directly from the database. For example, to retrieve all comments for a particular post you could run this query:
SELECT * FROM wp_comments
INNER JOIN wp_posts ON wp_comments.comment_post_id = wp_posts.ID
WHERE wp_posts.ID = '1'
This query returns all comments for post ID 1. Notice how you join the wp_comments.comment_post_ID field to the wp_posts.ID field. The SQL JOIN is necessary because there is an N:1 relationship between comments and posts; each post may have many comments but comments apply to only one post. These two fields are shown in the diagram as the joining fields for these tables. Also consider the following example, which demonstrates how to join the wp_users and wp_usermeta tables together:
SELECT * FROM wp_users
INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
WHERE wp_users.ID = '1'
As you can see in the database diagram, the wp_users.ID field was joined to the wp_usermeta.user_id field. The preceding query retrieves all of the user information, including user metadata, for user ID 1, which is the default admin account. Again, the database diagram makes it extremely easy to determine how tables are joined by index value inside the WordPress database, and how logical INNER JOIN operations can build result sets of related table rows.
If you are interested in learning more about SQL, you can read some amazing tutorials at http://www.w3schools.com/sql/.
SUMMARY
This chapter covered the WordPress database schema, database table relationships, the WordPress database class, and the proper way to debug database queries. Whether working with themes, plugins, or custom functions, understanding how to work with the WordPress database is very important. Understanding where and how WordPress stores data in the database can help as you develop more complex website features.
Next we’ll cover custom content in WordPress using custom post types. We’ll also cover custom taxonomies, custom metadata, and the power and importance of both when developing WordPress websites.