Ask your WordPress questions! Pay money and get answers fast! (more info)

Compare two custom fields and query posts based on the result WordPress

  • SOLVED

Hello!

On my posts I'm having two custom fields: <strong>price</strong> and <strong>old_price</strong>.

I would like to have a page on the site where I <strong>query all posts</strong> where the <strong>price</strong> value is <strong>lower</strong> than the <strong>old_price</strong> value.

If the <em>price</em> and <em>old_price</em> values are equal, or if the <em>price</em> value is higher than the <em>old_price</em> value, the posts should not show up at all.

Could someone help me with this query?

Thanks!

// Jens.


<strong>EDIT</strong>: The two custom field values are both stored on the same post, so what I need is a way to figure out if the post should show up or not, based on two different custom field values from the post itself.

So: I need to compare these custom field values before the posts are being queried.

Answers (3)

2013-07-23

Hariprasad Vijayan answers:

Hi,

I think WP_Query or Query Post won't work in this situation, you need to write custom query.


Jens Filipsson comments:

Yes, thanks...


Jens Filipsson comments:

So, do you have a solution?


Hariprasad Vijayan comments:

Hi,

You can use the following custom query for this

select wp_posts.ID FROM wp_posts where (SELECT CAST(meta_value as DECIMAL(10,5)) FROM `wp_postmeta` WHERE `meta_key` = 'price' and post_id=wp_posts.ID) < (SELECT CAST(meta_value as DECIMAL(10,5)) FROM `wp_postmeta` WHERE `meta_key` = 'old_price' and post_id=wp_posts.ID) and wp_posts.post_status = 'publish'


Hariprasad Vijayan comments:

Let me know if you need any help in this.


Jens Filipsson comments:

I'm not really sure how to implement it, but does this look right to you? Right now it doesn't find any posts at all...

<?php get_header(); ?>

<?php
$querystr = "
select wp_posts.ID FROM wp_posts where (SELECT CAST(meta_value as DECIMAL(10,5)) FROM 'wp_postmeta` WHERE `meta_key` = 'pris' and post_id=wp_posts.ID) < (SELECT CAST(meta_value as DECIMAL(10,5)) FROM `wp_postmeta` WHERE `meta_key` = 'ordinarie_pris' and post_id=wp_posts.ID) and wp_posts.post_status = 'publish'
";

$pageposts = $wpdb->get_results($querystr, OBJECT);
?>

<?php if ($pageposts): ?>
<?php global $post; ?>
<?php foreach ($pageposts as $post): ?>
<?php setup_postdata($post); ?>


<h2><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title(); ?>">
<?php the_title(); ?></a></h2>
<?php endforeach; ?>

<?php else : ?>
<h2 class="center">Not Found</h2>
<p class="center">Sorry, but you are looking for something that isn't here.</p>
<?php include (TEMPLATEPATH . "/searchform.php"); ?>
<?php endif; ?>

</div>

<?php get_footer(); ?>


Hariprasad Vijayan comments:

Try this,

<?php $postids = $wpdb->get_col("select wp_posts.ID FROM wp_posts where (SELECT CAST(meta_value as DECIMAL(10,5)) FROM `wp_postmeta` WHERE `meta_key` = 'price' and post_id=wp_posts.ID) < (SELECT CAST(meta_value as DECIMAL(10,5)) FROM `wp_postmeta` WHERE `meta_key` = 'old_price' and post_id=wp_posts.ID) and wp_posts.post_status = 'publish'");
if ( $postids )
{
foreach ( $postids as $id )
{
$post = get_post( intval( $id ) );
setup_postdata( $post );
?>
<a href="<?php the_permalink() ?>" title="<?php the_title() ?>">
<?php the_title() ?>
</a>
<?php
}
}
?>

Its working here


Jens Filipsson comments:

Hmm, this really looks nice and it feels like it should work, but I'm still not getting any posts. This is in a page template file on a static page.


Hariprasad Vijayan comments:

Please check the custom field name(price and old_price in my query). At least one post should have the value price that less than old_price.


Hariprasad Vijayan comments:

Also check the table prefiix(wp is in my query)


Hariprasad Vijayan comments:

Solved

2013-07-23

Navjot Singh answers:

Try this

$oldprice = get_post_meta($post->ID, 'old_price', true);
$args = array(
'post_type' => 'product',
'meta_query' => array(
array(
'key' => 'price',
'value' => $oldprice,
'compare' => '<'
)
)
);
$myquery = new WP_Query( $args );
if ($myquery->have_posts()) :
while ($myquery->have_posts()) : $myquery->the_post();


Jens Filipsson comments:

Doesn't seem to work, unfortunately...


Navjot Singh comments:

$oldprice = get_post_meta($post->ID, 'old_price', true);
$args = array(
'meta_key' => 'price',
'meta_value_num' => $oldprice,
'meta_compare' => '<'
)
);
$myquery = new WP_Query( $args );
if ($myquery->have_posts()) :
while ($myquery->have_posts()) : $myquery->the_post();


Navjot Singh comments:

Try this one now.


$oldprice = get_post_meta($post->ID, 'old_price', true);
$args = array(
'meta_key' => 'price',
'meta_value_num' => $oldprice,
'meta_compare' => '<'
)
);
$myquery = new WP_Query( $args );
if ($myquery->have_posts()) :
while ($myquery->have_posts()) : $myquery->the_post();


Navjot Singh comments:

Wait. There is an error.

$oldprice = get_post_meta($post->ID, 'old_price', true);
$args = array(
'meta_key' => 'price',
'meta_value_num' => $oldprice,
'meta_compare' => '<'
);
$myquery = new WP_Query( $args );
if ($myquery->have_posts()) :
while ($myquery->have_posts()) : $myquery->the_post();


Jens Filipsson comments:

This fetches posts, but not the ones that have a lower price. Doesn't the comparison need to execute earlier? Now there isn't two values to compare, is it?

Thanks!


Navjot Singh comments:

Try this now
<?php

// The Query
$my_query = new WP_Query(array(
'post_status' => 'publish',
'post_type' => 'post',
'posts_per_page' => '10',
'offset' => '1',
'paged' => (get_query_var('paged')) ? get_query_var('paged') : 1
));

// The Loop
if ( $my_query->have_posts() ) {
while ( $my_query->have_posts() ) {
$my_query->the_post();
$old_price = get_post_meta(get_the_ID(), 'old_price', true);
$price = get_post_meta(get_the_ID(), 'price', true);
if( $price < $old_price ) {
the_title();
echo '<br>';
}
}
} else {
// no posts found
}
/* Restore original Post Data */
wp_reset_postdata();
?>


Jens Filipsson comments:

Not working unfortunately...

2013-07-23

Arnav Joy answers:

try this

<?php

query_posts('posts_per_page=-1&post_type=post')
if (have_posts()) :

while (have_posts()) : the_post();

$old_price = get_post_meta(get_the_ID(), 'old_price', true);

$price = get_post_meta(get_the_ID(), 'price', true);

if( $price < $old_price ) {

the_title();

echo '<br>';
}
endwhile;
endif;
?>


Jens Filipsson comments:

Hey, looking at it, I thinks this could work, haven't tried it yet though. A question: If I would like to paginate the results? Then it wouldn't work right?

Thanks!


Jens Filipsson comments:

Tried it now, and unfortunately it gives an error, the page doesn't load at all...


Arnav Joy comments:

what error did you got?


Arnav Joy comments:

is your work done?


Jens Filipsson comments:

No, unfortunately I haven't found a solution to this problem... Therefore I have raised the price money...


Jens Filipsson comments:

When trying your solution, all I'm getting is a server error.


Arnav Joy comments:

is my code worked for you?


Jens Filipsson comments:

No, I'm getting a server error with your code