How to convert sql statement from pre-2.3 database structure

  • Resolved devpaq

    (@devpaq)


    14 years, 3 months ago

    I am upgrading a wordpress 2.2 to 2.9.1 and need to update some of my custom coding to reflect the new database structure. Can someone help me understand the new structure? I am trying to pull posts that are only from a specific category. In the past I would do something like this…

    SELECT wp_posts.*, wp_post2cat.* FROM wp_posts INNER JOIN wp_post2cat ON wp_posts.ID = wp_post2cat.post_id WHERE wp_posts.post_status = ‘publish’ AND wp_post2cat.category_id = ‘3’ ORDER BY post_date DESC

    How would I accomplish this in the current version.

    Thanks.

Viewing 2 replies - 1 through 2 (of 2 total)
  • MichaelH

    (@michaelh)

    14 years, 3 months ago

    Better to use something like this:

    <?php
    $args=array(
      'cat' => 3,
      'post_type' => 'post',
      'post_status' => 'publish',
      'posts_per_page' => -1,
      'caller_get_posts'=> 1
      );
    $my_query = null;
    $my_query = new WP_Query($args);
    if( $my_query->have_posts() ) {
      echo 'List of Posts';
      while ($my_query->have_posts()) : $my_query->the_post(); ?>
      <p><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a></p>
       <?php
      endwhile;
    }
wp_reset_query();  // Restore global post data stomped by the_post().
?>

    But just in case…that query above results in this SQL

    SELECT   wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)  WHERE 1=1  AND wp_term_taxonomy.taxonomy = 'category'  AND wp_term_taxonomy.term_id IN ('3')  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC

    Thread Starter devpaq

    (@devpaq)

    14 years, 3 months ago

    Thanks. This is extremely helpful. I think I can easily apply this to all the sql statements I am using.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘How to convert sql statement from pre-2.3 database structure’ is closed to new replies.

Tags