insert modifies my float data

[wolfie1]

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

[rwwd]

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

[PFMaBiSmAd]

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.

[wolfie1]

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? 

[PFMaBiSmAd]

Yes, remove the quotes. The definition of how numbers within quotes get operated on could change at any point in time.

 

Put string data in quotes. Don't put numbers in quotes.