PHP and MySQL how to query decimal column

Question

I have a decimal field in the database which I try to query. The field is used for all sort of numbers so it could be a currency with 2 digits or a anything else with a different format. The field is set up as decimal(15,5).

So a currency could be saved in there like 4.99000 and I format it when I read it out according to a set up which is saved in another table which will tell me the decimal-places and the decimal-separator. All that is fine, but how would I query something like that? Because "WHERE field = 4.99" will obviously not return this record. Any suggestions what I could do?

UPDATE:

OK, I have to go a slight different way. I take the entered number and use explode to split it into an array. I know that after the decimal . I have 5 numbers so as a next step I use

$numericValue = explode($myFormat['decimalseparator'], $value) // could be a . or a ,
$newValue = str_pad($numericValue[1], 5, "0", STR_PAD_RIGHT) ;
$dbValue = $numericValue[0].'.'.$numericValue[1];

It does seem to work, but I do wonder if I oversee something here...

will obviously not return this record did you try? it the datatype of the table column supports decimal that will work — Danyal Sandeelo
– Danyal Sandeelo, Commented Nov 29, 2016 at 10:41

Strawberry · Accepted Answer · 2016-11-29 10:50:22Z

1

SELECT 4.99 = 4.9900000000;

+---------------------+
| 4.99 = 4.9900000000 |
+---------------------+
|                   1 |
+---------------------+

answered Nov 29, 2016 at 10:50

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Parthipan Natkunam · Accepted Answer · 2016-11-29 11:40:55Z

0

How about something like this:

SELECT *  FROM mytable WHERE 
'field' REGEXP '(^[4]+.[9]+[9]$)|(^[4]+.[9]+[9]+0{1,3}$)';

MySQL Regex Documentation here.

P.S: The SQL Query is just a quick rough draft to give the idea.

edited Nov 29, 2016 at 11:40

answered Nov 29, 2016 at 10:50

Parthipan Natkunam

7941 gold badge11 silver badges16 bronze badges

5 Comments

Luka Over a year ago

i can not use the wildcard since it needs to search for the exact figure.

Parthipan Natkunam Over a year ago

What does exact figure mean? Could you please elaborate it?

Luka Over a year ago

I want to search for 4.99, so if the record is 4.9952 it should not be shown. I have updated above with a solution. I was actually wondering if MySql itself got something build in, but it seems to work with my PHP solution above.

Parthipan Natkunam Over a year ago

Thanks @Luka . I understand now and have edited my answer using Mysql Regex. Hope this is what you were looking for.

Luka Over a year ago

Thanks a lot, that is a very interesting approach and I will be looking deeper into it. Unfortunately it does return 44.99000 right now as well and I am not sure how I would actually implement it since it is all very complicated with the different format options of the actual value, but still an interesting solution. Thanks !

Collectives™ on Stack Overflow

PHP and MySQL how to query decimal column

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related