Hi,
I have a wordpress install and have created a custom type of ‘Toy Cars’. For this custome type I have created a custom field called RRP.
I want to be able to search the database and retrieve items (i.e. Toy Cars in this case) based upon their RRP (e.g. all Toy Cars less than £10), or order then in some way related to the RRP. Since the metavalue field is text based this is not straightforward.
I have tried using the MySQL CAST function but I don’t seem to be able to get it to work.
Here is the SQL I have thus far. Can anyone help me get this right? Currently I just get 0.00 for the cast statement.
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 wposts.post_status = 'publish'
AND wposts.post_type = 'toy_cars'
AND wposts.post_date < NOW( )
AND CAST( 'wpostmeta.meta_value' AS DECIMAL( 10, 2 ) ) < 9.99
ORDER BY wposts.post_date DESC