Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

Ok, so I'm using the WPAlchemy class to create custom field write panels in the write post page, and so far everything has gone great... However, there's one issue I can't seem to figure out. I'm trying to use the custom field values of "event dates" to sort events on a custom page template.

I followed the instructions "Query based on Custom Field and Sorted by Value" found in the codex to try and setup the custom query, but it doesn't seem to be working?

Here's the code from the custom page template for the "Events" page:

<?php
/* 
  Template Name: Events
*/
get_header();
?>
<div id="depthead" class="grid_12">
  <h2>Upcoming Events</h2>
</div><!--/depthead-->
<?php

$querystr = "
  SELECT wposts.* 
  FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
  WHERE wposts.ID = wpostmeta.post_id 
  AND wpostmeta.meta_key = '_events_meta[event_date]' 
  AND wposts.post_type = 'post'

  ORDER BY wpostmeta.meta_value DESC
";
$pageposts = $wpdb->get_results($querystr, OBJECT);

?>
<?php if ($pageposts): global $post; $cnt=0; foreach ($pageposts as $post): $cnt++; setup_postdata($post); ?>
<div id="article-<?php echo get_the_ID(); ?>" class="listingbox grid_3">
  <div class="deptpostimg">
    <a href="<?php the_permalink(); ?>" title="<?php the_title_attribute(); ?>"><img src="<?php echo $events_metabox->get_the_value('event_thumbnail'); ?>" style="outline:1px solid #000" alt="<?php the_title_attribute(); ?>" /><span class="event-date"><?php $events_metabox->the_value('event_date'); ?></span></a>
  </div><!--/deptpostimg-->
  <h4 class="listing-titles"><a href="<?php the_permalink(); ?>" title="<?php the_title_attribute(); ?>"><?php the_title(); ?></a></h4>
  <div class="excerpt">
    <?php the_excerpt(); ?>
  </div><!--/excerpt-->
</div><!--/article-<?php echo get_the_ID(); ?>-->
<?php if($cnt % 4 == 0) { ?>
<div class="grid_12 rowseparator">
  <hr />
</div><!--/rowseparator-->
<?php } ?>
<?php endforeach; endif; ?>
</div><!--/wrapper-->
<?php get_footer(); ?>

It's like it's not picking up the custom field keys... The class I'm using to create these custom write panels stores them all as an array, hence the reason I've attempted to access them by using: _events_meta[event_date]

Maybe that's the problem, but I don't know how to fix it if it is...

Any ideas?


EDIT: Here's an image so you can see just how the custom fields are stored in the database. Hopefully that helps you figure out why on earth _events_meta[event_date] won't work in the query?

Screenshot of PHPMyAdmin with an Insert Query for the WordPress database

share|improve this question
add comment

3 Answers

up vote 0 down vote accepted

Josh, take a look at: http://farinspace.com/wpalchemy-metabox-data-storage-modes/ ...

I am thinking I will be changing how WPAlchemy stores values by default ... making EXTRACT mode default ...

share|improve this answer
    
Dude, that just saved me from some serious hair loss... Thanks! –  Josh Aug 30 '10 at 13:30
add comment

I also got a blog with custom fields regarding event. Here is the query I used in combination with the query_posts() function:

query_posts($query_string . "&meta_key=event_date_end&meta_compare=>=&meta_value=".date('Y-m-d')."&meta_key=event_date_start&orderby=meta_value&order=ASC");

It will select all posts with a custom field named event_date compare it with the current date (to filter out old events) and sort them by another custom field called event_start_date. I hope that complex exmaple shows you, how you should use the query_posts() function correctly.

A simple selection that test only for the existance of a custom field looks like this:

query_posts('meta_key=event_date'); 

A simple ORDER BY a custom field looks like this:

query_posts('meta_key=event_date&orderby=meta_value&order=ASC');

You can see many example in the API description of the query_posts() function.

share|improve this answer
    
The problem with that example is that the custom field isn't named "event_date"... It's a custom field named "_events_meta" which holds an array of different custom field key/value pairs, such as "event_date" among others. That's why I tried to use _events_meta[event_date] in the query. –  Josh Aug 29 '10 at 21:08
add comment

Hi @Josh:

Your query works fine. It's probably that your $events_metabox variable is not in scope and causing your code to just fail (strangely it still runs the footer, dunno why.) To fix it first try setting it to global at the top of your template:

global $events_metabox

If that doesn't work you'll have to track down the problem some other way since I don't know what your code is doing with $events_metabox.

Debug Using Incremental Removal and print_r()

When you run into a problem like this start taking away pieces until you fix the problem. If you are trying to see if your query is working or not, use a print_r() to dump the values wrapped in <pre> tags so you can see what's happening, i.e.:

<?php
$pageposts = $wpdb->get_results($querystr, OBJECT);
echo '<pre>';
print_r($pageposts);
echo '<pre>';

Also, Use get_posts() instead of Direct SQL

That said, I'd highly recommend you replace your query with a get_posts() call. It's a best practice in WordPress to always use the WordPress API and it's query functions and never use direct SQL unless there's absolutely no way around it. You get many benefits including built-in caching in some cases, they handle things like ensuring only published posts are displayed unless you tell it otherwise, and it's less likely to break if they change the database structure in the future.

Here's the get_posts() call you'd need to replace your hardcoded SQL query (with some caveats):

$pageposts = get_posts('meta_key=_events_meta[event_date]&orderby=meta_value&order=ASC');

Or this equivalent does the exact same thing:

$pageposts = get_posts(array(
  'meta_key' => '_events_meta[event_date]',
  'orderby'  => 'meta_value',
  'order'    => 'ASC',
));

The caveats I mentioned are that your query didn't filter out non-published, drafts etc.; my guess is what get_posts() does it actually more what you'd want anyway.

The Codex Docs for get_posts()

The documentation for get_posts() is here:

But interestingly the better documentation for get_posts() arguments is on the query_posts() page which you could also probably use for your needs. I prefer the greater control of get_posts() but YMMV.

Anyway, query_posts() actually just calls get_posts() so the arguments really are identical:

share|improve this answer
    
I agree, I actually prefer query_posts() or get_posts() as well... However, when I'm trying something I've never done before, I tend to stick to what the documentation says until I have a better understanding of how it works. However, when using: $pageposts = get_posts('meta_key=_events_meta[event_date]&orderby=meta_value&order=ASC'); print_r($pageposts); All that's returned is "Array()" - Odd. Also, $events_metabox is declared as global at the top of the page. –  Josh Aug 30 '10 at 0:28
    
So the empty array is leading me to believe even more firmly that it must have something to do with the custom field key not being recognized in the query... For some reason _events_meta[event_date] just isn't working, even though that's exactly how the data is stored in the db... –  Josh Aug 30 '10 at 0:30
    
@Josh I copied and ran your code and it output nothing beyond the header (I pretty much don't answer questions here unless I actually test the code.) It was weird. Once I removed the lines with $events_metabox it started outputting. Have you definitely tested without those lines? Also how are the event dates stored? Meta is stored as longtext in MySQL so the only way sorting would work would be in YYYYMMDD format (i.e. date(DATE_ISO8601) should work.) Do you have plugins that might be affecting your queries? –  MikeSchinkel Aug 30 '10 at 4:28
    
Thanks for the in-depth explanations... but it did in fact turn out to be that the query wouldn't work with keys stored as arrays. Dimas (the creator of the WPAlchemy class) showed me where to look for the solution. Thanks again! –  Josh Aug 31 '10 at 0:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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