WordPress.org

Forums

[resolved] how does offset works on Pagination? (get_results) (2 posts)

  1. kizmark
    Member
    Posted 1 year ago #

    I followed this link and created my own custom query with pagination.. but I don't really understand how the offset works, Pagination with custom SQL query

    the pagination does not work well. and I'm getting zero value for offset.

    function spiciest(){
    global $wpdb, $paged, $max_num_pages;
    
    $paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
    $post_per_page = intval(get_query_var('posts_per_page')); //6
    $offset = ($paged - 1)*$post_per_page;
    /* Custom sql here. I left out the important bits and deleted the body
     as it will be specific when you have your own. */
    $sql = "
        SELECT DISTINCT * FROM $wpdb->posts
        INNER JOIN (SELECT *, SUBSTRING(name, 6) as 'post_ID',
        votes_up  AS votes_balance,
        votes_up + votes_down AS votes_total
        FROM thumbsup_items) AS thumbsup
        ON $wpdb->posts.ID = thumbsup.post_ID
        WHERE $wpdb->posts.post_status = 'publish'
        AND $wpdb->posts.post_type = 'post'
        AND $wpdb->posts.post_password = ''
        ORDER BY votes_up DESC, votes_balance DESC
        LIMIT ".$offset.", ".$post_per_page."; ";
    
    $sql_result = $wpdb->get_results( $sql, OBJECT);
    
    /* Determine the total of results found to calculate the max_num_pages
     for next_posts_link navigation */
    $sql_posts_total = $wpdb->get_var( "SELECT FOUND_ROWS();" );
    $max_num_pages = ceil($sql_posts_total / $post_per_page);
    
    print_r("offset ". $offset."\n") ;
    print_r("\n"."sql_posts_total ". $sql_posts_total."\n") ;
    print_r("\n"."max_num_pages ". $max_num_pages."\n") ;
    return $sql_result;
    }

    Please see it live.. I have printed the vlues.. http://goo.gl/fZTck It should have 7 pages with a total of 39 entries.

  2. kizmark
    Member
    Posted 1 year ago #

    The problem here is the LIMIT, it'll just count the first page and not the entire query.

    I had solved it by providing a secondary SQL query for counting the max pages. thanks for my friends for this tip.

    here's the complete code.

    **function.php**

    function spiciest(){
        global $wpdb, $paged, $max_num_pages;
    
        $paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
        $post_per_page = intval(get_query_var('posts_per_page')); //6
    	$offset = ($paged - 1)*$post_per_page;
    
    	// query normal post
    	$query_spicy = "
            SELECT DISTINCT * FROM $wpdb->posts
    		INNER JOIN (SELECT *, SUBSTRING(name, 6) as 'post_ID',
    		votes_up  AS votes_balance,
    		votes_up + votes_down AS votes_total
    		FROM thumbsup_items) AS thumbsup
    		ON $wpdb->posts.ID = thumbsup.post_ID
    		WHERE $wpdb->posts.post_status = 'publish'
    		AND $wpdb->posts.post_type = 'post'
    		AND $wpdb->posts.post_password = ''
    		ORDER BY votes_up DESC, votes_balance DESC";
    
    	//query the posts with pagination
    	$spicy = $query_spicy . " LIMIT ".$offset.", ".$post_per_page."; ";
    
        $spicy_results = $wpdb->get_results( $spicy, OBJECT);
    
    	// run query to count the result later
    	$total_result = $wpdb->get_results( $query_spicy, OBJECT);
    
    	$total_spicy_post = count($total_result);
        $max_num_pages = ceil($total_spicy_post / $post_per_page);
    
        return $spicy_results;
        }

    **TEMPLATE CODES:**

    <?php
         $spiciest = spiciest();
    
         if ($spiciest):
    		global $post;
    		foreach ($spiciest as $post) :
    			setup_postdata($post);
        ?>
    
        /**** PUT TEMPLATE TAGS HERE *****/
    
        <?php
     	    endforeach;
    	endif;
    
        ?>

    and then the PAGINATION here, please note of the TOTAL in array.

    `global $wp_rewrite, $wp_query, $max_page, $page;
    $wp_query->query_vars['paged'] > 1 ? $current = $wp_query->query_vars['paged'] : $current = 1;

    $pagination = array(
    'base' => @add_query_arg('page','%#%'),
    'format' => '',
    'total' => $max_num_pages,
    'current' => $current,
    'prev_text' => __('PREV'),
    'next_text' => __('NEXT'),
    'end_size' => 1,
    'mid_size' => 2,
    'show_all' => false,
    'type' => 'list'
    );

    if ( $wp_rewrite->using_permalinks() )
    $pagination['base'] = user_trailingslashit( trailingslashit( remove_query_arg( 's', get_pagenum_link( 1 ) ) ) . 'page/%#%/', 'paged' );

    if ( !empty( $wp_query->query_vars['s'] ) )
    $pagination['add_args'] = array( 's' => get_query_var( 's' ) );

    echo paginate_links( $pagination );
    `

Topic Closed

This topic has been closed to new replies.

About this Topic