Using MySQL Cast to find Wordpress posts with a custom field content equivalent to Price

Question

I am using custom fields in Wordpress to contain an 'RRP' currency value. This field is text based, but I want to be able to run queries where I can bring out posts which have an RRP in a specific range.

I've been looking into the MySQL CAST function and this seems to be the right thing, but I can't seem to get it working. Everything I use seems to just become 0.00. Can anyone point out where I am going wrong?

SELECT wpostmeta.meta_value, CAST( 'wpostmeta.meta_value' AS DECIMAL( 10, 2 ) ) , wposts . *
FROM tc_posts wposts, tc_postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id
AND wpostmeta.meta_key = 'rrp'
AND CAST( 'wpostmeta.meta_value' AS DECIMAL( 10, 2 ) ) < 9.99
ORDER BY wposts.post_date DESC

Answer 1

I think I see your problem here. 'wpostmeta.meta_value' will give you 0 when casted, you should get rid of those quotes, since it's the value you want to cast from, not the name of the field:

SELECT wpostmeta.meta_value, CAST(wpostmeta.meta_value AS DECIMAL(10, 2)), wposts.*
FROM tc_posts wposts, tc_postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id
AND wpostmeta.meta_key = 'rrp'
AND CAST(wpostmeta.meta_value AS DECIMAL(10, 2)) < 9.99
ORDER BY wposts.post_date DESC

Cheers.