Understanding data management in WordPress

WordPress is powered by MySQL database in backend and stores all the data for website including content, users, metadata, settings , links etc. The default installation of WordPress contains 11 tables. It is very minimal but very flexible. The plugins and themes that we install can create their own custom tables too. When a major release of WordPress is launched, few database changes are usually made. The table structure in WordPress is very consistent. The most important field in every table is unique ID field.  Following are the list of default tables that are available on WordPress installation

• wp_commentsmeta – Contains all metadata for comments
• wp_comments – Contains all comments , comments are linked posts by post id.
• wp_links – Contains all links added to WordPress
• wp_options – Stores all options defined under settings for WordPress. It also contains options created by plugins and themes. Also contains list of active themes and plugins.
• wp_postmeta – Contains all metadata for posts
• wp_posts – Contains posts of all types , pages, media records and revisions of each post. Most of the time it’s the largest table in the WordPress
• wp_terms – Contains all taxonomy terms defined for the website.
• wp_term_relationships – Joins taxonomy terms with content. Maps term to a post it references
• wp_term_taxonomy – Defines taxonomy to which each term is assigned. Allows hierarchical categories.
• wp_users – Contains all users created for the website.
• wp_usermeta – Contains all metadata associated with users.

To retrieve content we need to access wp_posts table. It stores all posts, pages, attachments, media etc. Attachments are media are not stored in the MySQL table, but stored in the filesystem of the server. But their file stored links are stored in MySQL table.  The field post_author stores stores unique ID of the wp_users table, post_date stores the date post is published, post_content stores main content of your post and post_title stores title of the post. One important field is post_status , it can have 8 statuses like publish, inherit, pending, private, future, draft, autodraft, future. The field post_type stores the type of the post either page or post or attachment etc. Custom post types can also be created.

The table wp_users contains data for registered member accounts. The fields user_login stores username of the user and user_pass stores encrypted password, user_email stores user’s email, user_url stores user’s web URL and user_registered stores user registration date.

The wp_comments table stores all the comments , pingbacks and trackbacks to your posts. The fields comment_id is unique id to the comment, comment_post_id is post id of the comment added to, comment_author stores the name of the commenter, comment_author_email stores email of the commenter, comment_author_url stores URL of the commenter, comment_date stores the date when comment is created.

Terms, relationships and taxonomies are broken down into three tables with one to many relationships to store hierarchical and multivalued categories to posts. When you add a new category or term to a post, then it needs to be updated in all the three tables to work it correctly. For example if you add a category ‘technology’ to a post, wp_terms table defines ‘technology’ and its slug, wp_term_taxonomy stores its mapping to category and its parent if hierarchical, wp_term_relationships identifies itself with the post the term ‘technology’ is attached.  This does make SQL query complex while retrieving posts related to a tag, but it makes your content cross linked and multivalued.

WordPress Database Class

WordPress features an object class wpdb defined in /wp-includes/wp-db.php to interact with database.  Using this class makes WordPress to execute your queries in a safest way. When you want to have a database query you need to mention about global variable $wpdb

$wpdb

The prepare() function is useful in escaping variables passed to your SQL queries, which is very useful in preventing SQL injection attacks. Its usage is explained in the following example.

<?php
global $wpdb;
$field_key = "address";
$field_value = "Bangalore, India";
$wpdb->query( $wpdb->prepare( "INSERT INTO $wpdb->my_custom_table
( id, field_key, field_value ) VALUES ( %d, %s, %s )", 1,
$field_key, $field_value ) );
?>

Notice that in the example $wpdb->my_custom_table is used to reference wp_my_custom_table. This is the proper way reference tables, and WordPress will take care of the table prefixes while querying table.

The get_var() function is used to retrieve single variable from a 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>';
?>

The get_row() function is used to retrieve entire row of an table as an object or as an array.

<?php
global $wpdb;
$thepost = $wpdb->get_row( $wpdb->prepare( "SELECT *
FROM $wpdb->posts WHERE ID = %d", 1 ) );
echo $thepost->post_title;
?>

The standard get_results() function is used for retrieving multiple rows of data from the table. It fetches multiple rows as 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 WordPress database class also adds specific functions for SQL INSERT, UPDATE, and DELETE queries.

insert() function inserts data into table in correct format.

<?php
global $wpdb;
$wpdb->insert(
$wpdb->postmeta,
array(
  'post_id' => '1',
  'meta_key' => 'address',
  'meta_value' => 'Bangalore, India.'
 ),
array(
  '%d',
  '%s',
  '%s'
 )
);
?>

The update() function works very similary to insert() , except you need to set $where and $where_format parameters.

<?php
global $wpdb;
$wpdb->update(
$wpdb->postmeta,
array(
'meta_value' => 'Bangalore, India'
),
array(
 'post_id' => '1',
 'meta_key' => 'address'
),
array(
 '%s'
),
array(
 '%d',
 '%s'
)
);
?>

The delete() function is structured similarly with $where and $where_format variables

$wpdb->delete(
$wpdb->postmeta,
array(
 'post_id' => '1',
 'meta_key' => 'address'
),
array(
 '%d',
 '%s'
)
);

The functions show_errors() and print_errors() together can be used to display any errors that occurred while executing a query

<?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 WordPress database class contains additional variables that store information about WordPress queries

<?php
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
?>

The powerful variable $queries can store all the queries if ‘SAVEQUERIES’ is enabled in wp-config.php file.

The database query class is a major asset while you are developing a plugin or developing a complex loop . You can also query database directly through phpmyadmin or using command prompt too. Whether you are working with themes, plugins or custom functions understanding how to work with WordPress database is very important. Understanding how WordPress stores data can help you to build more complex website features.