How to Connect WordPress to MySQL Database

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.