Need help with query using custom fields

  • TrishaM

    (@trisham)


    15 years, 8 months ago

    I have a category of posts for “events” and am using a custom field for the event date (that is different from the posting date). I need to have a section in my sidebar for “upcoming events” that will list the permalink to those events which have future dates, not events that have already passed.

    In searching the Codex I found examples on how to structure a query on the post’s custom fields, but since I am not a php programmer I don’t know how to structure the query to check the custom field (‘event_date’) against the current date of any given date that someone could be looking at the blog, in order to display future (upcoming) events only.

    I found this in the Codex:

    <?php
 $querystr = "
    SELECT wposts.*
    FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
    WHERE wposts.ID = wpostmeta.post_id
    AND wpostmeta.meta_key = 'tag'
    AND wpostmeta.meta_value = 'email'
    AND wposts.post_status = 'publish'
    AND wposts.post_type = 'post'
    ORDER BY wposts.post_date DESC
 ";
 $pageposts = $wpdb->get_results($querystr, OBJECT);
 ?>

    Where it shows wpostmeta.meta_key as ‘tag’, mine is ‘event_date’, and where it shows wpostmeta.meta_value as ’email’, mine is a date in this format: mm/dd/yy

    Can anyone tell me how to modify the code above to check the event_date against the current date in order to display links to only those posts whose event_date is equal or greater than the current date?

    Many thanks for any help anyone can offer!

Viewing 5 replies - 1 through 5 (of 5 total)
  • Thread Starter TrishaM

    (@trisham)

    15 years, 8 months ago

    Bump…..anyone? Any php programmers out there?

    Thread Starter TrishaM

    (@trisham)

    15 years, 8 months ago

    The closest solution I can find is a plugin called Future Now, but this will not suit my purpose as I routinely use a future timestamp for posting, and don’t want posts to appear before their timestamp…….

    Besides it seems like overkill to use a plugin when a simple query should produce the desired results – compare current date with meta_key of event_date, and display the permalink only for those whose event_date is equal to or greater than current date.

    I don’t have the PHP skills myself to write this query, but it seems like it should be fairly straightforward…….if there is someone who is an experienced PHP coder who can tell me that this is not as simple as I imagine it to be then I’ll keep searching for a suitable plugin, just not having any luck so far – I’ve tested a number of “events” plugins but they don’t work from posts, only from their own separate db, which won’t due as these events need to be individual posts.

    Would be grateful for any help or suggestions…..

    Thread Starter TrishaM

    (@trisham)

    15 years, 8 months ago

    I am doing this work is for a non-profit site for which I volunteer my time to help them with their website – I do not get paid, nor do I have any budget for this, but I am willing to pay a small bounty for some help with this, if you have a PayPal account – I can go as high as $20 for what should be just a few minutes help if you’re a good PHP programmer.

    Please?????

    Thread Starter TrishaM

    (@trisham)

    15 years, 8 months ago

    Bump……

    johnny-b

    (@johnny-b)

    15 years, 7 months ago

    Have you looked into the Event Calendar EC3 Plugin (wpcal.firetree.net)? It includes an ‘Upcoming Events’ feature. I’m using this plugin and it works well unless you’ve made a lot of modifications to your code.

    Other than that, I can only suggest this chunk of code below. It might not work (I’m kinda new to this), and it is an ugly, inefficient little piece of code…but at least its something. Maybe it will generate some more responses to this thread.

    <?php foreach (get_categories('include=your category here') as $term) : ?>

	         <?php $events_query =
		   "SELECT distinct DAY(post_date) as Day, MONTH(post_date) as Month, YEAR(post_date) as Year, term_taxonomy_id as Term
                    FROM ($wpdb->posts p left outer join $wpdb->term_relationships r on p.ID = r.object_id)
                    WHERE post_status = 'publish' and term_taxonomy_id =
		    (SELECT term_taxonomy_id FROM $wpdb->term_taxonomy where term_id = $term->term_id)
		    and event_date >= CURDATE()
		    ORDER BY post_date;
	       ?>

	       <?php $event_days = $wpdb->get_results($events_query); ?>

	       <?php if (!empty($event_days)) : ?>

	         <h2 style="border-bottom:1px #ccc solid;"><a href="<?=get_option('home'); ?>/category/<? echo $term->slug; ?>/"><? echo $term->name; ?></a></h2>

	         <?php foreach ($event_days as $event_day) : ?>

		   <?php $day_query = new WP_Query('cat='.$term->term_id.'&day='.zeroise($event_day->Day, 2).'&monthnum='.zeroise($event_day->Month, 2).'&year='.$event_day->Year.'&posts_per_page=-1'); ?>

		     <?php if ($day_query->have_posts()) : ?>

		     <?php while ($day_query->have_posts()) : $day_query->the_post(); ?>

		       <div class="post" id="post-<?php the_ID(); ?>">
		         <a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a>

                         <small><?php the_time('F j, Y') ?> | <?php the_author() ?> </small>
		       </div>

		     <?php endwhile; ?>

	           <?php endif; ?>

		 <?php endforeach; ?>

		 <?php else : ?>

		   <h2 style="border-bottom:1px #ccc solid;"><? echo $term->name; ?></h2>
                   <span style="color:gray;">No entries.</span>

		 <?php endif; ?>

	       <?php endforeach; ?>

    In the first line, you should replace ‘you category here’ with the term_id for your events category.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Need help with query using custom fields’ is closed to new replies.