WordPress.org

Ready to get started?Download WordPress

Forums

SQL query to extract all WordPress posts with categories (2 posts)

  1. sixfootjames
    Member
    Posted 1 year ago #

    I need to extract all posts from my WordPress DB along with the associated categories and not sure how to write this query.

    SELECT post_title, wpr.object_id, wp_terms.name
    FROM wp_terms
    INNER JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id
    INNER JOIN wp_term_relationships wpr ON wpr.term_taxonomy_id =
    wp_term_taxonomy.term_taxonomy_id
    INNER JOIN wp_posts ON ID = wpr.object_id
    WHERE taxonomy = 'category'
    AND post_type = 'post

    The problem I have with this query is that it returns 1,553 records, when I know it should only be returning 1,343.

    I am assuming this happens because there might be multiple categories per post, so what I would appreciate, is how to show all the posts, of type = post and show each category in its own column.

    Lastly, I don't want to use the WP API because I need to export all this information as a CSV, so that I can re-import it in a products table for WP-eCommerce.

    Many thanks!

  2. vtxyzzy
    Member
    Posted 1 year ago #

    The query for what you want could be very complex if you have very many categories possible for a single post. Here is some code showing one possible way to get the categories into columns:

    $max_terms = 8; // The maximum number of terms to allow
    query_posts( array('posts_per_page' => -1, 'ignore_sticky_posts' => 1) );
    if (have_posts()) {
       for ($post_ndx = 0;$post_ndx < sizeof($wp_query->posts) ;++$post_ndx ) {
          $terms = get_the_terms($wp_query->posts[$post_ndx]->ID, 'category' );
          if ( $terms && ! is_wp_error( $terms ) ) {
             $term_ndx = 0;
             foreach ($terms as $term) {
                ++$term_ndx;
                $label = "cat-$term_ndx";
                if ($term_ndx <= sizeof($terms)) {
                   $wp_query->posts[$post_ndx]->$label = $term->name;
                } else {
                   $wp_query->posts[$post_ndx]->$label = '';
                }
             }
          }
       }
       while (have_posts()) {
          the_post();
          echo '<p>';the_title(); echo '<br /> Post terms: ';
          for ($term_ndx = 1;$term_ndx <= $max_terms ;++$term_ndx ) {
             $label = 'cat-' . $term_ndx;
             echo " $label={$post->$label}";
          }
          echo '</p>';
       }
    }

Topic Closed

This topic has been closed to new replies.

About this Topic