wordpress-database

WordPress Database Development

This skill covers WordPress database patterns including $wpdb usage, custom table creation, schema migrations, WP_Query advanced queries, meta/taxonomy/date queries, caching layers, and query debugging.

Critical Rules

1. Always use $wpdb->prepare() for any query containing user-supplied or variable data — no exceptions.
2. Always use $wpdb->prefix for table names — never hardcode wp_.
3. Use WordPress APIs first — WP_Query, meta API, and options API before reaching for raw SQL.
4. Use dbDelta() for table creation and schema changes — it handles CREATE TABLE idempotently.
5. Version your schema — store a version number in wp_options and compare on plugin load.
6. Never run schema changes on every page load — gate behind version checks.
7. Cache expensive queries — use transients or the object cache for repeated lookups.

$wpdb Core Methods

global $wpdb;

// Single value:
$count = $wpdb->get_var(
    $wpdb->prepare(
        "SELECT COUNT(*) FROM {$wpdb->prefix}orders WHERE status = %s",
        'completed'
    )
);

// Single row (object by default):
$row = $wpdb->get_row(
    $wpdb->prepare(
        "SELECT * FROM {$wpdb->prefix}orders WHERE id = %d",
        $order_id
    )
);

// Single column (flat array):
$ids = $wpdb->get_col(
    $wpdb->prepare(
        "SELECT id FROM {$wpdb->prefix}orders WHERE user_id = %d",
        $user_id
    )
);

// Multiple rows:
$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT id, total FROM {$wpdb->prefix}orders WHERE status = %s ORDER BY created_at DESC LIMIT %d",
        'pending',
        50
    )
);

// Output types for get_row / get_results:
$wpdb->get_row( $sql, OBJECT );   // Default — stdClass
$wpdb->get_row( $sql, ARRAY_A );  // Associative array
$wpdb->get_row( $sql, ARRAY_N );  // Numeric array

CRUD Methods

Prefer these for single-row operations — they handle escaping via format arrays:

// INSERT — returns false on failure.
$wpdb->insert(
    $wpdb->prefix . 'orders',
    array(
        'user_id'    => $user_id,
        'total'      => $total,
        'status'     => 'pending',
        'created_at' => current_time( 'mysql' ),
    ),
    array( '%d', '%f', '%s', '%s' )
);
$new_id = $wpdb->insert_id;

// UPDATE — returns rows affected or false.
$wpdb->update(
    $wpdb->prefix . 'orders',
    array( 'status' => 'completed' ),       // SET
    array( 'id' => $order_id ),             // WHERE
    array( '%s' ),                          // SET formats
    array( '%d' )                           // WHERE formats
);

// DELETE — returns rows affected or false.
$wpdb->delete(
    $wpdb->prefix . 'orders',
    array( 'id' => $order_id ),
    array( '%d' )
);

// REPLACE (insert or update on duplicate key):
$wpdb->replace(
    $wpdb->prefix . 'orders',
    array(
        'id'     => $order_id,
        'status' => 'refunded',
    ),
    array( '%d', '%s' )
);