WordPress Database Basics

Learn how WordPress stores website information in a database, understand important database tables, and explore how PHP and SQL work together to retrieve and manage WordPress data.

1. What Is a Database?

A database is an organized collection of information that can be stored, retrieved, updated, and managed electronically. WordPress commonly uses MySQL or a compatible database system such as MariaDB.

A WordPress database stores information such as:

2. How WordPress Uses a Database

WordPress uses PHP to process requests and communicate with the database. When a visitor opens a blog post, WordPress retrieves the relevant information and generates the page.

3. Important WordPress Database Tables

A standard WordPress installation creates several core tables. The table names usually begin with the database prefix wp_, but the prefix can be different.

4. Understanding the wp_posts Table

The wp_posts table is one of the most important WordPress database tables. It stores several types of content, including blog posts, pages, and media attachment records.

5. What Is SQL?

SQL stands for Structured Query Language. It is used to communicate with relational databases, including MySQL and MariaDB.

Common SQL operations include:

6. Example: Retrieve Published Posts

You can use a SQL query to retrieve published blog post titles. The following example is for learning and read-only inspection.

SELECT ID, post_title, post_date
FROM wp_posts
WHERE post_type = 'post'
  AND post_status = 'publish'
ORDER BY post_date DESC
LIMIT 10;

Explanation:

• SELECT chooses the columns to retrieve.
• FROM wp_posts specifies the table.
• WHERE filters for published blog posts.
• ORDER BY sorts the newest posts first.
• LIMIT 10 restricts the result to ten records.

7. Access the Database Using phpMyAdmin

phpMyAdmin is a web-based database management tool commonly included with local development packages and hosting panels.

On a live website, access phpMyAdmin through your hosting provider or another authorized database administration tool.

8. Connect to WordPress Data Using PHP

WordPress provides database functions and objects that developers can use instead of creating a separate database connection. The global $wpdb object is the standard WordPress database interface.

<?php

global $wpdb;

$posts = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT ID, post_title
         FROM {$wpdb->posts}
         WHERE post_type = %s
           AND post_status = %s
         ORDER BY post_date DESC
         LIMIT %d",
        'post',
        'publish',
        10
    )
);

if ($posts) {

    foreach ($posts as $post) {

        echo '<h3>' .
            esc_html($post->post_title) .
            '</h3>';

    }

} else {

    echo '<p>No published posts found.</p>';

}
?>

Explanation:

• $wpdb is the WordPress database object.
• $wpdb->posts provides the correctly prefixed posts table name.
• prepare() safely prepares the query using placeholders.
• get_results() retrieves multiple records.
• foreach loops through the returned posts.
• esc_html() safely escapes titles before displaying them as HTML text.

9. Retrieve a Single Value

The get_var() method retrieves a single value from a database query.

<?php

global $wpdb;

$count = $wpdb->get_var(
    $wpdb->prepare(
        "SELECT COUNT(*)
         FROM {$wpdb->posts}
         WHERE post_type = %s
           AND post_status = %s",
        'post',
        'publish'
    )
);

echo 'Published Posts: ' . absint($count);

?>

The query counts records that are published blog posts. absint() converts the displayed count to a non-negative integer.

10. Insert Data Using WordPress Database Functions

WordPress provides $wpdb->insert() to insert a record into a database table. The following example inserts a row into a custom table named wp_demo_messages.

<?php

global $wpdb;

$table_name = $wpdb->prefix . 'demo_messages';

$data = array(
    'message' => 'Hello WordPress'
);

$formats = array('%s');

$result = $wpdb->insert(
    $table_name,
    $data,
    $formats
);

if ($result !== false) {

    echo 'Record inserted successfully.';

} else {

    echo 'Unable to insert record.';

}

?>

11. Update and Delete Data

WordPress provides $wpdb->update() and $wpdb->delete() for modifying and removing records. Always use an appropriate condition to target the intended rows.

<?php

global $wpdb;

$table_name = $wpdb->prefix . 'demo_messages';

$updated = $wpdb->update(
    $table_name,
    array(
        'message' => 'Updated message'
    ),
    array(
        'id' => 1
    ),
    array('%s'),
    array('%d')
);

?>

<?php

global $wpdb;

$table_name = $wpdb->prefix . 'demo_messages';

$deleted = $wpdb->delete(
    $table_name,
    array(
        'id' => 1
    ),
    array('%d')
);

?>

In both examples, the condition targets the record whose ID is 1. Change the condition to match the intended record. These operations change database data, so test them using sample data before using them on a live website.

12. Database Security Best Practices

13. Common Database Errors

14. Practical Assignment

15. Lesson Summary

• WordPress commonly uses MySQL or MariaDB to store website data.
• The wp_posts table stores posts, pages, and other post types.
• The database prefix can differ from wp_.
• SQL is used to retrieve and manage relational database records.
• The $wpdb object provides WordPress database functionality.
• Methods such as get_results(), get_var(), and insert() support database operations.
• Prepared queries, escaping, and backups help protect website data.

Lesson 44 Quiz: WordPress Database Basics

Choose the correct answer for each question.