$wpdb query for price in custom field value

admin管理员组

文章数量:1122832

A classifieds list site have only 3 criterias when searching products: the common search, category and region (for ZIP, city or full address) It's not enough and I really need to include a basic price system, so users can set a price range before searching.

Looking at the theme files, I found the function for different queries, based in one or more of those criterias. The smaller is the query for only category, so I'll take it as example:

elseif ($s_for == '' && $s_cat !== '' && $s_to == '') {
$query = "SELECT $wpdb->posts.*
FROM
$wpdb->posts
INNER JOIN $wpdb->term_relationships
ON $wpdb->posts.ID = $wpdb->term_relationships.object_id
WHERE
$wpdb->posts.post_type = '$cc_post_type'
AND ($wpdb->postmeta.meta_key = '$cc_price' AND $wpdb->postmeta.meta_value LIKE '%$s_prc%')
AND $wpdb->posts.post_status = '$cc_post_status'
AND ($wpdb->term_relationships.term_taxonomy_id = {$s_cat})
GROUP BY ID {$limit}";
}

Product prices are stored in custom fields (cc_price), so I tried...

AND ($wpdb->postmeta.meta_key = '$cc_price' AND $wpdb->postmeta.meta_value LIKE '%$s_prc%')

...with a simple checkbox ($s_prc added to functions) with the value (just for testing purposes), but it doens't work, "query with no results". Is the query correct and I'm wrong in any other point?

A classifieds list site have only 3 criterias when searching products: the common search, category and region (for ZIP, city or full address) It's not enough and I really need to include a basic price system, so users can set a price range before searching.

Looking at the theme files, I found the function for different queries, based in one or more of those criterias. The smaller is the query for only category, so I'll take it as example:

elseif ($s_for == '' && $s_cat !== '' && $s_to == '') {
$query = "SELECT $wpdb->posts.*
FROM
$wpdb->posts
INNER JOIN $wpdb->term_relationships
ON $wpdb->posts.ID = $wpdb->term_relationships.object_id
WHERE
$wpdb->posts.post_type = '$cc_post_type'
AND ($wpdb->postmeta.meta_key = '$cc_price' AND $wpdb->postmeta.meta_value LIKE '%$s_prc%')
AND $wpdb->posts.post_status = '$cc_post_status'
AND ($wpdb->term_relationships.term_taxonomy_id = {$s_cat})
GROUP BY ID {$limit}";
}

Product prices are stored in custom fields (cc_price), so I tried...

AND ($wpdb->postmeta.meta_key = '$cc_price' AND $wpdb->postmeta.meta_value LIKE '%$s_prc%')

...with a simple checkbox ($s_prc added to functions) with the value (just for testing purposes), but it doens't work, "query with no results". Is the query correct and I'm wrong in any other point?

• query
• wpdb
• sql

Share Improve this question asked Feb 14, 2016 at 21:54 Daniel LemesDaniel Lemes 19933 silver badges1313 bronze badges 1

• Have you considered using WP_Query instead? Writing raw sql can be dangerous and bypasses a lot of the caching and optimisation mechanisms in place – Tom J Nowell ♦ Commented Feb 15, 2016 at 2:03

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You can try the following:

AND ($wpdb->postmeta.meta_key = 'cc_price' AND $wpdb->postmeta.meta_value LIKE '%$s_prc%')

(Use cc_price instead of $cc_price)

本文标签： wpdb query for price in custom field value