Custom Select Query by two meta keys

You need to change one AND to an OR:

WHERE wposts.ID = wpostmeta.post_id
AND ((wpostmeta.meta_key = 'color' AND wpostmeta.meta_value = 'blue')
 OR (wpostmeta.meta_key = 'weight' AND wpostmeta.meta_value = '10'))
AND wposts.post_type = 'post'
ORDER BY wpostmeta.meta_value DESC
";

Also, since you are retrieving two different meta_keys, your ORDER BY will probably not work the way you want.

thanks vtxyzzy for your responses, but it is not working, any other idea?

Sorry, I overlooked an extra term in the FROM clause. You don’t need postmeta in both the FROM and LEFT JOIN clauses. Also, I suggest adding a test for published posts.

$querystr = "
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta

WHERE wposts.ID = wpostmeta.post_id
AND ((wpostmeta.meta_key = 'color' AND wpostmeta.meta_value = 'blue')
 OR (wpostmeta.meta_key = 'weight' AND wpostmeta.meta_value = '10'))
AND wposts.post_type = 'post'
AND wposts.post_status = 'publish'
ORDER BY wpostmeta.meta_value DESC
";

ok great work vtxyzzy, now I get ALL the products that have blue color OR the weight of 10kg.

What I need is to get only the products that are ‘blue’ and ’10’ kg of weight, so if the product has 10kg but is not blue he shouldn’t be displayed.

How about that?

Sorry about that – I misunderstood what you wanted. Please bear with me and try this:

$querystr = "
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta metacolor, $wpdb->postmeta metawgt

WHERE wposts.ID = metacolor.post_id
AND wposts.ID = metawgt.post_id
AND (metacolor.meta_key = 'color' AND metacolor.meta_value = 'blue')
AND (metawgt.meta_key = 'weight' AND metawgt.meta_value = '10')
AND wposts.post_type = 'post'
AND wposts.post_status = 'publish'
ORDER BY metacolor.meta_value DESC
";

And you will probably need to change the ORDER BY.

GREAT, works perfectly! Thanks

and the last part, how can I display a custom field value of a post next to the post title.

I tried with this code after the query, it displays just the titles of the posts;

ex: Post title, blue, 10

<?php global $post; ?>
<?php foreach ($pageposts as $post): ?>
<?php setup_postdata($post); ?>
<div class="post" id="post-<?php the_ID(); ?>">
<a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title(); ?>">  <?php the_title(); ?></a>
<?php
global $wp_query;
$postid = $wp_query->post->ID;
echo get_post_meta($post, 'color', true);
wp_reset_query();
?>

</div>
<?php endforeach; ?>
<?php endif; ?>

Thanks again

Give this a try:

<?php global $post; ?>
<?php foreach ($pageposts as $post): ?>
<?php setup_postdata($post); ?>
<div class="post" id="post-<?php the_ID(); ?>">
<a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title(); ?>">  <?php the_title(); ?></a>
<?php
echo ', ' . get_post_meta($post->ID, 'color', true);
echo ', ' . get_post_meta($post->ID, 'weight', true);
?>

</div>
<?php endforeach; ?>
<?php endif; ?>

This works perfectly, thank you very much!

Is it somehow possible to make the query above two or more times on the same page, but each time based on different meta_keys?

I know that to make more queries on the same page you have to use the new WP query like in the example under..

<?php $my_query = new WP_Query('category_name=Djelatnici&posts_per_page=200&orderby=title&order=asc');
  while ($my_query->have_posts()) : $my_query->the_post();
  $do_not_duplicate = $post->ID;?>

Any idea?

You can use the get_results() function as many times as you want on a page. Just change the $querystr each time.

ok, so i would have to rename $qerystr in something elese?

how would my second query on the same page have to look if this is the first one ?

<?php
global $wpdb;
global $post;
$querystr = "
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta metacolor, $wpdb->postmeta metawgt
WHERE wposts.ID = metacolor.post_id
AND wposts.ID = metawgt.post_id
AND (metacolor.meta_key = 'laboratory' AND metacolor.meta_value = 'lpem')
AND (metawgt.meta_key = 'title' AND metawgt.meta_value = 'Boss')
AND wposts.post_type = 'post'
AND wposts.post_status = 'publish'
"; 

$pageposts = $wpdb->get_results($querystr, OBJECT); ?>  <?php if ($pageposts):
?>  

<?php global $post; ?>
<?php foreach ($pageposts as $post): ?>
<?php setup_postdata($post); ?>
<div class="post" id="post-<?php the_ID(); ?>">
<a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title(); ?>">  <?php the_title(); ?></a>

</div>
<?php endforeach; ?>
<?php endif; ?>

the first query would show posts based on some meta keys and values, the second query would show posts based on some different meta keys and values, and so on..

For the first query:

$key1 = 'laboratory';
$val1 = 'lpem';
$key2 = 'title';
$val2 = 'Boss';
$querystr = "
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta metacolor, $wpdb->postmeta metawgt
WHERE wposts.ID = metacolor.post_id
AND wposts.ID = metawgt.post_id
AND (metacolor.meta_key = '$key1' AND metacolor.meta_value = '$val1')
AND (metawgt.meta_key = '$key2' AND metawgt.meta_value = '$val2')
AND wposts.post_type = 'post'
AND wposts.post_status = 'publish'
"; 

$pageposts = $wpdb->get_results($querystr, OBJECT); ?>  <?php if ($pageposts):
?>

For the second and following queries, you just need to change the keys and values, and do the query:

<?php  //Don't know if you need this - depends on the rest of your code
$key1 = 'secondkey1';
$val1 = 'secondval1';
$key2 = 'secondkey2';
$val2 = 'secondval2';

$pageposts = $wpdb->get_results($querystr, OBJECT); ?>  <?php if ($pageposts):
?>

nice, all works smoothly, thanks

you said earlier that it would be a problem order them by some order as there are two keys, is there maybe any chance to order them alphabetically by post title?

Sure, just use this as the ORDER BY clause:

ORDER BY UPPER(wposts.post_title) ASC

All the answers to my questions, thank you so much for your help!