Question 179755 in Wordpress Question posted in
The official Wordpress documentation can be found here.

“What MySQL query fetches products within a given price range in WordPress?”

Need a MySQL query to fetch product if provided range of price falls between the min and max price.

I have product A and product B. Product A has a min price equal to 3400 and max price equal to 5000 and Product B has a min price equal to 4400 and max price equal to 7000, stored in postmeta table (WordPress). Now I need a mysql query that should work with all below cases (not at a same time)

  1. When a user sets a filter with min price equal to 3800 and leave max price filter empty then product A must be fetched as this price range comes in between 3400 and 5000
  2. When a user sets a filter with max price equal to 5000 and leave min price filter empty then both product A and product B must be fetched as 5000 comes in between 3400 and 5000 which is product A price and also in between 4400 and 7000 which is product B price.
  3. When a user sets min price filter to 4500 and max price filter to 6000 then both products must be fetched as the max and min filter prices comes in between the min and max price of the product.
Tags:

2

Answers


  1. 0

    you can use the following query:

    global $wpdb;
$min_price = 3800; // Set the minimum price filter value
$max_price = null; // Set the maximum price filter value (null if empty)
$query = $wpdb->prepare("
    SELECT p.post_id, p.meta_value AS min_price, q.meta_value AS max_price
    FROM {$wpdb->prefix}postmeta AS p
    JOIN {$wpdb->prefix}postmeta AS q ON p.post_id = q.post_id
    WHERE p.meta_key = 'min_price'
        AND q.meta_key = 'max_price'
        AND (
            (%d IS NULL OR p.meta_value <= %d)
            AND (%d IS NULL OR q.meta_value >= %d)
        )
", $min_price, $min_price, $max_price, $max_price);

$results = $wpdb->get_results($query);

if ($results) {
    foreach ($results as $result) {
        $post_id = $result->post_id;
        $min_price = $result->min_price;
        $max_price = $result->max_price;

        // Process the retrieved product data as needed
        // ...
    }
} else {
    echo 'No products found.';
}
    Login or Signup to reply.
  2. 0 
    SELECT p.*
FROM wp_posts p
JOIN wp_postmeta pm_min ON p.ID = pm_min.post_id AND pm_min.meta_key = 'min_price'
JOIN wp_postmeta pm_max ON p.ID = pm_max.post_id AND pm_max.meta_key = 'max_price'
WHERE pm_min.meta_value <= 4500
  AND pm_max.meta_value >= 6000
  AND p.post_type = 'product'
  AND p.post_status = 'publish';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search