title: UPDATE DATA SUCCESSFULLY, BUT INSERT DATA HAVE ERROR(1264 out of range) in codeigniter
body: <p>i have write codeigniter file to upload from csv create,update customer,sales and collection while enter old customer it was update successfully not any error.if new customer in csv it was insert customer details and sales details successfully,but it cannt insert collection details and show error 1264 out of range column 'balance'. but i doesnt use column balance in collection.column balance was presents in sales table but it was insert successfully. then i import the csv file to server so i cannt modify datatype here is my coding is</p>
  
  <pre><code>&lt;?php
   defined('BASEPATH') OR exit('No direct script access allowed');
   require APPPATH . '/libraries/REST_Controller.php';
  
   class Import2 extends MX_Controller {
  
  function __construct() {
      parent::__construct();
  }
  function index()
  {
  $this-&gt;upload_get();
  }
   function upload2_get() {
      $this-&gt;load-&gt;library('CSVReader');
       $filepath="C:/wamp64/www/CI/data.csv";
      $content = $this-&gt;csvreader-&gt;parse_file($filepath);
      $this-&gt;db-&gt;trans_start();
      foreach ($content as $c) {
  
          //get group ID
          $this-&gt;db-&gt;select('g.id');
          $this-&gt;db-&gt;join('branch b',"g.branchid=b.id");
          $this-&gt;db-&gt;from("group g");
          $this-&gt;db-&gt;where('b.name', strtoupper($c['branch code']));
          $this-&gt;db-&gt;where('g.name', strtoupper($c['group code']));
          $query = $this-&gt;db-&gt;get();
          $result = $query-&gt;row();
          var_dump($this-&gt;db-&gt;last_query());
          $groupID = $result-&gt;id;
  
          //check if street exist
          $this-&gt;db-&gt;where('name', $c['street name']);
          $this-&gt;db-&gt;where('groupid', $groupID);
          $query = $this-&gt;db-&gt;get('street');
          $result = $query-&gt;row();
          if ($result) {
              $streetID = $result-&gt;id;
               echo "&lt;br&gt;existing StreetID :" . $streetID;
          } else {
              //insert new street
              $insertData = array('name' =&gt; $c['street name'],
                  'shiftid' =&gt; $c['line'],
                  'groupid' =&gt; $groupID);
              $this-&gt;db-&gt;insert('street', $insertData);
              $streetID = $this-&gt;db-&gt;insert_id();
               echo "&lt;br&gt; created StreetID :" . $streetID;
          }
  
  
  
          //insert into customer 
         $name=$c['customer code'] . " - " . $c['name'];
          $this-&gt;db-&gt;where('name',$name);
          $query=$this-&gt;db-&gt;get('customer');
          $result = $query-&gt;row();
          if($result)
          {
              $customerID= $result-&gt;id;
               $insertData = array();
                $insertData['balance'] = $c['balance'];
                $insertData['creationdate'] = $c['creation date'];
                $insertData['attendedBy'] = 2;
                   $this-&gt;db-&gt;where('id',$customerID);
               $this-&gt;db-&gt;update('customer', $insertData);
                echo "UPDated DATA";
          print_r($insertData);
              echo "&lt;br&gt;present customer :" . $customerID . "&lt;br&gt;";
          }
          else
          {
          $insertData = array('streetid' =&gt; $streetID,
              'name' =&gt; $c['customer code'] . " - " . $c['name'],
              'cycle' =&gt; $c['cycle']);
          $insertData['balance'] = $c['balance'];
          $this-&gt;db-&gt;insert('customer', $insertData);
          var_dump($insertData);
          $customerID = $this-&gt;db-&gt;insert_id();
          echo "created customer :" . $customerID . "&lt;br&gt;";
      }
  
  
  
          //add sales
          $this-&gt;db-&gt;where('customerid',$customerID);
          echo "SALES DETAILS ARE";
          $query=$this-&gt;db-&gt;get('sales');
          $result=$query-&gt;row();
          if($result)
          {
              $salesid=$result-&gt;id;
          $insertData = array();
         // $insertData['customerid'] = $customerID;
          $insertData['productid'] = 1081;
          $insertData['price'] = $c['sales amount'];
          $insertData['due_unit'] = $c['due unit'];
          $insertData['maturity_date'] = $c['mature date'];
          $insertData['creationdate'] = $c['creation date'];
          $insertData['balance'] = $c['balance'];
         // $insertData['balance'] = $insertData['price'];
          //$insertData['salesby'] = $this-&gt;rest-&gt;user_id;
        //  $insertData['salesby'] = 2;
         // $insertData['warehouseid'] = 1;
         // var_dump($insertData);
          $this-&gt;db-&gt;where('id',$salesid);
          $this-&gt;db-&gt;update('sales', $insertData);
          echo "UPDated DATA";
          print_r($insertData);
          echo "customer sales update :" . $salesid . "&lt;br&gt;";
      }
      else
      { $insertData = array();
          $insertData['customerid'] = $customerID;
          $insertData['productid'] = 1081;
          $insertData['price'] = $c['sales amount'];
          $insertData['due_unit'] = $c['due unit'];
          $insertData['maturity_date'] = $c['mature date'];
          $insertData['creationdate'] = $c['creation date'];
          $insertData['balance'] = $c['balance'];
          // $insertData['salesby'] = $this-&gt;rest-&gt;user_id;
          $insertData['salesby'] = 2;
          $insertData['warehouseid'] = 1;
          var_dump($insertData);
          $this-&gt;db-&gt;insert('sales', $insertData);
          $salesid = $this-&gt;db-&gt;insert_id();
          echo "customer sales create:" . $salesid . "&lt;br&gt;";
      }
  
  
  
          //add collection
          $this-&gt;db-&gt;where('salesid',$salesid);
          $query=$this-&gt;db-&gt;get('collection');
          $result=$query-&gt;row();
          if($result)
          {
              $collectionid=$result-&gt;id;
              $insertData = array();
             //$insertData['salesid'] = $salesid;
             $data = $c['sales amount'] - $c['balance'];
             $insertData['amount']=$data;
             var_dump($insertData);
              $this-&gt;db-&gt;where('id',$collectionid);
            $this-&gt;db-&gt;update('collection', $insertData);
            echo "customer collection update :" . $collectionid . "&lt;br&gt;";
  
  
          }
          else
          {
        /*  $insertData = array();
          $insertData['salesid'] = $salesid;
          $insertData['amount'] = $c['sales amount'] - $c['balance'];
         // $insertData['collectedby'] = $this-&gt;rest-&gt;user_id;
           $insertData['collectedby'] = 2;
          var_dump($insertData);
              $this-&gt;db-&gt;insert('collection', $insertData);
              // echo "Created Collection :" . $this-&gt;db-&gt;insert_id() . " 
      &lt;br&gt;";
              $collectionid=$this-&gt;db-&gt;insert_id();
               echo "Created Collection:" . $collectionid . "&lt;br&gt;";*/
               $insertData = array();
          $insertData['salesid'] = $salesid;
          $data = $c['sales amount'] - $c['balance'];
             $insertData['amount']=$data;
         // $insertData['amount'] = $c['sales amount'] - $c['balance'];
          $insertData['collectedby'] = 2;
          var_dump($insertData);
              $this-&gt;db-&gt;insert('collection', $insertData);
              echo "Created Collection :" . $this-&gt;db-&gt;insert_id() . " 
        &lt;br&gt;";
          }
           echo "completed customer:".$c['name'];
  
          if ($this-&gt;db-&gt;trans_status() === FALSE) {
              // generate an error... or use the log_message() function to 
    log your error
              echo "unable to process customer " . $c['Customer Account 
    Number'];
          }
      }//end of for each
      $this-&gt;db-&gt;trans_complete();
    }
  
  
    }
    ?&gt;
  </code></pre>
  
  <p>and my output with old customer successfully update details and nuw customer error is</p>
  
  <pre><code>  existing StreetID :1188UPDated DATAArray ( [balance] =&gt; 100 
    [creationdate] =&gt; 4/22/2018 [attendedBy] =&gt; 2 ) 
    present customer :18116
    SALES DETAILS AREUPDated DATAArray ( [productid] =&gt; 1081 [price] =&gt; 
     1290 [due_unit] =&gt; 64.5 [maturity_date] =&gt; 9/16/2018 [creationdate] =&gt; 
    4/22/2018 [balance] =&gt; 100 ) customer sales update :18116
    C:\wamp64\www\CI\application\modules\admin\controllers\Import2.php:142:
   array (size=1)
    'amount' =&gt; int 1190
    customer collection update :13232
     completed customer:DEVI VADIVEL RH
  
  
  
      existing StreetID :1188
    C:\wamp64\www\CI\application\modules\admin\controllers\Import2.php:77:
      array (size=4)
    'streetid' =&gt; string '1188' (length=4)
    'name' =&gt; string 'AA10 - RAM' (length=10)
    'cycle' =&gt; string '2' (length=1)
  'balance' =&gt; string '100' (length=3)
    created customer :70603
    SALES DETAILS ARE
   C:\wamp64\www\CI\application\modules\admin\controllers\Import2.php:123:
    array (size=9)
    'customerid' =&gt; int 70603
     'productid' =&gt; int 1081
    'price' =&gt; string '1290' (length=4)
   'due_unit' =&gt; string '64.5' (length=4)
   'maturity_date' =&gt; string '9/16/2018' (length=9)
   'creationdate' =&gt; string '4/22/2018' (length=9)
    'balance' =&gt; string '100' (length=3)
    'salesby' =&gt; int 2
    'warehouseid' =&gt; int 1
   customer sales create:71025
    C:\wamp64\www\CI\application\modules\admin\controllers\Import2.php:167:
   array (size=3)
  'salesid' =&gt; int 71025
  'amount' =&gt; int 1190
   'collectedby' =&gt; int 2
  A Database Error Occurred
   Error Number: 1264
  
  Out of range value for column 'balance' at row 1
  
    INSERT INTO `collection` (`salesid`, `amount`, `collectedby`) VALUES 
    (71025, 1190, 2)
  
     Filename: C:/wamp64/www/CI/system/database/DB_driver.php
  
     Line Number: 691
  </code></pre>