Custom Select Query with Custom Post Types

2 Views

Have been trying to figure out the issue with this for the past hour, but hitting a wall. Basically I’m just trying to query my custom post type based on some custom date fields and then displaying each. Anyone have an idea where I’m going wrong? 

<?php    
// - query -
                 global $wpdb;
                 $yesterday = strtotime("-1 day");
                 $querystr = "
                    SELECT *
                    FROM $wpdb->posts
                    INNER JOIN {$wpdb->postmeta}postmeta m1
                      ON ( {$wpdb->posts}posts.ID = m1.post_id )
                    INNER JOIN {$wpdb->postmeta}postmeta m2
                      ON ( {$wpdb->posts}posts.ID = m2.post_id )
                    WHERE $wpdb->posts.post_type = 'tf_events'
                    AND $wpdb->posts.post_status = 'publish'
                    AND ( m1.meta_key = 'tf_events_startdate' AND m1.meta_value > $yesterday )
                    AND ( m2.meta_key = 'tf_events_starttime')
                    ORDER BY m1.meta_key ASC, m2.meta_key ASC LIMIT 30
                 ";

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

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

                // - variables -
                $custom = get_post_custom(get_the_ID());
                $sd = $custom["tf_events_startdate"][0];
                $ed = $custom["tf_events_enddate"][0];
                $st = $custom["tf_events_starttime"][0];
                $et = $custom["tf_events_endtime"][0];
                $post_image_id = get_post_thumbnail_id(get_the_ID());
                        if ($post_image_id) {
                                $thumbnail = wp_get_attachment_image_src( $post_image_id, 'post-thumbnail', false);
                                if ($thumbnail) (string)$thumbnail = $thumbnail[0];
                        }

                // - output -
                ?>

                    <div>Show the goodies</div>

              <?php endforeach; ?>

              <?php else : ?>
                <h2 class="center">Not Found</h2>
                <p class="center">Sorry, but you are looking for something that isn't here.</p>
              <?php endif; ?>

Thank you in advance 🙂

Post Views: 2

Related posts

Leave a Reply

3 comments

  1. I would check the output of $querystr. It looks like you have the table name twice. $wpdb->postmeta resolves to something like wp_postmeta and then you have postmeta again. This gives you wp_postmetapostmeta which isn’t a valid table.

    I highly recommend you try out Debug Bar. It greatly simplifies query debugging.

  2. Finally got it, thanks for the help guys…

    <?php
        $querystr = "
            SELECT *
            FROM $wpdb->posts wposts, $wpdb->postmeta metadate, $wpdb->postmeta metatime
            WHERE (wposts.ID = metadate.post_id AND wposts.ID = metatime.post_id)
            AND (metadate.meta_key = 'tf_events_startdate' AND metadate.meta_value > $yesterday )
            AND metatime.meta_key = 'tf_events_starttime'
            AND wposts.post_type = 'tf_events'
            AND wposts.post_status = 'publish'
            ORDER BY metadate.meta_value ASC, metatime.meta_value DESC LIMIT 30
         ";

    ?>

  3. Close to this query (dunno if you could choose multiple meta keys – it’s possible with orderby):

    $tf_events_query = new WP_Query( array(
     'post_type'        => 'tf_events'
    ,'posts_per_page'   => '30'
    // ordered by meta_valu AND date
    ,'orderby'          => 'meta_value date'
    // or if the meta key value is numeric ordered by num meta value AND date
    // ,'orderby'       => 'meta_value_num date'
    ,'meta_key'         => 'tf_events_startdate'
    ,'order'            => 'ASC'
) );