User:JPMcC/Database Description20

1 Understanding the database structure
2 wp_posts
- 2.1 wp_posts - fields
- 2.2 wp_posts - sample records
3 wp_users
- 3.1 wp_users - fields
- 3.2 wp_users - sample records
4 wp_usermeta
- 4.1 wp_usermeta - fields
- 4.2 wp_usermeta - sample records
5 wp_categories
- 5.1 wp_categories - fields
- 5.2 wp_categories - sample records
6 wp_post2cat
- 6.1 wp_post2cat - fields
- 6.2 wp_post2cat - sample records

This article is a ROUGH DRAFT. The author is still working on this document, so please do not edit this without the author's permission. The content within this article may not yet be verified or valid. This information is subject to change.

The following is an outline and description of the database tables created during the standard installation of WordPress V 2.0. Currently, the only database supported by WordPress is MySQL version 3.23.23 or greater.

Because WordPress interfaces with this database by itself, you as an end user, shouldn't have to worry much about its structure. If you're Writing a Plugin, however, you may be interested in learning how to interface with the database directly. WordPress provides the wpdb Class to make this task easy.

Understanding the database structure

wp_posts holds all the POSTs and PAGES for your site, and is a good place to start learning the database structure. Once you've got your head round wp_posts, look at the tables which link to it - wp_users (and wp_usermeta), and wp_categories. However, to see which posts go in which categories, look at wp_post2cat.

wp_posts

wp_posts holds all the POSTs and PAGES for your site, one record per POST or PAGE. It's a bit confusing as some fields are used for POSTs, some for PAGEs, and some for both - see under Comments in the table below.

wp_posts - fields

The fields in the table are as follows:

Field	Type	Null	Default	Links to	Comments
ID	bigint(20)	No			Primary key - unique identifier for POST/PAGE
post_author	bigint(20)	No	0	wp_users -> ID	POST or PAGE
post_date	datetime	No	0000-00-00 00:00:00		POST or PAGE
post_date_gmt	datetime	No	0000-00-00 00:00:00		POST or PAGE
post_content	longtext	No			The full HTML text (POST or PAGE)
post_title	text	No			Title (POST or PAGE)
post_category	int(4)	No	0	wp_categories -> cat_ID Now replaced by wp_post2cat	POST only
post_excerpt	text	No			Optional summary (POSTonly)
post_status	enum('publish', 'draft', 'private', 'static', 'object', 'attachment')	No	publish		'draft', 'private' or 'publish' (POST) or 'static' (PAGE)
comment_status	enum('open', 'closed', 'registered_only')	No	open
ping_status	enum('open', 'closed')	No	open
post_password	varchar(20)	No
post_name	varchar(200)	No			'Slug' used in URLs (POST or PAGE)
to_ping	text	No
pinged	text	No
post_modified	datetime	No	0000-00-00 00:00:00		POST or PAGE
post_modified_gmt	datetime	No	0000-00-00 00:00:00		POST or PAGE
post_content_filtered	text	No
post_parent	bigint(20)	No	0	wp_posts -> ID	Optional hierarchy (PAGE only)
guid	varchar(255)	No
menu_order	int(11)	No	0		Sort Order (PAGE only)
post_type	varchar(100)	No
post_mime_type	varchar(100)	No
comment_count	bigint(20)	No	0

Top

wp_posts - sample records

This table shows three records from a typical wp_posts

Record ID=9 shows a PAGE (post_status of static) - note that the post_parent=7 means its parent page is record ID=7 in this table
Record ID=10 is a private POST (post_status of private)
Record ID=11 is a regular POST (post_status of publish)

ID:	9	10	11
post_author:	6 `[->]`	3 `[->]`	3 `[->]`
post_date:	2005-12-10 14:58:34	2005-12-10 16:10:32	2005-12-10 16:12:09
post_date_gmt:	2005-12-10 14:58:34	2005-12-10 16:10:32	2005-12-10 16:12:09
post_content:	My parent is a Main Page. Posted by Kathy	This post is about both cats and dogs but it's pri...	This is a public post about cats and dogs
post_title:	This is sub page two	This is another post from Paul	More from me
post_category:	0 `[->]`	0 `[->]`	0 `[->]`
post_excerpt:
post_status:	static	private	publish
comment_status:	open	open	open
ping_status:	open	open	open
post_password:
post_name:	this-is-sub-page-two	this-is-another-post-from-paul	more-from-me
to_ping:
pinged:
post_modified:	2005-12-10 14:58:34	2005-12-10 16:10:32	2005-12-10 16:12:09
post_modified_gmt:	2005-12-10 14:58:34	2005-12-10 16:10:32	2005-12-10 16:12:09
post_content_filtered:
post_parent:	7 `[->]`	0 `[->]`	0 `[->]`
guid:	URL?page_id=9	URL?p=10	URL?p=11
menu_order:	0	0	0
post_type:
post_mime_type:
comment_count:	0	0	0

Top

wp_users

wp_users holds information about all the users registered on the system. The contents of the fields are fairly obvious from the field names. However, note that much of the useful stuff about users is now stored in the wp_usermeta table.

wp_users - fields

The fields in the table are as follows:

Field	Type	Null	Default	Comments
ID	bigint(20)	No		Auto Increment; Primary Key
user_login	varchar(60)	No
user_pass	varchar(64)	No
user_nicename	varchar(50)	No
user_email	varchar(100)	No
user_url	varchar(100)	No
user_registered	datetime	No	0000-00-00 00:00:00
user_activation_key	varchar(60)	No
user_status	int(11)	No	0
display_name	varchar(250)	No

Top

wp_users - sample records

The table below shows three records from a typical wp_users

ID:	4	5	6
user_login:	peter	mary	kathy
user_pass:	5f4dcc3b5aa765d61d8327deb882cf99	5f4dcc3b5aa765d61d8327deb882cf99	5f4dcc3b5aa765d61d8327deb882cf99
user_nicename:	peter	mary	kathy
user_email:	peter@piper.org	mary@contrary.org	kathy@great.org
user_url:	http://	http://	http://
user_registered:	2005-12-10 12:19:12	2005-12-10 12:19:47	2005-12-10 12:22:12
user_activation_key:
user_status:	0	0	0
display_name:	peter	mary	kathy

Top

wp_usermeta

wp_usermeta is where relational database theory comes up against the real world :-) Basically, it is a place to dump any information related to users:

user_id is the ID of the user in wp_users;
meta_key is a text string saying what the data is (e.g. the user's description, maintained using the About Yourself box on the Profile screen); and
meta_value is the actual data

(see the Sample records below).

wp_usermeta - fields

Field	Type	Null	Default	Links to	Comments
umeta_id	bigint(20)	No			Auto_increment; Primary Key
user_id	bigint(20)	No	0	wp_users -> ID
meta_key	varchar(255)	Yes	NULL
meta_value	longtext	Yes	NULL

wp_usermeta - sample records

umeta_id:	24	35	36	25	26	27
user_id:	5 `[->]`	5 `[->]`	5 `[->]`	6 `[->]`	6 `[->]`	6 `[->]`
meta_key:	wp_user_level	description	rich_editing	first_name	last_name	nickname
meta_value:	2	I have got a little lamb It's fleece as white as snow And everywhere on earth I go My lamb is sure to go	true	Katherine	Great	kathy

Top

wp_categories

Categories enable posts to be grouped by topics of the user's choosing. Categories are either top-level (category_parent=0) or sub-categories of other categories (category_parent=cat_ID of the parent record). The links between categories and posts are held in wp_post2cat.

wp_categories - fields

Field	Type	Null	Default	Links to	Comments
cat_ID	bigint(20)	No			Auto_increment; Primary Key
cat_name	varchar(55)	No
category_nicename	varchar(200)	No
category_description	longtext	No
category_parent	bigint(20)	No	0	wp_categories -> cat_ID
category_count	bigint(20)	No	0

wp_categories - sample records

Note how the records with cat_ID = 4 and 5 are sub-categories of the record with cat_ID = 3.

cat_ID:	1	2	3	4	5
cat_name:	Uncategorized	Interesting	Animals	Dogs	Cats
category_nicename:	uncategorized	interesting	animals	dogs	cats
category_description:		Interesting Stuff	Furry friends	A dog is a type of animal	Cats are a type of animal
category_parent:	0 `[->]`	0 `[->]`	0 `[->]`	3 `[->]`	3 `[->]`
category_count:	1	1	0	2	1

Top

wp_post2cat

One post may be assigned to many categories. post2cat maintains these relationships.

wp_post2cat - fields

Field	Type	Null	Default	Links to	Comments
rel_id	bigint(20)	No			Auto_increment; Primary Key
post_id	bigint(20)	No	0	wp_posts -> ID
category_id	bigint(20)	No	0	wp_categories -> cat_ID

wp_post2cat - sample records

For example: this extract shows that the post with an ID of 10 in wp_posts is in two categories in wp_categories: the category with a cat_ID of 5 and the category with a cat_ID of 4.

rel_id:	6	7	8	11	12	13	14
post_id:	7 `[->]`	8 `[->]`	9 `[->]`	10 `[->]`	10 `[->]`	11 `[->]`	11 `[->]`
category_id:	1 `[->]`	1 `[->]`	1 `[->]`	5 `[->]`	4 `[->]`	5 `[->]`	4 `[->]`

Top

Codex