SQL query to extract only the “current” wp_posts?

0 Views

The wp_posts table seems to retain all revisions of the same, ok very similar but presumably different, posts/pages/whatever.

I’m somewhat conversant with SQL but not WordPress. I need to extract just those records which would appear on the public facing site; so just the most recent revision, and not all the superceded rows. Not sure how to filter the fields. Obviously something more complicated than:

Read More
select *
from wp_posts
where post_status in ('publish','revision')
order by post_modified desc

which has ‘duplicates’ and seems to miss some stuff.

Post Views: 0

Related posts

Leave a Reply

2 comments

  1. Rather than constructing query from scratch, it is easier to see what exactly is WordPress querying when API function is used:

    get_posts(array(
             'numberposts' => -1,
         ));

var_dump( $wpdb->last_query );

    Gives following SQL:

    SELECT wp_posts.* FROM wp_posts 
WHERE 1=1 
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish')
ORDER BY wp_posts.post_date DESC

  2. You should use this as WordPress does heavy lifting here:

    $args = array(
    'showposts' => 10,
);
$posts = query_posts();
var_dump($posts);

    You learn more about adding arguments to query_posts http://codex.wordpress.org/Class_Reference/WP_Query

    You should also use the WordPress database class when creating manual MYSQL queries. You can learn more here: http://codex.wordpress.org/Class_Reference/wpdb WordPress also includes a great function for escaping data in queries to keep your operation safe: esc_sql($string);

    You can use the WordPress db class like this:

    global $wpdb;
$results = $wpdb->get_results("SELECT * FROM $wpdb->posts WHERE $wpdb->posts.post_status = 'publish'");

    Using $wpdb->before your the table name prepends the assigned database prefix. This is important because you can have multiple WordPress installations in one database using table prefixes. $wpdb-> will grab the correct prefix context.