Retrieving post list of this author meta

You just need to use ‘AND’ instead of the second ‘WHERE’, and include meta_value in the results:

<?php
$thisuser_id = get_the_author_ID();
echo $thisuser_id;
$querystr = "
   SELECT wposts.*, wpostmeta.meta_value
   FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
   WHERE wposts.ID = wpostmeta.post_id
      AND post_author = $thisuser_id
      AND wpostmeta.meta_key = 'event_start_date'
   ORDER BY wpostmeta.meta_value ASC
   ";
$pageposts = $wpdb->get_results($querystr, OBJECT);
if ($pageposts):?>
   <small>
   <?php _e("Upcoming events"); ?>
   </small>
   <ul>
      <?php foreach ($pageposts as $post):
         setup_postdata($post);?>
            <li>
                <?php the_title(); ?>
             </li>
      <?php endforeach; ?>
   </ul>
<?php endif; ?>

What author meta key and value did you want to filter on?

Thank’s a lot vtxyzzy !

now I’ve done this:
List maximum of 5 posts in category 35 or 42, sorted by “event_start_date”, ASC, and only if “event_start_date” is equal or higher than current date

<?php
	global $wpdb;
	global $post;
	$querystr = "
	SELECT wposts.*
	FROM $wpdb->posts wposts
		LEFT JOIN $wpdb->postmeta wpostmeta ON wposts.ID = wpostmeta.post_id
		LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id)
		LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
	WHERE wpostmeta.meta_key = 'event_start_date'
		AND wpostmeta.meta_value >= CURDATE()
		AND $wpdb->term_taxonomy.taxonomy = 'category'
		AND $wpdb->term_taxonomy.term_id IN(35,42)
	ORDER BY wpostmeta.meta_value ASC
	LIMIT 5
	";

this work well!

but now I want to add another condition like:
only if an author_meta called “subscription_expire” is equal or higher than current date

but I’ve not find enough examples for my limited knowledge of php,
I think it should be something like:

WHERE author_meta.meta_key = 'subscription_expire'
     AND author_meta.meta_value >= CURDATE()

is author_meta.meta_value and author_meta.meta_valueright?
where i can put the code? after the last “AND”?

thank’s for your attention

I think the code below is correct for what you want.

You should be aware that the meta_value must be stored in ‘yyyy-mm-dd’ format or else you will need to reformat it for the DATE() function to work.

<?php
	global $wpdb;
	global $post;
	$querystr = "
	SELECT wposts.*
	FROM $wpdb->posts wposts
		LEFT JOIN $wpdb->postmeta wpostmeta ON wposts.ID = wpostmeta.post_id
		LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id)
		LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
		LEFT JOIN $wpdb->usermeta umeta ON (wposts.post_author = umeta.user_id)
	WHERE wpostmeta.meta_key = 'event_start_date'
		AND wpostmeta.meta_value >= CURDATE()
		AND $wpdb->term_taxonomy.taxonomy = 'category'
		AND $wpdb->term_taxonomy.term_id IN(35,42)
		AND umeta.meta_key = 'subscription_expire'
		AND DATE(umeta.meta_value) >= CURDATE()
	ORDER BY wpostmeta.meta_value ASC
	LIMIT 5
	";

many thanks vtxyzzy, you’re the man!