Run Custom SQL Queries in WordPress with $wpdb->prepare and get_results

Home » MySQL » Run Custom SQL Queries in WordPress with $wpdb->prepare and get_results
October 25, 2022 MySQL
Run Custom SQL Queries in WordPress with $wpdb->prepare and get_results

WordPress’s WP_Query covers most querying needs, but it generates SQL through several abstraction layers that prevent certain optimisations: window functions, GROUP BY aggregations, multi-table joins, or UNION queries. The $wpdb global object exposes a direct connection to the WordPress database and provides prepared statement support via $wpdb->prepare(), making raw SQL safe while keeping queries flexible. $wpdb->prepare() uses %s for strings, %d for integers, and %f for floats — the placeholders follow printf syntax rather than PDO-style named parameters. The get_results() method returns an array of objects by default, but passing ARRAY_A returns associative arrays and ARRAY_N returns numerically indexed arrays — useful for CSV exports or bulk processing. get_var() retrieves a single value, get_row() returns one row, and query() executes an INSERT, UPDATE, or DELETE — choosing the most specific method avoids unnecessary data transfer. The $wpdb->last_error property stores the most recent MySQL error message, which should be checked during development to catch mistyped column names before deploying to production. Enabling SAVEQUERIES in wp-config.php logs all queries with timing data to $wpdb->queries, useful for profiling custom SQL alongside WP_Query calls in a staging environment. Always use $wpdb->prefix when constructing table names — hard-coding the prefix breaks installations where the database prefix differs from the default wp_. The EXPLAIN guide shows how to analyse the execution plan of any $wpdb query by prepending EXPLAIN to the same SQL string. The custom table post is the natural next step when $wpdb queries against wp_postmeta become too slow due to unindexed data patterns. Never construct SQL by concatenating user input directly — even inside a WordPress admin screen, an administrator account can be compromised, so parameterized queries are the only safe approach regardless of the trust level of the executing user.

Problem: WP_Query cannot express complex SQL patterns such as aggregations, multi-table joins, or window functions, leaving developers with no clean way to run optimised custom queries against the WordPress database safely.

Solution: Use $wpdb->prepare() with get_results(), get_var(), or get_row() to run parameterized raw SQL that is safe from injection while taking full advantage of MySQL query capabilities.

global $wpdb;

// 1. Count published posts per category (aggregation WP_Query cannot express efficiently)
$cat_counts = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT t.name, t.slug, COUNT(tr.object_id) AS post_count
         FROM {$wpdb->terms} t
         INNER JOIN {$wpdb->term_taxonomy} tt ON t.term_id = tt.term_id
         INNER JOIN {$wpdb->term_relationships} tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
         INNER JOIN {$wpdb->posts} p ON tr.object_id = p.ID
         WHERE tt.taxonomy = %s AND p.post_status = %s
         GROUP BY t.term_id
         ORDER BY post_count DESC
         LIMIT %d",
        'category', 'publish', 20
    ),
    ARRAY_A
);

// 2. Get the latest post for each author (correlated subquery)
$latest_per_author = $wpdb->get_results(
    "SELECT p.post_author, p.ID, p.post_title, p.post_date
     FROM {$wpdb->posts} p
     WHERE p.post_status = 'publish' AND p.post_type = 'post'
       AND p.post_date = (
           SELECT MAX(p2.post_date)
           FROM {$wpdb->posts} p2
           WHERE p2.post_author = p.post_author AND p2.post_status = 'publish'
       )
     ORDER BY p.post_date DESC"
);

// 3. Update a custom field for all posts in a category (bulk UPDATE)
$updated = $wpdb->query(
    $wpdb->prepare(
        "UPDATE {$wpdb->postmeta} pm
         INNER JOIN {$wpdb->term_relationships} tr ON pm.post_id = tr.object_id
         INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
         SET pm.meta_value = %s
         WHERE tt.term_id = %d AND pm.meta_key = %s",
        'reviewed', 42, '_review_status'
    )
);

NOTE: Remove SAVEQUERIES from wp-config.php before deploying to production — keeping it enabled forces WordPress to store every query string and its execution time in memory on every request, increasing RAM usage significantly on busy sites.

Related posts