Wordpress

Working with Databases in WordPress : All You Need to Know

wpdb
wordpress DB, worpdress Database, mysql in wordpress

WordPress database is where all of the necessary website data is stored. Not just the basic information’s like usernames, passwords, posts, pages & comments, even the website theme & WordPress configuration settings. Today we will take a look at why and how you should manage the WordPress database. WordPress provides tons of functions that can be used to interact with the database.

WordPress Database Tables  List :

  1. wp commentmeta – Each comments has unique information called metadata, that will be available in this table.
  2. wp comments – All of the comments in the WordPress will be available in this table.
  3. wp links – This holds information related to the links entered into the Links feature of WordPress.
  4. wp options – This table contain’s the data that WordPress uses to store various preferences and configuration settings.
  5. wp postmeta – Each post has unique information called metadata, that data will be available in this section.
  6. wp posts – In WordPress, “posts” are articles that you write to populate your blog. This section will store that data. Pages and navigation menu items are also stored here.
  7. wp termmeta – Each term has unique information called metadata, that data will be available in this section.
  8. wp terms – The categories for both posts and links and the tags for posts are stored here.
  9. wp term relationships – Posts are associated with categories and tags from the wp_terms table, and this association is maintained in here.
  10. wp term taxonomy – This table describes the taxonomy (category, link, or tag) for the entries in the wp_terms table.
  11. wp usermeta – Each user has unique information called metadata, that data will be available in this section.
  12. wp users – The list of users is maintained in here.

What is SQL Query?

Full form of SQL is Structured Query Language, and it is used to manage db/DataBases. An SQL request issued to CRUD(Create, Retrieve, Update, Delete) data in the DataBase server is called a Query. WordPress uses MySQL queries to store and retrieve the data and it will generate it into web pages.

Now its time to work on mysql queries for WordPress :

WordPress provides tons of functions that can be used to interact with the wp database. In WordPress their is a global name for Database called $wpdb. Which means WordPress DataBase.

Get DataBase Table Prefix :

While installing the WordPress by default it will ask for table prefix. Now will see how to get the prefix by dynamically.

global $wpdb; (DataBase Global connection Declaration)
$table_prefix  = $wpdb->prefix; (WordPress DataBase Table Prefix)

Add the above simple code your php file to get wordpress table prefix.

Insert Query :
$insertQuery = “INSERT INTO “.$table_prefix.”options (option_name,option_value) VALUES (‘$variable1’, ‘$variable2’)”;
$wpdb->query($insertQuery );

Update Query :

$updateQuery = “UPDATE “.$table_prefix.”options SET `option_value` = $variable WHERE “.$table_prefix.”options.`option_name` = ‘value'”;
$wpdb->query($updateQuery );

Retrieve Query in loop :

$retrieve = $wpdb->get_results(“SELECT * FROM “.$table_prefix.”posts”);
foreach ( $retrieve as $my_value ) {
echo $my_value ->guid;  // Table column name
}

Retrieve Query for Single row :

$getUserId = $wpdb->get_row( “select * from “.$table_prefix.”users WHERE user_email = “.$wp_email.””);
echo $getUserId->ID;

 

Let’s wrap up this topic. I have covered the benefits of having a database to store and retrieve data and also learn how to use the Query to ease our work in managing db/DataBase, and lastly, how to perform basic a WordPress database management with SQL Queries. The above are the basic Queries of WordPress. You can utilize as per the requirement.

About the author

Chetan Hegde

Chetan Hegde

Chetan Hegde is the architect of ProCodersOnline.com. I'm a Software Engineer and technology enthusiast person who loves to learn and explore new things and technologies. When i'm not writing content for my websites, I love to travel and explore the nature.

Add Comment

Click here to post a comment