How to find what index page a post is on?

2 comments

1. 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- https://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.

   Log in to Reply

2. 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!

   Log in to Reply