Advanced WordPress Development

Chapter 7: WordPress Database

7.1 Database Structure

WordPress uses MySQL/MariaDB with specific table structure.

Core Tables:

• wp_posts: Posts, pages, custom post types
• wp_postmeta: Post metadata
• wp_users: User accounts
• wp_usermeta: User metadata
• wp_options: Site settings and options

7.2 Using WPDB Class

Interact with database using WordPress database class.

global $wpdb;

// Prepare query
$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM {$wpdb->posts} WHERE post_type = %s",
        'product'
    )
);

7.3 Database Queries

Perform safe database queries.

// Insert data
$wpdb->insert(
    $wpdb->prefix . 'custom_table',
    array(
        'column1' => 'value1',
        'column2' => 'value2',
    ),
    array('%s', '%s')
);

// Update data
$wpdb->update(
    $wpdb->prefix . 'custom_table',
    array('column1' => 'new_value'),
    array('id' => 1),
    array('%s'),
    array('%d')
);

7.4 Custom Database Tables

Create custom database tables for plugins.

// Create custom table
function create_custom_table() {
    global $wpdb;
    
    $table_name = $wpdb->prefix . 'custom_table';
    
    $charset_collate = $wpdb->get_charset_collate();
    
    $sql = "CREATE TABLE $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        column1 varchar(255) NOT NULL,
        column2 text,
        PRIMARY KEY (id)
    ) $charset_collate;";
    
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);
}

7.5 Database Optimization

Optimize database for better performance.

• Use indexes on frequently queried columns
• Clean up unused post revisions
• Remove spam comments
• Optimize database tables regularly
• Use caching to reduce database queries