2015-06-04 18:06 阅读 25


For some odd reason, some database values do not match the datastream. It is not for every entry although. For instance a line from my CSV file is below.


And somehow in my DB the same entry reads


My code for dumping into the DB is below:

function dump($csvFile, $conn){
if(($handle = fopen($csvFile, 'r')) !== false) {

    $header = fgetcsv($handle);
    $id = 0;

    $sql = "TRUNCATE TABLE inv_price";
    $sth = $conn->prepare($sql);

    while(($data = fgetcsv($handle)) !== false) {
        $sql = "INSERT INTO `inv_price` (sku, part_status, msrp, curr_sugg_retail, your_price) 
                VALUES ('$id', '$data[0]', '$data[1]', '$data[2]', '$data[3]')";

    catch (PDOException $e) {
        print "Error!: " . $e->getMessage() . "<br/>";


The $data printed to the console matches the CSV file. I am not sure how or why certain rows are changing the value to 999.99.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    duanhongqiong9460 duanhongqiong9460 2015-06-04 18:21

    Most likely explanation for this behavior is that the column is defined as datatype DECIMAL(5,2), which allows for only three digits before the decimal point. And the error/warning is being ignored.

    The value being stored in the column the maximum value that can be stored in that datatype. We observe similar behavior with integer values as well.

    SQL Fiddle Here!2/a7577f/1

    create table foo (col52 DECIMAL(5,2), col72 decimal(7,2));
    insert ignore into foo (col52, col72) values (1234.56, 1234.56);
    select * from foo;
    col52   col72
    ------  -------
    999.99  1234.56

    On an entirely different note, the code appears to be vulnerable to SQL injection.

    Curiously, the TRUNCATE TABLE statement is a prepared statement, but the INSERT is not.

      $sql = 'INSERT INTO `inv_price` (sku, part_status, msrp, curr_sugg_retail, your_price) 
              VALUES (?, ?, ?, ?, ?)';
    点赞 评论 复制链接分享