2

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 :)

  • What are you getting with this code? – Bainternet Feb 8 '11 at 18:19
3

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.

  • Thanks Ethan, that's pretty much what it was, I also rearranged it so that it makes more sense (posted the final result :) ). – Noel Tock Feb 8 '11 at 18:54
1

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'
) );
1

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
         ";

?>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.