WordPress Database
WordPress stores all the content and settings in a MySQL or MariaDB database. A fresh install creates 12 tables, and all of them start with the table prefix (wp_ by default).
$ wp db tables
wp_commentmeta
wp_comments
wp_links
wp_options
wp_postmeta
wp_posts
wp_term_relationships
wp_term_taxonomy
wp_termmeta
wp_terms
wp_usermeta
wp_users
$ wp db tables | wc -l
12
The default tables
| Table | What it stores |
|---|---|
| wp_posts | Posts, pages, menu items, attachments, revisions and all custom post types |
| wp_postmeta | Extra data (custom fields) of every post |
| wp_comments | Comments |
| wp_commentmeta | Extra data of comments |
| wp_users | Users: login, hashed password, email |
| wp_usermeta | Extra data of users, like the role and the name |
| wp_terms | The names of categories, tags and other terms |
| wp_term_taxonomy | The taxonomy of each term (category or tag) and the parent |
| wp_term_relationships | Connects posts with terms |
| wp_termmeta | Extra data of terms |
| wp_options | Site settings, active plugins and the active theme |
| wp_links | Old Links Manager data, not used anymore |
Read the data with SQL
You can open the database in phpMyAdmin, or use wp db query. First the posts table:
$ wp db query "SELECT ID, post_title, post_type, post_status FROM wp_posts ORDER BY ID"
ID post_title post_type post_status
1 Hello world! post publish
2 Sample Page page publish
3 Privacy Policy page draft
4 Navigation wp_navigation publish
Look at the post_type column: only the first row is a post. The others are two pages and the wp_navigation menu of the block theme, so this one table stores many kinds of content. Now the settings table, where every option is a row of name and value:
$ wp db query "SELECT option_name, option_value FROM wp_options WHERE option_name IN ('blogname','siteurl','posts_per_page','template')"
option_name option_value
blogname Demo Site
posts_per_page 10
siteurl http://example.com
template twentytwentyfive
How the tables are connected
One post can have many rows in wp_postmeta. Let us add two custom fields to the first post and see them:
$ wp post meta add 1 price 499
Success: Added custom field.
$ wp post meta add 1 color blue
Success: Added custom field.
$ wp db query "SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id = 1 AND meta_key IN ('price','color')"
post_id meta_key meta_value
1 price 499
1 color blue
A category connects to a post in three steps: wp_terms (the name), wp_term_taxonomy (it is a category) and wp_term_relationships (this post has it):
$ wp db query "SELECT p.post_title, t.name, tt.taxonomy FROM wp_posts p JOIN wp_term_relationships tr ON tr.object_id = p.ID JOIN wp_term_taxonomy tt ON tt.term_taxonomy_id = tr.term_taxonomy_id JOIN wp_terms t ON t.term_id = tt.term_id WHERE p.ID = 1"
post_title name taxonomy
Hello world! Uncategorized category
Never write directly in the database
Reading with SQL is fine for learning. But for changes, use the WordPress functions (wp_insert_post(), update_option() and others). They keep the caches and the connected tables correct. If a direct query is really needed, use the $wpdb object. See WordPress $wpdb Class.
| Exercises & Assignments |
|---|
| No Content Found. |
| Interview Questions & Answers |
|---|
| No Content Found. |