Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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:

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

which has 'duplicates' and seems to miss some stuff.

share|improve this question
1  
Do you need to do this via SQL? Why not WordPress functions? –  Rarst Sep 14 '11 at 10:11
    
+1 on @Rarst. Plus: If you get duplicates, use DISTINCT. –  kaiser Sep 14 '11 at 11:02

2 Answers 2

up vote 6 down vote accepted

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
share|improve this answer
    
+1 Didn't know about getting the query! –  Elliott Sep 14 '11 at 12:12
    
@Elliott yeah, I wish someone had told me about that trick couple years earlier than I learned it :) –  Rarst Sep 14 '11 at 12:15
    
You should always use wpdb to get the current table prefix. –  Brian Fegter Sep 14 '11 at 13:16
    
@Brian Fegter this query is generated by WP and so already has correct prefix in place. This approach will always give query, specific to installation. –  Rarst Sep 14 '11 at 13:19
    
@Rarst - DOH :) –  Brian Fegter Sep 14 '11 at 13:20

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.