Jump to content

insert modifies my float data


wolfie1

Recommended Posts

Hello,

 

The data that actually gets stored in my database differs from what I'm trying to put there.  Here's a short code snippet:

 

$lat = 42.272939;

$lng = -83.622941;

 

  // insert cordinates into dB

  $query = "INSERT INTO markers (lat, lng, user_id) VALUES ('$lat','$lng','$user_id')";

  $results = mysql_query($query)

    or die(mysql_error());

 

When I look in the database, the data is stored as:

42.272938  and  -83.622940.

 

Any ideas as to why this happens?

 

These values are being stored as float(10,6).  Not sure if this is useful, but here's some other info:

datbase:  MySQL

engine:    InnoDB

Format:    Compact

Collation:  utf8_general_ci

 

Thanks

Link to comment
https://forums.phpfreaks.com/topic/213480-insert-modifies-my-float-data/
Share on other sites

Numerical values don't need quotes around them, and I personally wouldn't put the data in IP stylee, I would preg_replace the periods for something else...

$query = "INSERT INTO `markers` (`lat`, `lng`, `user_id`) VALUES (".$lat.",".$lng.",".$user_id.")";

 

Something like that anyway..

 

Rw

LOL, when you put numeric data inside quotes in the query, mysql first converts that data to floating point. If that data happens to have a fractional part, you end up with floating point precision conversion errors.

 

AND, you should define your columns as a DECIMAL data type if they are not already, as the same problem will occur if the columns themselves are defined as a FLOAT data type.

Thanks guys.  I altered the table structure to store these as decimal(10,6) instead of float(10,6), and the data is getting stored correctly now.  I also understand this after just having read http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html.

 

Question:

I didn't modify the query (to address the quotes issue described in both replies) and the data is getting stored correctly.  Am I just getting lucky with this particular value?  Does the query need to be modified for sure? 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.