WordPress.org

Forums

Custom sql wordpress query with pagination... (4 posts)

  1. mdudu2
    Member
    Posted 2 years ago #

    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
     WHERE
     post_status = 'publish'
     AND
     ID IN (
                SELECT DISTINCT post_parent
                FROM $wpdb->posts
                WHERE
                  post_parent > 0
                AND
                  post_type = 'attachment'
                AND
                  post_mime_type IN ('image/jpeg', 'image/png')
      )
    ORDER BY post_date DESC LIMIT 0, 12
    ");
    
    foreach($posts as $post) :
    setup_postdata($post);
    ?>
    
    <?php
    $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);
                ?></div>
    
    <?php
    endforeach;
    wp_reset_query();
    ?>

    Thank you

  2. bcworkz
    Member
    Posted 2 years ago #

    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.

  3. mdudu2
    Member
    Posted 2 years ago #

    bcworkz,

    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

  4. keesiemeijer
    moderator
    Posted 2 years ago #

    You can query images with WP_Query like this:

    <?php
    $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>

    See:
    http://codex.wordpress.org/Function_Reference/WP_Query
    http://codex.wordpress.org/Pagination

Topic Closed

This topic has been closed to new replies.

About this Topic