WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] How can I paginate this monter? (7 posts)

  1. pixelnate
    Member
    Posted 3 years ago #

    I needed to put together this custom query to display events on a client's website. Everything is working perfectly, but I haven't a clue how to paginate the results. Could somebody please help a fellow out with this? Any pointers are greatly appreciated.

    $query = "
    SELECT *,
    m1.meta_value AS start_date,
    IF( m2.meta_value=0, m1.meta_value, m2.meta_value ) as last_day
    FROM {$wpdb->prefix}posts
    INNER JOIN {$wpdb->prefix}postmeta m1
    ON ( {$wpdb->prefix}posts.ID = m1.post_id )
    INNER JOIN {$wpdb->prefix}postmeta m2
    ON ( {$wpdb->prefix}posts.ID = m2.post_id )
    WHERE {$wpdb->prefix}posts.post_type = 'events'
    AND {$wpdb->prefix}posts.post_status = 'publish'
    AND ( m1.meta_key = '_startdate' AND m1.meta_value)
    AND ( IF( m2.meta_value=0, m1.meta_value, m2.meta_value ) > DATE_FORMAT( NOW(), '%Y%m%d%H%i') )
    AND ( m2.meta_key = '_enddate' AND m2.meta_value >= '0' )
    GROUP BY {$wpdb->prefix}posts.ID
    ORDER BY start_date
    ASC;
    ";

    $events = $wpdb->get_results( $query, OBJECT );

    if ( $events ) : foreach ( $events as $post ) : setup_postdata( $post );
    ...

  2. pixelnate
    Member
    Posted 3 years ago #

    Crickets, huh? Perhaps the SQL is scaring people away from answering. Really this boils down to pagination with a custom SQL query. Still need some help figuring that out, though. Thx.

  3. Tim Moore
    Moderator
    Posted 3 years ago #

    Try using something like this: http://www.petefreitag.com/item/451.cfm

    You'd set up the SQL as in that article and then pass a $_GET variable with what page you are on. You'd increase the offset based on the page number.

  4. pixelnate
    Member
    Posted 3 years ago #

    As it turns out, there was an answer in this forum that worked perfectly. And it works with both the default next/previous links as well as the Pagenavi plugin.

    http://wordpress.org/support/topic/custom-select-query-w-pagination?replies=11

  5. dashaluna
    Member
    Posted 3 years ago #

    Hello @pixelnate,

    Which exactly solution did you use? there are couple in that post.
    Thanks.

  6. pixelnate
    Member
    Posted 3 years ago #

    This is my full code. It hits the db twice, once to find the total number of posts and once to put them in an array for use in the loop. That could probably be refactored into in a single query, but I am not the best php progger and there are hotter issues on my plate at the moment. I hope it helps, or at least gets you closer to solving your issue.

    $total = "
    	SELECT *,
    	m1.meta_value AS start_date,
    	IF( m2.meta_value=0, m1.meta_value, m2.meta_value ) as last_day
    	FROM {$wpdb->prefix}posts
    	INNER JOIN {$wpdb->prefix}postmeta m1
    	  ON ( {$wpdb->prefix}posts.ID = m1.post_id )
    	INNER JOIN {$wpdb->prefix}postmeta m2
    	  ON ( {$wpdb->prefix}posts.ID = m2.post_id )
    	WHERE {$wpdb->prefix}posts.post_type = 'events'
    	AND {$wpdb->prefix}posts.post_status = 'publish'
    	AND ( m1.meta_key = '_startdate' AND m1.meta_value)
    	AND ( IF( m2.meta_value=0, m1.meta_value, m2.meta_value ) > DATE_FORMAT( NOW(), '%Y%m%d%H%i') )
    	AND ( m2.meta_key = '_enddate' AND m2.meta_value >= '0' )
    	GROUP BY {$wpdb->prefix}posts.ID
    	ORDER BY start_date
    	ASC;
    ";
    
    $totalevents = $wpdb->get_results( $total, OBJECT );
    
    $ppp = intval( get_query_var( 'posts_per_page' ) );
    
    $wp_query->found_posts = count( $totalevents );
    
    $wp_query->max_num_pages = ceil( $wp_query->found_posts/$ppp );
    
    $on_page = intval( get_query_var( 'paged' ) );
    
    if( $on_page == 0 ){ $on_page = 1; }
    
    $offset = ( $on_page-1 ) * $ppp;
    
    $wp_query->request = "
    	SELECT *,
    	m1.meta_value AS start_date,
    	IF( m2.meta_value=0, m1.meta_value, m2.meta_value ) as last_day
    	FROM {$wpdb->prefix}posts
    	INNER JOIN {$wpdb->prefix}postmeta m1
    	  ON ( {$wpdb->prefix}posts.ID = m1.post_id )
    	INNER JOIN {$wpdb->prefix}postmeta m2
    	  ON ( {$wpdb->prefix}posts.ID = m2.post_id )
    	WHERE {$wpdb->prefix}posts.post_type = 'events'
    	AND {$wpdb->prefix}posts.post_status = 'publish'
    	AND ( m1.meta_key = '_startdate' AND m1.meta_value)
    	AND ( IF( m2.meta_value=0, m1.meta_value, m2.meta_value ) > DATE_FORMAT( NOW(), '%Y%m%d%H%i') )
    	AND ( m2.meta_key = '_enddate' AND m2.meta_value >= '0' )
    	GROUP BY {$wpdb->prefix}posts.ID
    	ORDER BY start_date
    	ASC
    	LIMIT $ppp
    	OFFSET $offset;
    ";
    
    $events = $wpdb->get_results($wp_query->request, OBJECT);
    
    if ( $events ) : foreach ( $events as $post ) : setup_postdata( $post );
    
    	// YOUR LOOP GOES HERE
    
    	endforeach;
    endif; ?>
  7. doiches
    Member
    Posted 2 years ago #

    Hi everyone in the forum, close to this topic i am trying a search unsing the faceted search plugin with categories, and as pixelnate i am getting more posts than the ones i would like to display in one page. I have tryed to re-adapt the facetedsearch_smart_query function but i am getting lost cuz no results are diplayed or not paged. Could any one give me a clue? Thanks in advanced:
    Here is the function code modified:

    function facetedsearch_smart_query($effective_array, $descendant_array)
    {
    	global $wpdb;
    
    	$query = 'SELECT DISTINCT * ';
    	$queryfrom = 'FROM '.$wpdb->posts;
    	$querywhere = " WHERE post_type = 'post' AND post_status = 'publish' ";
    
    	$siblings = $_POST['taxonomy'];
    	$indexes = array();	
    
    	foreach ($siblings as $clave => $valor)
    	{
    		if ($valor == 0)
    		{
    		array_push($indexes, $clave);
    		}
    	}
    
    	foreach ($indexes as $clave => $valor)
    	{
    		//generar los arrays dinamicamente
    		${$indexes.$valor} = array();
    	}
    
    	foreach ($effective_array as $clave => $valorefectivo)
    	{
    		$cat_name = get_category_parents($valorefectivo,false,' » ');
    		$category->name =  substr($cat_name ,0, strpos($cat_name,' » ') );
    		$id= get_term_by("name",$category->name, "category");
    		$id = $id->term_id;
    		array_push(${$indexes.$id}, $valorefectivo);
    	}
    
    	foreach ($indexes as $clave => $valor)
    	{
    		if ( count(${$indexes.$valor}) > 0 ){
    			$querywhere .=  'AND (';
    			$querywhere .=  facetedsearch_simple_query(${$indexes.$valor},null,"OR");
    			$querywhere .= ')';
    		}
    	}
    	$querywhere .= ' ORDER BY post_date DESC';
    	$query = $query.$queryfrom.$querywhere;	
    
    	$totalevents = $wpdb->get_results( $query, OBJECT );
    
    	$ppp = intval( get_query_var( 'posts_per_page' ) );
    	$wp_query->found_posts = count( $totalevents );
    	$wp_query->max_num_pages = ceil( $wp_query->found_posts/$ppp );
    	$on_page = intval( get_query_var( 'paged' ) );
    
    	if( $on_page == 0 ) {  $on_page = 1; }
    
    	$offset = ( $on_page-1 ) * $ppp;
    
    	$wp_query->request = $query." LIMIT ".$ppp. " OFFSET ". $offset;
    	$pageposts = $wpdb->get_results($wp_query->request, OBJECT);

Topic Closed

This topic has been closed to new replies.

About this Topic