PHP & Mysql weirdness

[knoob]

Hi everyone!

 

Have a weird problem which I've been troubling for some time just not sure what I'm missing.

 

SQL statement created by PHP looks like this:

 

insert into PerformanceBalance (id_tmp,item,Value1,Value2,Value3,Value4) values ('1','name','2213.54','194.2','68.5','828.95');

 

While inserting it to Mysql DB through commandline all the values goes correctly to the database but if insert if done via PHP:

 

$result = @mysql_db_query("DB1", $sql);

 

The Value1 is incorrect in DB:

 

| id_tmp  | item    | Value1    | Value2    | Value3    | Value4 |

+----------+----------+------------+-----------+------------+--------+

| 1 | name | 2.000000 | 194.200000 | 68.500000 | 828.950000 | 125.700000 |

 

 

Fields in the DB all are the same:

 

+----------------------------+---------------+------+-----+---------+-------+

| Field                      | Type          | Null | Key | Default | Extra |

+----------------------------+---------------+------+-----+---------+-------+

| id_tmp                    | char(15)      | NO  | PRI | NULL    |      |

| item                      | char(15)      | NO  | PRI | NULL    |      |

| Value1                    | decimal(15,6) | YES  |    | NULL    |      |

| Value2                    | decimal(15,6) | YES  |    | NULL    |      |

| Value3                    | decimal(15,6) | YES  |    | NULL    |      |

| Value4                    | decimal(15,6) | YES  |    | NULL    |      |

 

 

It does not matter if I change the order for the insert line for Values, still it's always 'Value1' which is inserted incorrectly.

 

Updating the DB works fine, just cannot figure out the problem in PHP's behavior or if it's the problem at all.

 

Does anyone have any idea?

 

PHP version is 5.3.3

Mysql version 5.1.49

 

Using Ubuntu 10.10

 

[ignace]

1) Post your PHP code. 2) Open PHPMyAdmin, run your code, return to PHPMyAdmin and execute SHOW WARNINGS; or write SET @@global.sql_mode='TRADITIONAL'; before you execute your PHP code (make sure to catch any error).

[sasa]

are you shure that your Value1 isn't '2,213.54'?

[knoob]

Hmm, both getting me something to think about. Indeed now can see the error:

 

MySQL error 1366: Incorrect decimal value

 

Though changing the presentation to 2,213.54 didn't solve it, could be a setting somewhere but still would like to handle the numbers as pure decimals.

[knoob]

Think I got it. The thousands separator is haunting still, now just need to figure out how to get rid of it..

 

Thanks for putting me on right track.