How do you check if value in MySQL field is Numeric in Query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

How do you check if value in MySQL field is Numeric in Query

Post by simonmlewis »

Code: Select all

$result = mysql_query ("SELECT number, category, id, title FROM products WHERE pause = 'off' and number > '50'  ORDER BY number DESC");
I need to ensure that this query pulls out info ONLY if the 'number' field has something in it, but also, only if it is a numeric output. The field does have occasionally "alpha" characters in there instead, where the owner states the information is not available at the moment. So I only want to get the info if it does have 100, 200, or whatever figure has been entered.

I thought it would be something like this:

Code: Select all

$result = mysql_query ("SELECT number, category, id, title FROM products WHERE pause = 'off' and number IS NUMERIC  ORDER BY number DESC");
.... but it doesn't work. Doesn't produce anything at all.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: How do you check if value in MySQL field is Numeric in Query

Post by mikosiko »

simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you check if value in MySQL field is Numeric in Query

Post by simonmlewis »

Thanks, but that sort of option which I had looked at would only work once the query had pulled everything from the DB.

If the 'IS NUMERIC' is within the query, the DB works less hard.

I have worked it out though. If I enter "number > '50'" it produces everything. But if I enter "number > 50", it works!!
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: How do you check if value in MySQL field is Numeric in Query

Post by mikosiko »

what datatype is your field "number" ?.... if it is any Mysql numeric datatype it can only contain a number or NULL (if you didn't define a default value for it)... :?: so... if your number field IS a Mysql numeric datatype you only have to control that it is not null in addition to whatever amount that you want to compare it with...
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: How do you check if value in MySQL field is Numeric in Query

Post by Eran »

If you remove the quotes around the number, the comparison would cast the field to an integer when comparing. Any string that is not a number will be cast to 0
[sql] ... number > 50 ... [/sql]
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you check if value in MySQL field is Numeric in Query

Post by simonmlewis »

So it deems it to be an Integer? And if it is not one, it rejects it in the query?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: How do you check if value in MySQL field is Numeric in Query

Post by Eran »

If it's not an integer it will always return 0 so a condition like ' > 50 ' will always evaluate to false and filter the relevant rows.
Post Reply