Page 1 of 1

MySQL query incorrectly storing negative numbers

Posted: Tue Dec 15, 2009 3:29 pm
by rinascimentoman
Hello,

I'm working on a Twitter app, and the following are the relevant code snippets:

$geo_longitude = (float) $tweets[$i]['geo_longitude'];
$geo_latitude = (float) $tweets[$i]['geo_latitude'];

switch ($mood) {
case happy:
$query = "INSERT INTO happy_tweets
(twitter_id, published, image_uri, title, content, updated, source, author_name, author_uri location, geo_status, geo_accuracy, geo_longitude, geo_latitude)
VALUES ";
break;
etc. etc etc.

$query .= "( $twitterident, '$published', '$image_uri', '$title', '$content', '$updated', '$source', '$author_name', '$author_uri', '$location', $geo_status, $geo_accuracy, $geo_longitude, $geo_latitude ),";

$query = substr($query, 0, strlen($query)-1);

echo "the query is as follows ";
echo($query);

mysql_query($query);

The query echo looks like this, and appears to be correct:

INSERT INTO happy_tweets (twitter_id, published, image_uri, title, content, updated, source, author_name, author_uri, location, geo_status, geo_accuracy, geo_longitude, geo_latitude) VALUES ( 6643773536, '2009-12-13 18:39:30', 'http://a1.twimg.com/profile_images/4740 ... normal.jpg', 'At Neary Lagoon. So beautiful. I love places like this. Wishin we had one in westwood so I could go everyday.. #peaceful', 'At Neary Lagoon. So beautiful. I love places like this. Wishin we had one in westwood so I could go everyday.. #peaceful', '2009-12-13 18:39:30', 'txt', 'seeking_freedom (Susan Li)', 'http://twitter.com/seeking_freedom', 'Los Angeles', 200, 4, -118.2436849, 34.0522342 )

The problem:

geo_longitude (value = -118.2436849) is being stored as -100 in the MySQL database. Numbers greater than -100 (e.g., -45.55, -67.864, etc.) are stored correctly.

Note: the geo_longitude column in the mysql database is defined as FLOAT.

Something's happening between the time the query is sent to the server and the data is stored in the database. Any ideas?

Thanks.

Re: MySQL query incorrectly storing negative numbers

Posted: Tue Dec 15, 2009 4:12 pm
by AbraCadaver
In the definition of geo_longitude, make sure that you have declared the precision and scale (10,10) whatever. For precision math you should probably use DECIMAL type and declare the precision and scale. I don't know what problems I would have, but I would be tempted to store these as text.