MySQL rounds decimal values when inserted with php script but not when inserted from phpMyAdmin

Question

The environment this is running in is as follows. It's an instance of Server2Go on a thumb drive.

Apache/2.2.15 (Win32),
PHP/5.3.2,
SQLite 2,
MySQL 5.1.46-community,
 and Perl 5.8.

The php script opens a file and loops through it line by line. A query is built and executed with the information in each line of the file.

The table structure is as follows:

CREATE TABLE `exp_report` (
  `b_unit` varchar(11) DEFAULT NULL,            //IMPORTANT TO THIS PROBLEM
  `b_unit_title` varchar(255) DEFAULT NULL,
  `act_code` varchar(11) DEFAULT NULL,
  `act_title` varchar(255) DEFAULT NULL,
  `adopted_bgt` varchar(20) DEFAULT NULL,
  `amended_bgt` varchar(20) DEFAULT NULL,
  `encumb` varchar(20) DEFAULT NULL,
  `ytd_exp` float(14,2) DEFAULT NULL,           //IMPORTANT TO THIS PROBLEM
  `encumb_ytdexp` float(14,2) DEFAULT NULL,     //IMPORTANT TO THIS PROBLEM
  `available_bgt` varchar(20) DEFAULT NULL,
  `percent` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

When the script is executed I echo the query statement that is generated before and after the query is executed in the php script. This is to ensure nothing strange is happening to the data in PHP.

Example output:

INSERT INTO exp_report (b_unit, b_unit_title, act_code, act_title, adopted_bgt, 
                        amended_bgt, encumb, ytd_exp, encumb_ytdexp, available_bgt,
                        percent
                       )
   VALUES('01000101', 'COUNCIL', '414000', 'SALARIES & WAGES', '259500','214500',
          '0', '2', '209296.72', '5203.28', '0.97574228'
         ); 

The Result in the database looks like this:

010001 | COUNCIL | 414000 | SALARIES & WAGES | 259500 | 214500 | 0 | 2.00 | 2.00 | 5203.28 | 0.97574228

Please note the first field and the last 4 fields of the above data.

The first field is b_unit varchar(11)` and the program attempts to insert 01000101 but the last two digits are cut off when it is stored in the database.

The next two fields are ytd_exp float(14,2) and encumb_ytdexp float(14,2). I attempt to insert '209296.72' in encumb_ytdexp and it is converted into 2.00.

The remaining two fields are varchars and they store the information properly.

If I copy the query that is echoed to the browser and run it within phpMyAdmin the data is stored properly and it looks exactly like what was passed through in the query statement.

I can't seem to figure out what is causing this behavior.

Any advice is appreciated.

Code for the php script:

<?php 
include('includes\db_fns.php');

$filename = "d2013expRep.xls";

$lines = file($filename);



foreach($lines as $key=>$current){
    $contents = explode("\t",$current);
    if($key=="0"){
        continue;
    }
    $actr = 0;
    //var_dump($contents);
    foreach($contents as $key2=>$c2){
        $la = trim($c2);

        if($actr==0){
            $finout .= "'".$la."'"; 
        }else{
            if($key2=="7"){
                $la = (float) $la;
                $finout .= ",'".$la."'";
            }else{
                $finout .= ",'".$la."'";
            }
        }

        $actr++;    
        unset($la);
    }

    $query = "INSERT INTO exp_report (b_unit,b_unit_title,act_code,act_title,adopted_bgt,amended_bgt,encumb,ytd_exp,encumb_ytdexp,available_bgt,percent) VALUES($finout); ";
    //echo($query."<BR><BR><BR>");
    $result =  mysqli_query($dc2a,$query) or trigger_error("Failed Query: " . mysqli_error($dc2a)); 
    $finout .= "\r\n";
    echo $query."<BR><BR><BR>";
    unset($finout);
    unset($actr);
}

//$query = "LOAD DATA LOCAL INFILE '/Library/WebServer/Documents/".$filename."' INTO TABLE exp_report FIELDS TERMINATED BY '\s\t' LINES TERMINATED BY '\n' IGNORE 1 LINES ";
//$result = mysqli_query($dc2a,$query) or trigger_error("Failed Query: " . mysqli_error($dc2a)); 
//echo $query;


?>

I have attempted all of respondents suggestions to no avail. Perhaps these shots will help shed some light on this question.

The data is inserted into the database once the script is executed. I'm using text/strings as opposed to numbers/decimals/floats. Now the database is telling me records do not exist when I know they do.

When I execute a query using like the result is an empty set. The field I am searching is setup as a text field.

Does this make sense to anyone or am I chasing a ghost?

Answer 1

Hard to see what's wrong from what you posted

Ensure the read file is a properly tab separated text file: maybe I'm wrong but from the extension .xls it seems you're trying to read a native Excel binary file (when I save from Excel as tab-separated values I get .txt as extension but I'm on Mac...)

I rewrote your code to be more clear

Also (that may fix the issue) I added 3 things:

1. every value is escaped before inserting into the values string. This will save you if the value contains a single quote, for example

2. I enclosed the field' names between backticks to avoid potential mysql reserved keywords conflicts.

EDIT:

3. It seem you're parsing a UTF-16 file. After reading the line it's converted to UTF8.

Hope this helps

<?php 
include('includes\db_fns.php');

$filename = "d2013expRep.xls"; // <--- make sure you're importing properly tab separated text data (are you importing an xls file??)

$lines = file($filename);

foreach($lines as $lineNum => $line) {
    // It seem you're working with a UTF-16 text file: convert to UTF8
    $lineUTF8 = mb_convert_encoding( $line, "UTF-8", "UTF-16LE" );
    $contents = explode( "\t", $lineUTF8 );
    if($lineNum==0) {
        continue;
    }

    $queryValues = "";

    foreach( $contents as $valueNum => $value ) {
        // good practice to escape the string
        $queryValues .= ( $valueNum == 0 ? "" : ", " ) . "'" . mysql_escape_string( trim( $value ) ) . "'"; 
    }

    // backtick field names to avoid reserved keywords conflicts
    $query = "INSERT INTO `exp_report` ( `b_unit`,
                                         `b_unit_title`, 
                                         `act_code`, 
                                         `act_title`, 
                                         `adopted_bgt`, 
                                         `amended_bgt`, 
                                         `encumb`, 
                                         `ytd_exp`, 
                                         `encumb_ytdexp`, 
                                         `available_bgt`, 
                                         `percent` ) VALUES( $queryValues ); ";

    $result =  mysqli_query($dc2a,$query) or trigger_error("Failed Query: " . mysqli_error($dc2a)); 

    // echo $query . "<br /><br />";
}

?>