PDO losing decimal precision when updating record

Question

I am trying to update a table in my database using an HTML form and PHP script, I am using pdo with prepared statements and named placeholders

here is the sql before execution:

UPDATE lctn_bt SET `BTlongitude` = :BTlongitude, `BTlatitude` = :BTlatitude, `BTaccuracy` = :BTaccuracy, `BTnotes` = :BTnotes, `ISShaft` = :ISShaft, `ClientID` = :ClientID WHERE BTid = 79

and here is the array i am feeding into the execute statement:

Array ( [:BTlongitude] => 144.9687500 [:BTlatitude] => -37.7452700 [:BTaccuracy] => 4 [:BTnotes] => Audit. To right of driveway [:ISShaft] => [:ClientID] => 0 )

and here is the code that builds the statment and executes it:

$sql4 = "UPDATE ".$table." SET ";

    foreach($values3 as $key => $string){
        if($fields[$key] !== $primaryID){
            $sql4 = $sql4."`".$fields[$key]."` = :".$fields[$key].", ";
            $prepared[':'.$fields[$key]] = $string;
            echo $fields[$key].': '.$string.'</br>';
        }
    }
    $sql4 = substr($sql4, 0, -2);
    $sql4 = $sql4." WHERE ".$primaryID." = ".$record;
    echo $sql4;
    $sql4 = $dbh->prepare($sql4);
    $out = $sql4->execute($prepared);

If I was to execute that statement with the array up top, in the values for "BTlatitude" and "BTlongitude" get rounded to 144.0000000 and -37.0000000 even though the 2 fields in the database are set up as "decimal(12,7)" and other records in the same table are storing full values. in fact, the values I am trying to store where originally read from that table.

Yet if I take the SQL statement and manually put the values in then run the SQL with phpMyAdmin it works fine and stores the decimal places.

This is doing my head in, the only other posts online I can find with the same issue stem from people trying to store decimal places in a decimal field that has no decimal places defined (eg. decimal(10,0))

The above array is a print_r($prepared); placed right after the last line ($out = $sql4->execute($prepared);) and shows the correct values.

If I run the following code the record is added successfully with all decimal places intact:

$prepared = array(':BTlongitude' => 144.9687500, ':BTlatitude' => -37.7452700, ':BTaccuracy' => 4, ':BTnotes' => "Audit. To right of driveway", ':ISShaft' => '',':ClientID' => 0, ':BTid' => 79);

$dbh = new PDO("mysql:host=$db->host;dbname=$db->db_name", $db->username, $db->password);

$sql = "UPDATE lctn_bt SET `BTlongitude` = :BTlongitude, `BTlatitude` = :BTlatitude, `BTaccuracy` = :BTaccuracy, `BTnotes` = :BTnotes, `ISShaft` = :ISShaft, `ClientID` = :ClientID WHERE BTid = :BTid";
$sql = $dbh->prepare($sql);
$out = $sql->execute($prepared);

the table is set up as so:

CREATE TABLE `lctn_bt` (
 `BTid` int(11) NOT NULL,
 `BTproperty` int(11) DEFAULT NULL,
 `BTlongitude` decimal(12,7) DEFAULT NULL,
 `BTlatitude` decimal(12,7) DEFAULT NULL,
 `BTaccuracy` int(11) DEFAULT NULL,
 `BTnotes` varchar(1000) DEFAULT NULL,
 `ISShaft` int(11) DEFAULT NULL,
 `ClientID` int(11) DEFAULT '0',
 `LastUpdated` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

You need to determine where the values are being rounded. Add `var_dump($values3)` before you loop and `var_dump($prepared)` after. Are the values rounded there or do they look correct? If you've already done that, then please explain it clearly in your question — Phil, Oct 09 '17 at 05:36
that array i posted above is a print_r($prepared) i have in the code after the the SQL is executed, to check that exact thing. it appears to be either at the $sql4->execute($prepared); or on the MySQL end. do you have any other hints? thanks for taking th time to have a look at my code :) — mike16889, Oct 09 '17 at 06:06
Please provide a [Minimal, Complete, and Verifiable example](https://phpdelusions.net/pdo/mcve) — Your Common Sense, Oct 09 '17 at 06:19
this works: `$prepared = array(':BTlongitude' => 144.9687500, ':BTlatitude' => -37.7452700, ':BTaccuracy' => 4, ':BTnotes' => "Audit. To right of driveway", ':ISShaft' => '',':ClientID' => 0, ':BTid' => 79); $dbh = new PDO("mysql:host=$db->host;dbname=$db->db_name", $db->username, $db->password); $sql = "UPDATE lctn_bt SET 'BTlongitude' = :BTlongitude, 'BTlatitude' = :BTlatitude, 'BTaccuracy' = :BTaccuracy, 'BTnotes' = :BTnotes, 'ISShaft' = :ISShaft, 'ClientID' = :ClientID WHERE BTid = :BTid"; $sql = $dbh->prepare($sql); $out = $sql->execute($prepared);` i cant see where my code is wrong — mike16889, Oct 09 '17 at 07:12
**Please read the article linked above** and provide a **verifiable** example, **thank you** — Your Common Sense, Oct 09 '17 at 07:46
i have read the artical and am unable to reproduce the error outside my main code. — mike16889, Oct 09 '17 at 09:13
If you cannot reproduce it, then your question doesn't fit for Stack Overflow, sorry for that. — Your Common Sense, Oct 09 '17 at 09:20
If the `print_r` output is to be believed, the only difference I can see between the *working* and *not working* code is that you are binding the `BTid` and the table is hard coded instead of concatenated in via `$table`. Are you sure you're looking at the right database, table, row, etc? — Phil, Oct 09 '17 at 23:41

score 0 · Accepted Answer · answered Oct 09 '17 at 23:41

0

Turns out it was an issue with casting. I was using:

$somevalue = htmlentities($_POST'somevalue'], ENT_HTML5, 'UTF-8');

further up in the code, i think it was converting the decimal place to the html entity equivalent.

i swapped to

$somevalue = htmlspecialchars($_POST'somevalue'], ENT_HTML5, 'UTF-8');

and then down where i built the array of values for pdo i checked for numbers and forced the type cast to floats with the following code:

if(is_numeric($string)){
    $prepared[':'.$fields[$key]] = floatval($string);
} else {
    $prepared[':'.$fields[$key]] = $string;
}

and that seems to have done the trick.

answered Oct 09 '17 at 23:41

mike16889

322
3
11

You should **never** encode values for storage. Encoding is for display. See https://stackoverflow.com/questions/38411112/using-htmlspecialchars-function-with-pdo-prepare-and-execute – Phil Oct 09 '17 at 23:43
Also, try not to debug via HTML documents. Preferably use an actual debugger or, if you must dump variables, do so as plain text. – Phil Oct 09 '17 at 23:48

PDO losing decimal precision when updating record

1 Answers1