Numeric BETWEEN AND

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ssand
Forum Commoner
Posts: 72
Joined: Sat Jun 22, 2002 9:25 pm
Location: Iowa

Numeric BETWEEN AND

Post by ssand »

I have

Code: Select all

AND `price` BETWEEN 1 AND 2500
which works. But I need only data that is numeric and is BETWEEN 1 AND 2500.

I through I was on the right track with

Code: Select all

AND `price` regexp '[[:digit:]]' BETWEEN 1 AND 2500
but it returns nothing.

Is there a is_numeric() equivalent?

Thanks
ssand
Forum Commoner
Posts: 72
Joined: Sat Jun 22, 2002 9:25 pm
Location: Iowa

Re: Numeric BETWEEN AND

Post by ssand »

Figured it out.

I found that [[:digit:]] included anything containing digits and need to use BETWEEN separately (thought I could just combine it).

Code: Select all

AND `price` regexp '^[0-9]+$'
AND `price` BETWEEN 1 AND 2500
Thanks
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Numeric BETWEEN AND

Post by AbraCadaver »

Ideally your database column should be defined as the correct type like DECIMAL, NUMERIC, etc.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
ssand
Forum Commoner
Posts: 72
Joined: Sat Jun 22, 2002 9:25 pm
Location: Iowa

Re: Numeric BETWEEN AND

Post by ssand »

Well, it was originally. Then someone thought they needed it to support more than just numbers and decimals. For a "price" column?!? Really?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Numeric BETWEEN AND

Post by John Cartwright »

ssand wrote:Well, it was originally. Then someone thought they needed it to support more than just numbers and decimals. For a "price" column?!? Really?
You seriously should consider eliminating or altering the data that does not conform to a decimal (2,4), fix your table structure, then write the correct query. What your doing is terribly inefficient.
Post Reply