Explaining WordPress Database Tables

When you install WordPress ,11 default tables in the database are created. Each database table contains data for different sections or features WordPress. We are going to discuss the the structure of these tables, so you can easily understand where different parts of your website are stored.

Currently, a default WordPress installation creates the following tables:

wp_commentmeta : This table contains meta information about comments posted on a WordPress website. This table has four fields meta_id, comment_id, meta_key, and meta_value. Each meta_id is related to a comment_id. One example of comment meta information stored is the status of comment (approved, pending, trash, etc).

wp_comments : As the name suggests this table contains your WordPress comments. It contains comment author name, url, email, comment, etc.

wp_links : To manage blogrolls create by earlier versions of WordPress or the Link Manager plugin.

wp_options : This table contains most of your WordPress site wide settings such as: site url, admin email, default category, posts per page, time format, and much much more. The options table is also used by numerous WordPress plugins to store plugin settings.

wp_postmeta : This table contains meta information about your WordPress posts, pages, and custom post types. Example of post meta information would be which template to use to display a page, custom fields, etc. Some plugins would also use this table to store plugin data such as WordPress SEO information.

wp_posts : The name says posts but actually this table contains all post types or should we say content types. This table contains all your posts, pages, revisions, andcustom post types.

wp_terms : WordPress has a powerful taxonomy system that allows you to organize your content. Individual taxonomy items are called terms and they are stored in this table. Example, your WordPress categories and tags are taxonomies, and each category and tag inside them is a term.

wp_term_relationships : This table manages relationship of WordPress post types with terms in wp_terms table. For example this is the table that helps WordPress determine post X is in Y category.

wp_term_taxonomy : This table defines taxonomies for terms defined in wp_terms table. For example if you have a term “WordPress Tutorials“, then this table contains the data that says it is associated with a taxonomy categories. In short this table has the data that helps WordPress differentiate between which term is a category, which is a tag, etc.

wp_usermeta : Contains meta information about Users on your website.

wp_users : Contains User information like username, password, user email, etc.

 

Schema Diagram
wordpress_db_schema

Share your thoughts in comments below or if you face any issue, do let me know – Don’t forget to share this article – Sharing is Caring..

Batch changing URL of post images in wordpress

For blogger who self-hosts the WordPress blog publishing system on a web hosting server with own registered domain name, sometimes, you may decide to reorganize the blog link URL to make it tidier or to reflect new focus or theme of the blog. You may decide to change URL of your site as well. But changing URL can cause some issues. You may face broken images in all your previous posts since you just changed URL of site but didn’t update the URLs for images already added to posts.

e.g Before changes, your wordpress URL was: www.old-site.com & your images URL were like: www.old-site.com/2013/06/image.png
And After Change, your site URL is: www.new-site.com but your all posts are showing images from www.old-site.com

It’s not possible to correct URL of all images by editing each post since it can take too much time based on volume of site. A better approach is to Change URLs of all the images collectively by SQL Querying the database. Let’s see how easy it this below:

NOTE: We will be using SQL statements based on MySQL replace() function to modify the database. To run SQL queries, you need to login to MySQL database that houses WordPress tables via phpMyAdmin or login to the DB server and run MySQL client as root. If you don’t have access to it, then you can also do this from your wordpress admin dashboard by installing a plugin: Find & Replace ( http://wordpress.org/plugins/search-and-replace/ )

Go to MySQL Query mode & use the following SQL commands to fix broken links to blog in all WordPress posts and pages:

-- MySQL Query

UPDATE wp_posts SET post_content  =
replace(post_content, 'http://www.old-site.com', 'http:/ www.new-site.com');

-- see documentation of replace function for more understanding

The above SQL Query will replace all the links for www.old-site.com with www.new-site.com in post_content (DB Table that stores all posts content) & hopefully this will fix broken images links as well since now those images URLs will be changed to new domain.

Caution: Before making changes to database & run queries, make sure to backup your database because these changes will be permanent and can’t be undone. Dont forget to export your database from phpmyadmin as .sql 

Please share this with others who are facing such issues – Sharing is Caring..

WordPress: Change Color of specific menu Tab in Navigation menu

Complexity: A recent problem i faced was to change the color of only one tab in navigation menu bar of wordpress. I was using Ubermenu MegaMenu plugin but that didn’t provide any option for this. So i did it myself. You may also face such situation so following is smart solution to do that.

Let’s say you create a menu item “HOME” in your wordpress menu. When you create it in wordpress, a specific ID and Class is assigned to that menu item like:

<li id="menu-item-1704" class="menu-item menu-item-type-post_type menu-item-1704"><a href="http://www.wordpress.org">Home</a></li>

WordPress always assigns each “menu-item”  a unique ID Number.  So open stylesheet of your theme that styles menu, usually style.css or otherwise open header.php  file of your theme and add following code between head :


#menu-item-1704
{
    background:red;
}

For sake of example, i have used minimum css but you can add other menu properties as well for #menu-item-1704 to extend it’s style.

Another problem you may face is to find ID of your menu item. To find out id of your menu item that you want to style differently, go to Appearance > Menus.  Hover your mouse on menu item tab:

menu-navigation-inst-01

See the URL that will appear in browser status bar, it will look like:

www.somewebsite.com/wp-admin/nav-menus.php?edit-menu-item=377#menu-item-settings-377

menu-navigation-inst-1

 

Here 377 is menu item ID.

So once you find the menu item ID, you can easily style that specific menu item as i mentioned above.

Please share this post with others –Sharing is Caring !!!