List posts under meta_value heading

Question

I need to list completed (meta_value) projects for each year (meta_value), like this:

2006
- Project 1
- Project 2
...

2005
- Project 3 ...

I found (here) a great piece of code to deal with I'm after:

<?php // List posts by a Custom Field's values
$meta_key = 'year';  // The meta_key of the Custom Field
$sql = "
   SELECT p.*,m.meta_value
   FROM $wpdb->posts p
   LEFT JOIN $wpdb->postmeta m ON (p.ID = m.post_id)
   WHERE p.post_type = 'post'
      AND p.post_status = 'project'
      AND m.meta_key = '$meta_key'
   ORDER BY m.meta_value, p.post_date DESC
";
$rows = $wpdb->get_results($sql);
if ($rows) {
   foreach ($rows as $post) {
      setup_postdata($post);
      if ($post->meta_value != $current_value) {
         echo "<h3>$post->meta_value</h3>";
         $current_value = $post->meta_value;
      }
      // Put code here to display the post
      the_title();
   }
}
?>

But I still have (1) a further request on the code and (2) a problem with it, which are:

1. I need to filter the query by another meta_key (key state: value completed).

2. I use WPML and the post list displays both the post and it's translation (it should only get the current language posts)

Answer 1

You should use some of WordPress's built in functions, there is even a new meta compare parameter. You can create, for example:

$state =  get_post_meta($post->ID, 'meta_state', true);  //the meta value to compare
$query = new WP_Query
              ( array( 
               'meta_key' => 'project', 
               'meta_value' => '$state', 
               'meta_compare' => '<=', 
               'post_type' => 'projects' ) );

//spit them out into yearly dates using a conditional tag