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.

Here is a simple example of what I am wanting to figure out.

Say I have WordPress set to show 5 posts per page. And say I there is a post 17 posts back chronologically.

The formula would be 17 / 5 = 3.4. Round up 3.4 to a whole page of 4 and that post 17 posts back is on the 4th index page.

Now... How to do this programmatically...

The number of posts on each index page is determined by the get_option( 'posts_per_page' ); option.

If all I have is the post's id, how do I figure out how many posts back it is chronologically?

My head first went to WP_Query, but it is not a suitable option. The blog this is for has thousands of posts and it runs out of memory before I can get anywhere to determine where the post is at.

Does anyone have an clever suggestions?

share|improve this question
1  
Can I ask why you need to figure this out? –  Tom J Nowell Mar 28 '13 at 21:49
    
I wrote my own Redis caching plugin and this was one of the missing pieces. I needed to clear the post's corresponding index page when a comment was left so the comment count on the index page could be recached to show an accurate comment count. –  Michael Novotny May 9 '13 at 4:31

2 Answers 2

There's no way to do it directly but there's a way to determine the position of a particular row in a mysql query. You can find out how to do it here- http://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by

Once you have the position you can divide it with the value from get_option('posts_per_page') to determine the correct page number.

share|improve this answer
up vote 0 down vote accepted

Thanks to the suggestion of Mridul Aggarwal, I came up with the following query, which returns a post's chronological position for a given post id.

The post id, post title, and post date can be removed and are just used for debugging / verifying the results.

SELECT
    *
FROM (
        SELECT
            ID as id
        , post_date
        , post_title
        , @rownum := @rownum + 1 AS position
        FROM
            wp_posts
        , (SELECT @rownum := 0) AS rownum
        WHERE
            post_type = 'post'
        AND post_status = 'publish'
        ORDER BY
            post_date DESC
    ) as post_position
WHERE
    id = 12345 /* REPLACE THIS WITH THE POST ID YOU WANT */

Make sure you replace the 12345 for the post id with the post id you want to find the position for.

You can run this query using $wpdb->get_results. More details on $wpdb can be found in the Codex.

With the query results, I can then calculate what paginated index page the post is on using the PHP logic below.

// Get posts per page option.
$posts_per_page = get_option( 'posts_per_page' );

// Calculate what index page a post is on.
$page_index = ceil( $position / $posts_per_page );

And that's it! The $page_index variable will hold the magical paginated index page number!

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.