Support » Plugins » Hacks » Custom sql wordpress query with pagination…

  • Hello,

    I’ve done a custom sql query for my blog. What the code does is to retrive the latest image from a post that has a gallery. In this way i can display latest … les’t say 12 posts that have a gallery- the code actualy displays the first image that is attached to the post.

    Problem is that i cannot create a next prev functionality for this and i really need this

    Here is the code:

    <?php wp_reset_query();
     global $wpdb;
     $posts = $wpdb->get_results
     SELECT *
     FROM $wpdb->posts
     post_status = 'publish'
     ID IN (
                SELECT DISTINCT post_parent
                FROM $wpdb->posts
                  post_parent > 0
                  post_type = 'attachment'
                  post_mime_type IN ('image/jpeg', 'image/png')
    ORDER BY post_date DESC LIMIT 0, 12
    foreach($posts as $post) :
    $images = get_children(array(
      'post_parent' => get_the_id(),
      'post_type' => 'attachment',
      'post_mime_type' => 'image',
      'orderby' => 'menu_order',
      'order' => 'ASC'
    $ids = array_keys($images);
       <div style="height:132px; width:132px; float:left; margin-right:1px; margin-top:1px; overflow:hidden;" ><?php
      echo the_attachment_link($ids[0],false, false, true);

    Thank you

Viewing 3 replies - 1 through 3 (of 3 total)
  • Moderator bcworkz


    Your next/prev links should include a parameter for which page it is requesting. Your script will grab this value out of $_GET and calculate new LIMIT parameters for the query that correspond to the posts that appear on the page.

    If you use WP_Query instead of $wpdb, the paging is pretty much handled for you. One problem with this approach is I do not think WP_Query accepts post_mime_type arguments, but you can insert it into the query by using the ‘posts_where_paged’ filter.


    thank you for your help,
    as i understand, is complicated to use $_GET in wordpress because the cms does’t handle very well this type of request .. get and post .

    regarding the other approach … can you please please help me a little bit with the code? appreciate your help

    thank you

    Moderator keesiemeijer



    You can query images with WP_Query like this:

    $args = array(
    	'post_mime_type' => 'image',
    	'post_status' => 'inherit', // important
    	'post_type' => 'attachment',
    $image_posts = new WP_Query( $args );

    Now you have to change the loop for your query:

    <!-- the loop -->
    <?php if ( $image_posts->have_posts() ) : while ( $image_posts->have_posts() ) : $image_posts->the_post(); ?>
    <!-- your loop code here -->
    <?php endwhile; ?><!-- end of loop -->
    <!-- pagination functions here -->
    <?php wp_reset_postdata(); ?>
    <?php else:  ?>
    <p><?php _e('Sorry, no posts matched your criteria.'); ?></p>


Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Custom sql wordpress query with pagination…’ is closed to new replies.