WordPress.org

1. pixelnate
   Member
   Posted 1 year 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 1 year 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
   Code Wrangler
   Posted 1 year 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 1 year 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 11 months ago #

   Hello @pixelnate,

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

6. pixelnate
   Member
   Posted 11 months 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 3 months 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.