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)
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
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...
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