Support » Fixing WordPress » 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,
    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 =
    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) {
                $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()) {
          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.