Custom MySQL Query for Post and Post Meta

Question

I have a custom table called "tags" that contains a charity_id that is a reference to a post ID ... I need to query the "tags" table to grab this charity_id and then pull in the post title and a few of the meta data.

Ideally, the end result would produce the following:

tags.serial_number, post.title, post_meta_data.post_title, post_meta_data.location_city, post_meta_data.location_state, post_meta_data.location_country

Answer 1

You have to use JOIN for that. Try it like this:

$wpdb->get_results("SELECT tags.*, $wpdb->posts.*, $wpdb->postmeta.*
              FROM tags
              INNER JOIN $wpdb->posts ON tags.charity_id = $wpdb->posts.ID
              INNER JOIN $wpdb->postmeta ON tags.charity_id = $wpdb->postmeta.post_id
              WHERE tags.charity_id = $charity_id"
            );

Answer 2

Wpdb can solve the custom table problems. But tag.charity_id has to be foreign key of the charity.ID