0

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

  • What are you going to do with this query? Save the results as a CSV? Or work directly with the db to port it to your new system. Point being, it would be easier to get the data you need using WP + PHP than a custom SQL query. – TheDeadMedic Oct 26 '16 at 14:17
  • Ideally port to a csv so I could manipulate and import into new custom structure. I may just have to create new structure and have someone go in and manually move the content over. Thanks – Chris Oct 26 '16 at 15:32
  • Do any posts have multiple categories, or is it only ever 1 of the 8? – TheDeadMedic Oct 26 '16 at 15:38
  • I am fairly confident they do not have multiple categories. – Chris Oct 26 '16 at 15:42
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)
        ]);
    }
}
  • Thank you. I will try this later today and accept it as soon as I get it to work. Appreciate it. – Chris Oct 26 '16 at 16:20

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.