SQL query to extract all WordPress posts with categories

  • 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!

Viewing 1 replies (of 1 total)

  • 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>';
   }
}
Viewing 1 replies (of 1 total)
  • The topic ‘SQL query to extract all WordPress posts with categories’ is closed to new replies.