Extract wordpress posts content and category content

Question

I got client that built a learning course with wordpress. The learning course has 8 categories and within each category there are 20-50 posts assigned in order.

I am trying to figure out a way to extract all of the posts in order from the database only from the 8 specific categories. I do not want all of the pages and post that are not apart of the learning course as the course is only a portion of the site.

The end goal is to have the ability to take the content and migrate it to a custom built solution for the course.

What I really need to pull would be post_title, post_slug, post_content, category_id, order_id (doubt that is actual table row names but get the idea of what i am trying to pull).

I looked around and saw the relationship to get all of the posts with category relationships similar to this.

SELECT p.*, t.*
FROM wp_posts p
LEFT JOIN wp_term_relationships rel ON rel.object_id = p.ID
LEFT JOIN wp_term_taxonomy tax ON tax.term_taxonomy_id = rel.term_taxonomy_id
LEFT JOIN wp_terms t ON t.term_id = tax.term_id

But not sure how to narrow it down as that is grabbing everything. I am novice at wordpress and go start digging into the docs to see what I can figure out but figure it will take me a long time to get what I need. I figure there might be someone out there who know a simple tweak to this to help and save me a lot of time. Appreciate any help. Thanks

Answer 1

This'll output a CSV export of:

1. Post title
2. Post slug
3. Post content
4. Order ID
5. Category ID

Pop this in a file in the root of your WordPress install (e.g. export.php), make sure you change the $category_ids with the IDs of all the specific categories.

<?php

require './wp-load.php';

header( 'Content-Type: text/csv; charset=' . get_bloginfo( 'charset' ) );
header( 'Content-Disposition: attachment; filename=posts.csv' );

$category_tax = 'category'; // Change if different category taxonomy
$category_ids = [ 1, 2, 3, 4, 5, 6, 7, 8 ]; // The 8 specific category IDs

$query = new WP_Query;
$paged = 1;

$fopen = fopen( 'php://output', 'w' );

while ( true ) {
    $query->query([
        'post_type'      => 'post', // Change if different post type
        'post_status'    => [ 'publish', 'private' ],
        'posts_per_page' => 50,
        'no_found_rows'  => true,
        'paged'          => $paged++,
        'tax_query'      => [[
            'taxonomy' => $category_tax,
            'terms'    => $category_ids,
        ]],
    ]);

    if ( ! $query->have_posts() )
        break;

    foreach ( $query->posts as $post ) {
        $cats = [];

        if ( $terms = get_the_terms( $post, $category_tax ) ) {
            foreach ( $terms as $term ) {
                if ( in_array( $term->term_id, $category_ids ) )
                    $cats[] = $term->term_id;
            }
        }

        fputcsv( $fopen, [
            $post->post_title,
            $post->post_name, // Slug - use get_page_uri( $post ) instead if posts are hierarchical
            $post->post_content, // Use apply_filters( 'the_content', $post->post_content ) if you want HTML-rendered frontend output
            $post->menu_order, // Order ID
            implode( ':', $cats ), // Colon-separated list of the category IDs (if more than one)
        ]);
    }
}