Skip to content

Instantly share code, notes, and snippets.

@mishterk
Last active July 1, 2020 11:21
Show Gist options
  • Save mishterk/fd4bc9ae71d7398135690fd8c31ca996 to your computer and use it in GitHub Desktop.
Save mishterk/fd4bc9ae71d7398135690fd8c31ca996 to your computer and use it in GitHub Desktop.
A basic example for querying data from custom tables created using ACF Custom Database Tables. For more info see https://hookturn.io/2019/09/how-to-use-acf-custom-database-tables-data-with-wp_query-objects/

How to use your custom table data with WP_Object queries

This example illustrates how to query an array of post IDs from a custom DB table then use the array in a WP_Query.

This can be much faster than using meta queries on a WP_Query object, particularly if you are matching multiple fields.

<?php
// Declare the global wpdb variable
global $wpdb;
// Write our custom query. In this query, we're only selecting the post_id field of each row that matches our set of
// conditions. Note the %s placeholders – these are dynamic and indicate that we'll be injecting strings in their place.
$SQL = "SELECT `post_id` FROM `wp_custom_db_table`
WHERE `date_start` >= %s
AND `location` = %s
ORDER BY `date_start` ASC;";
// Use $wpdb's prepare() method to replace the placeholders with our actual data. Doing it this way protects against
// injection hacks as the prepare() method santizes the data accordingly. The output is a prepared, sanitized SQL
// statement ready to be executed.
$SQL = $wpdb->prepare( $SQL, current_time( 'Ymd' ), 'some location' );
// Query the database with our prepared SQL statement, fetching the first column of the matched rows. In our case, we
// only queried the post_id field of each row so we know that the post_id fields will be the first column. The result
// here is an array of post_ids (provided we have a match)
$post_ids = $wpdb->get_col( $SQL );
if ( $post_ids ) {
// When calling WP_Query, we no longer need to worry about specifying a post type because we know exactly which post
// IDs we're after. We've also already ensured the correct order of our post IDs through the ORDER BY xxx ASC
// portion of our SQL query. So, we just tell WP_Query to return the WP_Post objects in the order of the post IDs we
// pass to it.
$query = new WP_Query( [
'post__in' => $post_ids,
'post_type' => 'any',
'posts_per_page' => 4,
'orderby' => 'post__in',
] );
// The rest is exactly as you normally would handle a WP_Query object.
if ( $query->have_posts() ) {
while ( $query->have_posts() ) {
$query->the_post();
// Do yo thang...
}
wp_reset_postdata();
}
} else {
// …
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment