WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Adding Pagination to a Custom Select Query (8 posts)

  1. eggroup
    Member
    Posted 2 years ago #

    I'm using a Custom Select Query to return posts sorted by Custom Field value and it's working but it returns all my 350 posts in one page I need to add a pagination to only show 20 posts per page like the rest of my website.

    I used the exact same code shown in this Codex page :
    http://codex.wordpress.org/Displaying_Posts_Using_a_Custom_Select_Query

    <?php
     $querystr = "
        SELECT $wpdb->posts.*
        FROM $wpdb->posts, $wpdb->postmeta
        WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
        AND $wpdb->postmeta.meta_key = 'top'
        AND $wpdb->posts.post_status = 'publish'
        AND $wpdb->posts.post_type = 'post'
        AND $wpdb->posts.post_date < NOW()
        ORDER BY ABS($wpdb->postmeta.meta_value) DESC
     ";
     $pageposts = $wpdb->get_results($querystr, OBJECT);
    ?>
     <?php if ($pageposts): ?>
      <?php global $post; ?>
      <?php foreach ($pageposts as $post): ?>
        <?php setup_postdata($post); ?>
    <div id="post-<?php the_ID(); ?>" class="thumbnail" >

    PS: I have the WP-PageNavi plugin already installed.

  2. Big Bagel
    Member
    Posted 2 years ago #

    Wouldn't it be easier to use WP_Query?

    $args = array(
        'posts_per_page' => 20,
        'order_by' => 'meta_value',
        'meta_key' => 'top',
        'paged' => get_query_var('paged')
    );
    
    $query = new WP_Query( $args );

    The query above should be equivalent to yours but with proper pagination and 20 results per page. If you do use WP_Query make sure to add wp_reset_postdata(); after your wp_pagenavi(); call as recommended by the author of that plugin.

  3. eggroup
    Member
    Posted 2 years ago #

    That's sounds good and interesting. Sorry I'm not so experienced in WP & PHP. Should I replace my whole code with your query or what should I do?

    I really appreciate your help. Thank you!

  4. Big Bagel
    Member
    Posted 2 years ago #

    I'm not that experienced when it comes to doing queries, (I mainly just go go through this codex page). The query I gave:

    <?php
    $args = array(
        'posts_per_page' => 20,
        'order_by' => 'meta_value',
        'meta_key' => 'top',
        'paged' => get_query_var('paged')
    );
    
    $my_query = new WP_Query( $args );
    ?>

    would essentially replace this part:

    <?php
    $querystr = "
        SELECT $wpdb->posts.*
        FROM $wpdb->posts, $wpdb->postmeta
        WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
        AND $wpdb->postmeta.meta_key = 'top'
        AND $wpdb->posts.post_status = 'publish'
        AND $wpdb->posts.post_type = 'post'
        AND $wpdb->posts.post_date < NOW()
        ORDER BY ABS($wpdb->postmeta.meta_value) DESC
     ";
     $pageposts = $wpdb->get_results($querystr, OBJECT);
    ?>

    Then to start the loop you would do something like:

    <?php
    while ( $my_query->have_posts() ) { $my_query->the_post();
    
    ...all the bits to display your posts (the_title(); the_content(); and all those sorts of things)...
    
    }
    ?>

    And then, if you're using that plugin you mentioned, you would probably call wp_reset_postata(); after using it. For example:

    wp_pagenavi( array( 'query' => $my_query ) );
    
    wp_reset_postdata();

    *I'm not familiar with the use of that plugin; I'm just going by what the author of it says here.

    If you want to post your entire query and loop into a pastebin, I could look it over for any obvious weirdness.

  5. eggroup
    Member
    Posted 2 years ago #

    Thank you so much. You just helped me to solve a problem I've been trying to solve for over two days now. Your code did put me on the right path but there is a couple of problems I faced but I managed to solve them and get the code work probably. my meta_key was a numeric value and therefore the posts weren't sorted right and also I didn't get a pagination even after I used your code. Here is the whole code after I edited. Thanks!

    First :

    <?php
    $paged = get_query_var( 'paged' ) ? get_query_var( 'paged' ) : 1;
    $args = array(
        'posts_per_page' => 20,
        'orderby' => meta_value_num,
        'meta_key' => 'top',
    	'order' => 'desc',
        'paged' => $paged
    );
    $my_query = new WP_Query( $args );
    ?>
    <?php if (have_posts()) : ?>
    <?php while ( $my_query->have_posts() ) : $my_query->the_post(); ?>

    Then replace
    echo "<div id=\"pagenavi\">"; wp_pagenavi(); echo"</div>";
    With

    echo "<div id=\"pagenavi\">"; wp_pagenavi( array( 'query' => $my_query ) ); echo"</div>";
    wp_reset_postdata();

    This will sort all your posts desc by custom field called "top" and you will 20 posts per page.

    I can't thank you enough for putting an end for my problem and believe me while I'm searching I came across tens of posts regarding this problem which was always missing something or a bit different than what I need.

    Finally, Thank you! :)

  6. Big Bagel
    Member
    Posted 2 years ago #

    I don't believe you need the 'order' => 'desc' argument since that's what it automatically defaults to. You used 'orderby' correctly, except for the single quotes around 'meta_value_num'. As far as the pagination, I always have problems with it. Try it with 'page' instead, like this:

    $args = array(
        'posts_per_page' => 20,
        'orderby' => 'meta_value_num',
        'meta_key' => 'top',
        'paged' => get_query_var( 'page' )
    );

    There's another way to try if it still gives you problems with the order. If the pagination doesn't work with get_query_var( 'paged' ) or get_query_var( 'page' ), I'll look for something else to try; I really hope it's not just a problem with the plugin.

  7. eggroup
    Member
    Posted 2 years ago #

    Thanks for the tip. The current code I pasted above did make the pagination work just fine. I just needed to edit wp_pagenavi() as I posted above.

    You're right. I don't need 'order' => 'desc'. I removed it and everything is still working just fine.

    Thanks again! :)

  8. yeasin33
    Member
    Posted 2 years ago #

    What if I want to paginate Category lists. Maybe from all the category or the child categories of a parent category?

Topic Closed

This topic has been closed to new replies.

About this Topic