MySQL query incorrectly storing negative numbers

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rinascimentoman
Forum Newbie
Posts: 1
Joined: Tue Dec 15, 2009 1:40 pm

MySQL query incorrectly storing negative numbers

Post 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.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: MySQL query incorrectly storing negative numbers

Post 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.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Post Reply