Skip to:
Content
Pages
Categories
Search
Top
Bottom

bb_query: get topics started within a date range?


  • mrhoratio
    Member

    @mrhoratio

    How do I use bb_query to fetch all topics that were started within a given date range?

    Here’s an example found an example of a bb_query code on the bbPress blog:

    $topic_query = new BB_Query( ‘topic’,

    array(

    ‘topic_author’ => ‘mdawaffe’,

    ‘started’ => ’2007-06′,

    ‘tag’ => ‘bbpress’,

    ‘order_by’ => ‘topic_start_time’

    )

    );

    $topic_query->results; // Here’s the array of topics the query returned.

    This shows how to get topics that where started on June 2007. But what if I wanted to get all topics that were started between June 2, 2007 10:30:49 PM and February 12, 2008 9:02:32 am?

    I think I may be missing something totally obvious. Any suggestions? I

Viewing 4 replies - 1 through 4 (of 4 total)

  • mrhoratio
    Member

    @mrhoratio

    I did some more digging, and it looks a way to do is to not use BB_query at all, but to use $bbdb->get_results instead:

    $startdate=”20070602103049″;

    $enddate=”20080212090232″;

    $topics = $bbdb->get_results(“SELECT * FROM $bbdb->topics WHERE topic_start_time BETWEEN $startdate AND $enddate ORDER BY topic_start_time DESC”);


    Sam Bauers
    Participant

    @sambauers

    BB_Query can’t do this at the moment as far as I can tell. It probably should though.

    I’ll make a trac ticket to suggest this.

    At the moment you could do two BB_Queries and intersect their results. But that’s pretty poor.

    Just be sure if you do use a direct query that you prepare it first. e.g.:

    $startdate="20070602103049";
    $enddate="20080212090232";

    $query = $bbdb->prepare("SELECT * FROM %s WHERE topic_start_time BETWEEN %s AND %s ORDER BY topic_start_time DESC", $bbdb->topics, $startdate, $enddate);
    $topics = $bbdb->get_results($query);


    Sam Bauers
    Participant

    @sambauers


    mrhoratio
    Member

    @mrhoratio

    Hey Sam, thanks for making a ticket for this. I was able to query for topics within a date from directly from WordPress as well. Here’s something I wrote to merge “super sticky” bbPress topics into the WordPress loop. It’s a bit of hack, but it brings the “Promote to Front Page” functionality of Drupal into WordPress/bbPress:

    <?php get_header(); ?>

    <?php

    //Put WordPress posts into an array
    $wp_posts = $posts;

    if (!is_single()){

    //Retrieve first post of previous page (we need this post's date to query bbPress topics
    $offset=$paged*$posts_per_page+$posts_per_page;
    $first_post_of_previous_page = get_posts('numberposts=1&offset='.$offset);

    //First set the start and end dates to limit the query to the bbPress table
    $startdate=(date('YmdHis',strtotime($first_post_of_previous_page[0]->post_date_gmt)));

    //If it's the most recent page, set end date to today
    if ($paged){
    $enddate=(date('YmdHis',strtotime($wp_posts[0]->post_date_gmt)));
    } else {
    $enddate=gmdate('YmdHis');
    }

    //Retrieve "Super Sticky" topics from bbPress tables
    //This assumes your bbPress and WordPress tables are in the same database
    $bb_topics = $wpdb->get_results("SELECT * FROM bb_topics WHERE topic_sticky = 2 AND topic_start_time BETWEEN $startdate AND $enddate ORDER BY topic_start_time DESC");

    //Map bbPress topics to WordPress posts structure
    foreach($bb_topics as $bb_topic){
    $bb_first_post = $wpdb->get_results("SELECT post_text FROM bb_posts WHERE post_position = '1' AND topic_id = $bb_topic->topic_id", ARRAY_A);
    $bb_post->ID = "forum_topic_".$bb_topic->topic_id;
    $bb_post->post_author = $bb_topic->topic_poster;
    $bb_post->post_date = $bb_topic->topic_start_time;
    $bb_post->post_content = $bb_first_post[0][post_text];
    $bb_post->post_title = $bb_topic->topic_title;
    $bb_post->post_status = "publish";
    $bb_post->comment_status = "open";
    $bb_post->ping_status = $bb_topic->post_id;
    $bb_post->post_name = "forums/topic/".$bb_topic->topic_slug;
    $bb_post->post_type = "post";
    $bb_post->comment_count = $bb_topic->topic_posts-1;

    //add bbPress topic to WordPress posts array
    $wp_posts[] = $bb_post;

    };

    //Create function to sort array of posts by date
    function compare($x, $y){
    if ( $x->post_date == $y->post_date )
    return 0;
    else if ( $x->post_date < $y->post_date )
    return 1;
    else
    return -1;
    }

    //Sort array
    usort($wp_posts,'compare');
    }

    ?>

    <div id="content-box" class="span-8">

    <div id="content-area" class="clearfix">

    <?php if ($wp_posts): ?>
    <?php foreach ($wp_posts as $post): ?>
    <?php setup_postdata($post); ?>

    <div class="entry">

    <div class="entry-header clearfix">

    <div class="info span-2">
    <a href="<?php the_permalink(); ?>#comments" class="comment-activity"><?php comments_number('<strong>Post comment</strong>', '<strong>1</strong> Comment', '<strong>%</strong> Comments' );?></a>
    </div><!-- end info -->

    <div class="content span-6 last">
    <h2><a href="<?php the_permalink(); ?>" rel="bookmark" title="Permanent Link to <?php the_title(); ?>"><?php the_title(); ?></a></h2>
    </div><!-- end content -->

    </div><!-- end entry-header -->

    <div class="entry-content clearfix">

    <div class="info span-2 clearfix">
    <p><strong><?php the_author() ?></strong><br />
    <?php the_time('M j, Y'); ?>
    </p>
    <p class="share-this"><?php akst_share_link(); ?></p>

    <p class="post-comment"><a href="<?php the_permalink(); ?>#respond">Comment</a></p>

    </div><!-- end info -->

    <div class="content span-6 last clearfix">

    <?php the_content('Click to continue'); ?>

    <p class="entry-tags"><?php the_tags('<strong>Posted in: </strong>', ', ', ''); ?> </p>

    </div><!-- end content -->

    </div><!-- end entry-content -->

    </div><!-- end entry -->

    <?php endforeach; ?>

    <?php include (TEMPLATEPATH . '/navigation.php'); ?>

    <?php else : ?>

    <h2 class="page_header center">Not Found</h2>
    <div class="entry">
    <p class="center">Sorry, but you are looking for something that isn't here.</p>
    <?php include (TEMPLATEPATH . "/searchform.php"); ?>
    </div>

    <?php endif; ?>

    </div><!-- end content-area-->

    </div><!-- end content-box -->

    <?php get_sidebar(); ?>

    <?php get_footer(); ?>

Viewing 4 replies - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.