CODEWITHSUNDEEP
phpmysqldatabasedecimalrounding
codacopia
codacopia

Reputation: 2501

What is the Best MySQL Database Formatting Type for GPS Coordinates?

I have a script which is inserting latitude and longitude into the MySQL database. I have assigned these rows as: 

`gpslat` DECIMAL(11,4)  NOT NULL ,
`gpslong` DECIMAL(11,4)  NOT NULL ,

For some reason, when I pass the string gpslat=45.7834 and gpslong=78.7834 I am getting a value in the database without the full string as gpslat=45.0000 and gpslong=78.0000

Any ideas why this is not just sending the complete values?

PHP Addition:

function upload($lat, $long) {

if (gpsData['error']==0) {
    $result = query("INSERT INTO gpsTable(gpslat,gpslong) VALUES('%d','%d')",$lat,$long);
}

I have the $lat and $long values being passed in this case as $lat=45.7834 and $long=78.7834

Query Function:

function query() {
global $link;
$debug = false;

//get the sql query
$args = func_get_args();
$sql = array_shift($args);

//secure the input
for ($i=0;$i<count($args);$i++) {
    $args[$i] = urldecode($args[$i]);
    $args[$i] = mysqli_real_escape_string($link, $args[$i]);
}

//build the final query
$sql = vsprintf($sql, $args);

if ($debug) print $sql;

//execute and fetch the results
$result = mysqli_query($link, $sql);
if (mysqli_errno($link)==0 && $result) {

    $rows = array();

    if ($result!==true)
    while ($d = mysqli_fetch_assoc($result)) {
        array_push($rows,$d);
    }

    //return json
    return array('result'=>$rows);

} else {

    //error
    return array('error'=>'Database error');
}
}

Upvotes: 2

Views: 1029

Answers (1)

Moshe Katz
Moshe Katz

Reputation: 16926

Since you are using %d in your query, and there is no MySQL call on PHP that uses that format (MySQLi uses ? and PDO uses real names), I assume that your query function is using the sprintf function to build the SQL query. (UPDATE: I see from your update that I was right.)

According to the documentation for sprintf, a %d means (emphasis mine):

the argument is treated as an integer, and presented as a (signed) decimal number.

The correct format string for you to use would be %f:

the argument is treated as a float, and presented as a floating-point number (locale aware).

(or, if you do not want the input to be locale-aware, use %F instead.)

(In any case, if you are accepting this data from a user (or from a user-controlled device), you really should be using parameterized queries (docs for MySQLi, PDO), instead of rolling your own SQL query builder.)

Upvotes: 2

Related Questions