Sending decimal values from JQuery Ajax to PHP results in unwanted rounding

Question

I am sending data in the form of decimal values using JQuery to a PHP file. The PHP file in turn inserts these values into a MySQL database in which the two columns lat,lng are of type decimal. It seems that the PHP script, particularly the $_POST variable, undesirably rounds the values. My question is, how do I receive the decimal values in the PHP file without rounding them, so that I can send the unrounded values to the database?

For example: Running the below code resulted in a database entry of 33,57 instead of the expected 32.58285,57.38373.

sendMarkerData.js:

$(document).ready(function(){
    $.ajax({
        url:'sendMarkerData.php',
        method:'POST',
        data:{
            lat:32.58285,
            lng:57.38373 // sample lat, lng values
        },
        success:function(data){
            console.log(data);
        }
    })

sendMarkerData.php:

<?php

include ('database_connect.php');

$lat = $_POST['lat']; 
$lng = $_POST['lng']; // apparently these two lines round lat and lng to integers

$sqlSend = "INSERT INTO `markers` (`lat`, `lng`) VALUES ($lat, $lng)";

if ($conn->query($sqlSend) === TRUE) {
    echo "success";
}
else {
    echo "fail";
}

$conn->close();

?>

First, I would pass the values as strings: `data: {lat: '32.58285', lng: '57.38373'}` just to bypass JavaScript floating point processing. And you should be using *prepared statements* to prevent SQL Injection attacks. But if you end up with 33,57 it sounds like your columns are *int* types rather than *decimal* or *float* types. You probably want the columns to be `DECIMAL(8, 5)`. — Booboo, Sep 12 '21 at 17:11
@Booboo Thank you, although the columns are of type decimal I realized that decimal(10,0) means that it'll round to integer values as there are zero decimal places. I'll also look into preventing SQL Injection attacks once I get this sorted out. Appreciate it! — Colin Davis, Sep 12 '21 at 17:14
But heed my comment about passing strings or the floating point conversions done by JavaScript could possibly affect the value. Also the comment about using a prepared statement. — Booboo, Sep 12 '21 at 17:16
Why you tag the question as `mysql`, but then omit the relevant table? — Martin Zeitler, Sep 12 '21 at 18:45
Does this answer your question? [What MySQL data type should be used for Latitude/Longitude with 8 decimal places?](https://stackoverflow.com/questions/12504208/what-mysql-data-type-should-be-used-for-latitude-longitude-with-8-decimal-places) — Martin Zeitler, Sep 12 '21 at 19:03
@MartinZeitler I had the data type set to decimal(10, 0) and didn't understand what that meant. I changed it to allow decimal places which fixed the issues. thank you — Colin Davis, Sep 12 '21 at 19:07
@ColinDavis Please search before asking ...I know, that one needs to know the keywords to do so... but in general, this site knows an awful lot, when knowing what to search for. — Martin Zeitler, Sep 12 '21 at 19:09
@MartinZeitler I did search extensively for the answer but failed to use the right keywords — Colin Davis, Sep 13 '21 at 14:38

score 1 · Accepted Answer · answered Sep 12 '21 at 17:15

this is not a jQuery or PHP error , PHP page receive the actual value you sent through jQuery. the round after two digit is database type setting, just go to database table structure and change column type to decimal and in length/values enter 18,10 or 18,12 means number of digit you want to enter after first dot.

Sending decimal values from JQuery Ajax to PHP results in unwanted rounding

1 Answers1