WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Is there a way to do this w/ query_posts? (7 posts)

  1. ancawonka
    Member
    Posted 4 years ago #

    I'm trying to build an event management system that uses post custom fields to hold event start dates and event end dates. I want to extract all events that take place during the current week. (where the start and/or end date fall during this week).

    Here's the DB query I have to do this:

    $query = "select post_id, meta_id, 'start' as meta_key, meta_value from wp_5_postmeta where meta_key='event_start_date' and '$weekstart' <= meta_value and meta_value <= '$weekend' union select post_id, meta_id, 'end' as meta_key, meta_value from wp_5_postmeta where meta_key='event_end_date' and '$weekstart' <= meta_value and meta_value <= '$weekend'";

    Basically, I'd like the union of:

    all posts where the value of "event_start_date" are between the first day of the week ($weekstart) and the end ($weekend)
    AND
    all posts where the value of "event_end_date" are between the first day of the week ($weekstart) and the end ($weekend).

    This query gives me the correct post_id's, but now I'd like to display these posts, and I'm not sure I want to get them one by one from the DB.

    I'm wondering if it's possible to do a "between" query using query_posts (in which case I could just query twice to get the full list of posts to display, and then de-dupe that list before showing it), or if I can query on two different custom fields...

    Sorry if this post is a little rambling, I'm just trying to think my way through this.

    Cheers,

    Anca.

  2. MichaelH
    Member
    Posted 4 years ago #

    Didn't test this "much", but try:

    <?php
    $start_key = 'event_start_date';
    $end_key = 'event_end_date';
    $weekstart = '20100131'; //Jan 31, 2010
    $weekend = '20100206'; //Feb 6, 2010
    
    $postids = $wpdb->get_col($wpdb->prepare("
    SELECT DISTINCT post_id
    FROM        $wpdb->postmeta
    WHERE       (meta_key = %s OR meta_key=%s)
                AND (meta_value >= %s AND meta_value <= %s)",$start_key, $end_key, $weekstart, $weekend)); 
    
    if ($postids) {
      $args=array(
        'post__in' => $postids,
        'post_type' => 'post',
        'post_status' => 'publish',
        'posts_per_page' => -1,
        'caller_get_posts'=> 1
      );
      $my_query = null;
      $my_query = new WP_Query($args);
      if( $my_query->have_posts() ) {
        echo 'List of Posts';
        while ($my_query->have_posts()) : $my_query->the_post(); ?>
          <p><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a></p>
          <?php
        endwhile;
      }
      wp_reset_query();  // Restore global post data stomped by the_post().
    }
    ?>
  3. ancawonka
    Member
    Posted 4 years ago #

    Wow, thanks for your quick response. I'll give it a whirl and get back to you.

  4. ancawonka
    Member
    Posted 4 years ago #

    Wellp, I got it to work. Thanks for helping me get into more of the WP_Query stuff, I hadn't really used it before (because it's easier to use $wpdb->query... ;) )

    Some notes:

    1. post__in - (with 2 underlines) is not a typo. Is there a reason for this?

    2. I'm using WPMU (so perhaps I should bring this question to that forum), so there are a couple of ramifications for this case. The main one is, you need to make sure that WP_Query is looking at the correct blog, so it's necessary to do a switch_to_blog before and after the query, depending on where the events are being displayed.

  5. MichaelH
    Member
    Posted 4 years ago #

    1. Two underlines is right...don't know why they did that http://core.trac.wordpress.org/ticket/4895

    2. Yes the WPMU forums is the correct place to address that.

  6. sakarya
    Member
    Posted 3 years ago #

    which date format can i use?

    05 Feb 2010
    02/05/2010
    20100205

  7. ancawonka
    Member
    Posted 3 years ago #

    You can use any date format that is recognized by MySQL. I am using yyyy-mm-dd (2011-01-05) to get dates.

Topic Closed

This topic has been closed to new replies.

About this Topic