Page 1 of 1

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

Posted: Thu Mar 18, 2010 2:43 pm
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.

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

Posted: Thu Mar 18, 2010 3:37 pm
by mikosiko

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

Posted: Thu Mar 18, 2010 3:45 pm
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!!

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

Posted: Thu Mar 18, 2010 7:35 pm
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...

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

Posted: Thu Mar 18, 2010 7:43 pm
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]

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

Posted: Fri Mar 19, 2010 3:08 am
by simonmlewis
So it deems it to be an Integer? And if it is not one, it rejects it in the query?

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

Posted: Fri Mar 19, 2010 4:20 am
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.