PDO loosing decimal precision when updateing record

i am trying to update a table in my database using a 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))

0 Comment



Captcha image