selecting records between two numbers

centenial

Hi,

I have a field called "price" in one of my MySQL database tables. I want to select ALL records between $1,000 & $25,000 - How can I do this?

Also, what format would the numbers have to be in for this to work? Are commas or money signs allowed?

Thanks,

madwormer2

The format that will give you the most functionality is just the plain number, without any commas or signs, as this will enable you to make all the number comparisons you can make, that you can't with a string.

So, you'd have the numbers stored as, for example, 3000 for $3,000.

And the query would look something like this:

SELECT * FROM `mytable` WHERE `price` > 1000 AND `price` < 25000;

centenial

Thanks!!

Is there a way to turn a number like this 30000 into a nicely formatted number like this $30,000 ?? Could this be done with PHP and arrays?

madwormer2

Just so happens to be a function that does just that.

[man]number_format/man

and there's even

[man]money_format/man

Installer

Something like this should work:

SELECT CONCAT('$', FORMAT(price, 0)) AS my_price FROM mytable WHERE price BETWEEN 1000 AND 25000;

or, for non-inclusive:

...WHERE price BETWEEN 1001 AND 24999;

(Unnecessary backticks omitted.)

madwormer2

Gargh, SQL confuses me :p I go for as simple SQL as possible, and let PHP do the rest.