小贝子编程

成功更新数据，但在编码点火器中插入数据有错误(1264超出范围)



我已经编写了代码点火器文件以从csv上传 创建，更新客户，销售和收集，同时输入旧客户，它已成功更新，没有任何错误。如果CSV中的新客户已成功插入客户详细信息和销售详细信息，但无法插入集合详细信息并显示错误1264超出范围列"余额"。 但我在收藏中没有使用列余额。列余额出现在销售表中，但它已成功插入。 然后我将CSV文件导入服务器，所以我无法修改数据类型，这是我的编码

<?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->upload_get();
}
function upload2_get() {
$this->load->library('CSVReader');
$filepath="C:/wamp64/www/CI/data.csv";
$content = $this->csvreader->parse_file($filepath);
$this->db->trans_start();
foreach ($content as $c) {
//get group ID
$this->db->select('g.id');
$this->db->join('branch b',"g.branchid=b.id");
$this->db->from("group g");
$this->db->where('b.name', strtoupper($c['branch code']));
$this->db->where('g.name', strtoupper($c['group code']));
$query = $this->db->get();
$result = $query->row();
var_dump($this->db->last_query());
$groupID = $result->id;
//check if street exist
$this->db->where('name', $c['street name']);
$this->db->where('groupid', $groupID);
$query = $this->db->get('street');
$result = $query->row();
if ($result) {
$streetID = $result->id;
echo "<br>existing StreetID :" . $streetID;
} else {
//insert new street
$insertData = array('name' => $c['street name'],
'shiftid' => $c['line'],
'groupid' => $groupID);
$this->db->insert('street', $insertData);
$streetID = $this->db->insert_id();
echo "<br> created StreetID :" . $streetID;
}

//insert into customer 
$name=$c['customer code'] . " - " . $c['name'];
$this->db->where('name',$name);
$query=$this->db->get('customer');
$result = $query->row();
if($result)
{
$customerID= $result->id;
$insertData = array();
$insertData['balance'] = $c['balance'];
$insertData['creationdate'] = $c['creation date'];
$insertData['attendedBy'] = 2;
$this->db->where('id',$customerID);
$this->db->update('customer', $insertData);
echo "UPDated DATA";
print_r($insertData);
echo "<br>present customer :" . $customerID . "<br>";
}
else
{
$insertData = array('streetid' => $streetID,
'name' => $c['customer code'] . " - " . $c['name'],
'cycle' => $c['cycle']);
$insertData['balance'] = $c['balance'];
$this->db->insert('customer', $insertData);
var_dump($insertData);
$customerID = $this->db->insert_id();
echo "created customer :" . $customerID . "<br>";
}

//add sales
$this->db->where('customerid',$customerID);
echo "SALES DETAILS ARE";
$query=$this->db->get('sales');
$result=$query->row();
if($result)
{
$salesid=$result->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->rest->user_id;
//  $insertData['salesby'] = 2;
// $insertData['warehouseid'] = 1;
// var_dump($insertData);
$this->db->where('id',$salesid);
$this->db->update('sales', $insertData);
echo "UPDated DATA";
print_r($insertData);
echo "customer sales update :" . $salesid . "<br>";
}
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->rest->user_id;
$insertData['salesby'] = 2;
$insertData['warehouseid'] = 1;
var_dump($insertData);
$this->db->insert('sales', $insertData);
$salesid = $this->db->insert_id();
echo "customer sales create:" . $salesid . "<br>";
}

//add collection
$this->db->where('salesid',$salesid);
$query=$this->db->get('collection');
$result=$query->row();
if($result)
{
$collectionid=$result->id;
$insertData = array();
//$insertData['salesid'] = $salesid;
$data = $c['sales amount'] - $c['balance'];
$insertData['amount']=$data;
var_dump($insertData);
$this->db->where('id',$collectionid);
$this->db->update('collection', $insertData);
echo "customer collection update :" . $collectionid . "<br>";

}
else
{
/*  $insertData = array();
$insertData['salesid'] = $salesid;
$insertData['amount'] = $c['sales amount'] - $c['balance'];
// $insertData['collectedby'] = $this->rest->user_id;
$insertData['collectedby'] = 2;
var_dump($insertData);
$this->db->insert('collection', $insertData);
// echo "Created Collection :" . $this->db->insert_id() . " 
<br>";
$collectionid=$this->db->insert_id();
echo "Created Collection:" . $collectionid . "<br>";*/
$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->db->insert('collection', $insertData);
echo "Created Collection :" . $this->db->insert_id() . " 
<br>";
}
echo "completed customer:".$c['name'];
if ($this->db->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->db->trans_complete();
}

}
?>

我的输出与老客户成功更新详细信息，NUW客户错误是

existing StreetID :1188UPDated DATAArray ( [balance] => 100 
[creationdate] => 4/22/2018 [attendedBy] => 2 ) 
present customer :18116
SALES DETAILS AREUPDated DATAArray ( [productid] => 1081 [price] => 
1290 [due_unit] => 64.5 [maturity_date] => 9/16/2018 [creationdate] => 
4/22/2018 [balance] => 100 ) customer sales update :18116
C:wamp64wwwCIapplicationmodulesadmincontrollersImport2.php:142:
array (size=1)
'amount' => int 1190
customer collection update :13232
completed customer:DEVI VADIVEL RH

existing StreetID :1188
C:wamp64wwwCIapplicationmodulesadmincontrollersImport2.php:77:
array (size=4)
'streetid' => string '1188' (length=4)
'name' => string 'AA10 - RAM' (length=10)
'cycle' => string '2' (length=1)
'balance' => string '100' (length=3)
created customer :70603
SALES DETAILS ARE
C:wamp64wwwCIapplicationmodulesadmincontrollersImport2.php:123:
array (size=9)
'customerid' => int 70603
'productid' => int 1081
'price' => string '1290' (length=4)
'due_unit' => string '64.5' (length=4)
'maturity_date' => string '9/16/2018' (length=9)
'creationdate' => string '4/22/2018' (length=9)
'balance' => string '100' (length=3)
'salesby' => int 2
'warehouseid' => int 1
customer sales create:71025
C:wamp64wwwCIapplicationmodulesadmincontrollersImport2.php:167:
array (size=3)
'salesid' => int 71025
'amount' => int 1190
'collectedby' => 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

伙计们终于谢谢你了，我进行了正确的编码以在我的程序中插入csv详细信息，有一些错误并纠正了编码，我的编码是

<?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->upload_get();
}
function upload2_get() {
$this->load->library('CSVReader');
//  $filepath = "/var/www/html/data.csv";
$filepath="C:/wamp64/www/CI/data.csv";
$content = $this->csvreader->parse_file($filepath);
$this->db->trans_start();
foreach ($content as $c) {
//get group ID
$this->db->select('g.id');
$this->db->join('branch b',"g.branchid=b.id");
$this->db->from("group g");
$this->db->where('b.name', strtoupper($c['branch code']));
$this->db->where('g.name', strtoupper($c['group code']));
$query = $this->db->get();
$result = $query->row();
var_dump($this->db->last_query());
$groupID = $result->id;
//check if street exist
$this->db->where('name', $c['street name']);
$this->db->where('groupid', $groupID);
$query = $this->db->get('street');
$result = $query->row();
if ($result) {
$streetID = $result->id;
} else {
//insert new street
$insertData = array('name' => $c['street name'],
'shiftid' => $c['line'],
'groupid' => $groupID);
$this->db->insert('street', $insertData);
$streetID = $this->db->insert_id();
}
echo "<br>StreetID :" . $streetID;

//insert into customer 
$name=$c['customer code'] . " - " . $c['name'];
$this->db->where('name',$name);
$query=$this->db->get('customer');
$result = $query->row();
if($result)
{
$customerID= $result->id;
$insertData = array();
$insertData['balance'] = $c['balance'];
$insertData['creationdate'] = $c['creation date'];
$insertData['attendedBy'] = 2;
$this->db->where('id',$customerID);
$this->db->update('customer', $insertData);
echo "UPDated DATA";
print_r($insertData);
echo "<br>present customer :" . $customerID . "<br>";
}
else
{
$insertData = array('streetid' => $streetID,
'name' => $c['customer code'] . " - " . $c['name'],
'balance' => $c['balance'],
'cycle' => $c['cycle']);
$this->db->insert('customer', $insertData);
var_dump($insertData);
$customerID = $this->db->insert_id();
echo "created customer :" . $customerID . "<br>";
}

//add sales
$this->db->where('customerid',$customerID);
echo "SALES DETAILS ARE";
$query=$this->db->get('sales');
$result=$query->row();
if($result)
{
$salesid=$result->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->rest->user_id;
//  $insertData['salesby'] = 2;
// $insertData['warehouseid'] = 1;
// var_dump($insertData);
$this->db->where('id',$salesid);
$this->db->update('sales', $insertData);
echo "UPDate DATA";
print_r($insertData);
echo "customer sales update :" . $salesid . "<br>";
}
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'] = $insertData['price'];
$insertData['salesby'] =2;
$insertData['warehouseid'] = 1;
var_dump($insertData);
$this->db->insert('sales', $insertData);
$salesid = $this->db->insert_id();
echo "customer sales created :" . $salesid . "<br>";
}
//add collection
$this->db->where('salesid',$salesid);
$query=$this->db->get('collection');
$result=$query->row();
if($result)
{
$collectionid=$result->id;
$insertData = array();
//$insertData['salesid'] = $salesid;
$data = $c['sales amount'] - $c['balance'];
$insertData['amount']=$data;
var_dump($insertData);
$this->db->where('id',$collectionid);
$this->db->update('collection', $insertData);
echo "customer collection update :" . $collectionid . "<br>";

}
else
{
$insertData = array();
$insertData['salesid'] = $salesid;
$insertData['amount'] = $c['sales amount'] - $c['balance'];
$insertData['collectedby'] = 2;
var_dump($insertData);
if ($insertData['amount'] != 0) {
$this->db->insert('collection', $insertData);
echo "Created Collection create:" . $this->db->insert_id() . 
"<br>";
}
}
echo 'Completed Customer Details'.$c['name'];

if ($this->db->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->db->trans_complete();
}
}
?>

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium