Key Value Pair Comparison

Question

SELECT wposts.*, wpostmeta.*
    FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta, $wpdb->postmeta wpostmeta2, $wpdb->postmeta wpostmeta3
    WHERE wposts.ID = wpostmeta.post_id
        AND wposts.ID = wpostmeta2.post_id
        AND wposts.ID = wpostmeta3.post_id
        AND wpostmeta.meta_key = 'listing_subtype'
            AND wpostmeta.meta_value = '$search_home_type'
        AND wpostmeta2.meta_key = 'district'
            AND wpostmeta2.meta_value = '$search_district'
        AND wpostmeta3.meta_key = 'price_current'
            AND wpostmeta3.meta_value IS >= '$search_price_min' AND <= '$search_price_max'
        AND wposts.post_status = 'publish'
        AND wposts.post_type = 'vreb_property'
    ORDER BY wposts.post_date DESC

The line AND wpostmeta3.meta_value IS >= '$search_price_min' AND <= '$search_price_max' is trying to make sure that the meta_value of the meta_key price_current is GREATER THAN $search_price_min and LESS THAN $search_price_max

This isn't working...

[You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<= '100000' AND wposts.post_status = 'publish' AND wposts.post_type = 'vreb_' at line 11]

is it showing an invalid SQL statement or just not returning the results you want? — jbnunn, Jan 23 '13 at 01:06
Invalid statement right at that point. I've tried adding brackets around that one statement, but that doesn't seem to make a difference. — dcolumbus, Jan 23 '13 at 01:06
Get rid of the `IS` in `IS >= ` Should be `wpostmeta3.meta_value >= '$search_price_min' AND <= '$search_price_max'` — Michael Berkowski, Jan 23 '13 at 01:07
(maybe try a BETWEEN, `AND wpostmeta3.meta_value BETWEEN '$search_price_min' and '$search_price_max'`) — jbnunn, Jan 23 '13 at 01:07
No, sorry. that doesn't work. `AND wpostmeta3.meta_value >= '$search_price_min' AND <= '$search_price_max'` makes no difference. — dcolumbus, Jan 23 '13 at 01:08
@dcolumbus What does your error checking report? There was a clear syntax error, so if you have other syntax errors, you need to find them. First get the query working in a MySQL client, then put it into PHP. — Michael Berkowski, Jan 23 '13 at 01:10
convert from string to float or int ... http://stackoverflow.com/questions/5960620/convert-text-into-number-in-mysql-query — jbnunn, Jan 23 '13 at 01:10
@dcolumbus You need something on both sides of `<=`. `AND wpostmeta3.meta_value <= '$search_price_max'` — Michael Berkowski, Jan 23 '13 at 01:10
...or use `wppostmeta3.meta_value BETWEEN '$search_price_min' AND '$search_price_max'` — Michael Berkowski, Jan 23 '13 at 01:11
Tried that... got that following error: `Unknown column 'wppostmeta3.meta_value' in 'where clause'` — dcolumbus, Jan 23 '13 at 01:13
Because I misspelled it. Check your table aliases and put in the correct one. — Michael Berkowski, Jan 23 '13 at 01:14
Caught that. Now I don't get any results ... which I know is not true. At least there's no error. Any other options? — dcolumbus, Jan 23 '13 at 01:16

score 0 · Answer 1 · answered Jan 23 '13 at 01:17

0

AND <= '$search_price_max'

and what should be <=? You missed the argument for that operator: That's the error.

Probably you meant:

AND wpostmeta3.meta_value <= '$search_price_max'

answered Jan 23 '13 at 01:17

Shoe

74,840
36
166
272

score 0 · Accepted Answer · answered Jan 23 '13 at 01:18

0

AND wpostmeta3.meta_value IS >= '$search_price_min' AND <= '$search_price_max'

needs to be

AND wpostmeta3.meta_value >= '$search_price_min'
AND wpostmeta3.meta_value <= '$search_price_max'

answered Jan 23 '13 at 01:18

inhan

7,394
2
24
35

That's exactly why. Thanks, inhan. Any reason why my results seem to work if my `search_price_max` is 800000 but nothing lower? It's so odd because the `current_price` is only **59000** – dcolumbus Jan 23 '13 at 01:25
@dcolumbus Well it depends. What is the datatype of the `meta_value` column and what are min & max values you're using in your query? – inhan Jan 23 '13 at 01:27
Well, the `meta_value` should be a string ... and the min is `0` while the max is `1000000000` or whatever. It seems to always return a result if the number is not starting with a `1` ... correction, anything after `600000` – dcolumbus Jan 23 '13 at 01:29
If you're trying to evaluate a value against numbers why are you using single quotes around them at first place? – inhan Jan 23 '13 at 01:31
THATS IT! Of course! The single quotes stops the numbers from being able to be evaluated! Thanks! – dcolumbus Jan 23 '13 at 01:33
Don't forget to cast those numbers to integers if you're getting their values from the user - just in case, for security reasons. – inhan Jan 23 '13 at 01:35
$search_price_min = (int)$params['search_price_min']; ? – dcolumbus Jan 23 '13 at 01:35
Exactly. If these are provided by you explicitly you don't need to worry, though. – inhan Jan 23 '13 at 01:38

Key Value Pair Comparison

2 Answers2