0

I want all posts of type 'ajde_events' as a row each with a column for some basics from the post table and some ( not all ) associated postmeta values. I have cribbed the following from this post How to display multiple Post meta_key/meta_values by SQL query but as the op says in a comment it only returns a single row ( which seems to be the row where the location is a string starting with "Z" which I suspect is the "Highest" i.e. maximum string.

SELECT p.ID, p.post_title,
MAX(IF(PM.meta_key = 'evcal_location', PM.meta_value, NULL)) AS location,
MAX(IF(PM.meta_key = 'evcal_srow', PM.meta_value, NULL)) AS start_time
FROM wp_posts AS p
LEFT JOIN wp_postmeta AS pm ON p.ID = pm.post_id
WHERE post_type = 'ajde_events' AND post_status = 'publish'
ORDER BY p.ID

How do I get a row for each relevant post?

  • Is there a specific reason you're looking for a MySQL query? If you're just wanting to get posts with their postmeta values and loop through them to display, you can use built-in WP functions instead. – WebElaine Oct 8 '19 at 18:12
  • @WebElaine I'm trying to create a .csv file for import into another CMS. – elb Oct 9 '19 at 9:21
  • 1
    @WebElaine You can still use the built in WP_Query to do this, it will also be much simpler than messing about with MySQL. Let the PHP do the work and build the csv for you. – Drmzindec Oct 9 '19 at 9:31
0

Using built-in WP functions to get the data is usually a better approach. It can be more performant, and is preferable security-wise because you're using prebuilt queries instead of building your own.

In a plugin, you can use something like:

<?php
// Get all published CPTs
$allEvents = get_posts(array(
    'post_type' => 'ajde_events',
    'post_status' => 'publish',
    'numberposts' => -1
));
// If any were found
if($allEvents) {
    // Open/create your csv (your code here)
    // Loop through all CPTs and grab postmeta
    foreach($allEvents as $event) {
        $meta = get_post_meta($event);
        // Save the postmeta with the post object
        $event->evcal_location = $meta['evcal_location'];
        $event->evcal_srow = $meta['evcal_srow'];
        // Add the full post object, with postmeta, to an array
        $fullData[] = $event;
    }
    // Reset the query
    wp_reset_postdata();
    // Save to the csv and close it (your code here)
}
?>

Where to put this code depends on how you want the plugin to work. Perhaps you want to create an admin screen where a user can manually press a button to download the CSV, or perhaps you want a cron job that automatically builds it every day and emails the file.

| improve this answer | |
0

The query I posted just needs the ORDER BY clause changing to GROUP BY and it then does what I wanted.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.