Today i came across a functionality where i need to import the CSV/Excel file in to MYSQL database via codeigniter. There is a special requirement from client where he can upload the CSV/Excel file in file upload field in HTML form and all data from CSV/Excel must import into MYSQL database table through PHP and remove the duplicates rows of data.But I faced some problems here.
My problems is:
- when the user upload the csv file,its enter into the MYSQL database,but it have duplicates data of about 3-4 row.
- the data in csv file is about 8000 rows but the data insert in MYSQL db is only about 5000(included the duplicates data)
this error occur when the file stop import the data
This is my model
<?php
class Upload_services extends CI_Model{
function __construct(){
parent::__construct();
}
function upload_sampledata_csv(){
if(isset($_POST['submit'])){
$fp = fopen($_FILES['userfile']['tmp_name'],'r') or die("can't open file");
while(($line = fgetcsv($fp)) !== FALSE){
//check whether there are duplicate rows of data in database
$prevQuery = array(
'articleno'=> $line[0] ,
'product_description' => $line[1] ,
'cust_name' => $line[2] ,
'size' => $line[3] ,
'colour' => $line[4],
'process_description' => $line[5],
'output' => $line[6],
'material_part' => $line[7],
'printingOutput' => $line[8]
);
$q=$this->db->select('sindi_productprocess_temp', $prevQuery)
->where('articleno',$line[0],
'product_description', $line[1] ,
'cust_name' , $line[2] ,
'size', $line[3] ,
'colour' , $line[4],
'process_description' , $line[5],
'output', $line[6],
'material_part', $line[7],
'printingOutput', $line[8]);
$prevResult = $this -> db->query($q);
if($prevResult->num_rows > 0){
//update process data
$data = array(
'articleno' => $line[0] ,
'product_description' => $line[1] ,
'cust_name' => $line[2] ,
'size' => $line[3] ,
'colour' => $line[4],
'process_description' => $line[5],
'output' => $line[6],
'material_part' => $line[7],
'printingOutput' => $line[8]
);
$this->db->set
(
'articleno',$line[0],
'product_description', $line[1] ,
'cust_name' , $line[2] ,
'size', $line[3] ,
'colour' , $line[4],
'process_description' , $line[5],
'output', $line[6],
'material_part', $line[7],
'printingOutput', $line[8]
);
$this->db-where('articleno',$line[0],
'product_description', $line[1] ,
'cust_name' , $line[2] ,
'size', $line[3] ,
'colour' , $line[4],
'process_description' , $line[5],
'output', $line[6],
'material_part', $line[7],
'printingOutput', $line[8]
);
$this->db->update('sindi_productprocess_temp');
}else{
for($i = 0, $j = count($line); $i < $j; $i++){
$data = array('articleno' => $line[0] ,
'product_description' => $line[1] ,
'cust_name' => $line[2] ,
'size' => $line[3] ,
'colour' => $line[4],
'process_description' => $line[5],
'output' => $line[6],
'material_part' => $line[7],
'printingOutput' => $line[8]
);
$data['crane_features']=$this->db->insert('sindi_productprocess_temp', $data);
}
$i++;
}
}
fclose($fp) or die("can't close file");
}
}
function get_car_features_info(){
$get_cardetails=$this->db->query("select * from sindi_productprocess_temp");
return $get_cardetails;
}
}
?>
This is my controller
function processupload(){
$this->load->model('upload_services');
$data['result']=$this->upload_services->upload_sampledata_csv();
$data['query']=$this-> upload_services->get_car_features_info();
$this->load->view(' Upload_csv ',$data);
}
And this is my view
<form action="{$path}pro/processupload" method="post" enctype="multipart/form-data" name="form1" id="form1">
<table>
<tr>
<td> Choose your file: </td>
<td><input type="file" class="form-control" name="userfile" id="userfile" align="center"/></td>
<td><div class="modal-footer">
<td colspan="2" ><input type="submit" id="submit" name="submit" value="Import"></td>
</div>
</td>
</tr>
</table>
Can anybody please help me on this?? Thank you.
Advertisement
Answer
There were three mistakes:
- You are passing multiple params to WHERE, you was passing it wrong
- When we use SELECT and WHERE use use
get()instead ofquery(), we use query when we write complete custom query. - you were using
for($i = 0, $j = count($line); $i < $j; $i++)in else condition which is adding duplicate records
I also added two comments:
- To remove header from your CSV (as in you code loop first iteration is checking headers only)
- Instead of indexed array it better to use associative array
Below is the working complete code of your model:
<?php
class Upload_services extends CI_Model
{
function __construct()
{
parent::__construct();
}
function upload_sampledata_csv()
{
if(isset($_POST['submit'])){
$fp = fopen($_FILES['userfile']['tmp_name'],'r') or die("can't open file");
//fgetcsv($fp, 1000, ",");// to remove header from CSV
/*** If you want associated Array *****/
/*
$csv = array_map("str_getcsv", file($_FILES['userfile']['tmp_name'],FILE_SKIP_EMPTY_LINES));
$keys = array_shift($csv);
//To turn all the rows into a nice associative array you could then apply:
foreach ($csv as $i=>$row) {
$csv[$i] = array_combine($keys, $row);
}
*/
while(($line = fgetcsv($fp)) !== FALSE)
{
//check whether there are duplicate rows of data in database
$prevQuery = array(
'articleno'=> $line[0] ,
'product_description' => $line[1] ,
'cust_name' => $line[2] ,
'size' => $line[3] ,
'colour' => $line[4],
'process_description' => $line[5],
'output' => $line[6],
'material_part' => $line[7],
'printingOutput' => $line[8]
);
$where = array('articleno' => $line[0],
'product_description' => $line[1] ,
'cust_name' => $line[2] ,
'size' => $line[3] ,
'colour' => $line[4],
'process_description' => $line[5],
'output' => $line[6],
'material_part' => $line[7],
'printingOutput' => $line[8]);
$q = $this->db->select("*")
->where($where)
->from('sindi_productprocess_temp');
$prevResult = $this->db->get();
if($prevResult->num_rows > 0){
//update process data
$data = array(
'articleno' => $line[0] ,
'product_description' => $line[1] ,
'cust_name' => $line[2] ,
'size' => $line[3] ,
'colour' => $line[4],
'process_description' => $line[5],
'output' => $line[6],
'material_part' => $line[7],
'printingOutput' => $line[8]
);
$this->db->set
(
'articleno',$line[0],
'product_description', $line[1] ,
'cust_name' , $line[2] ,
'size', $line[3] ,
'colour' , $line[4],
'process_description' , $line[5],
'output', $line[6],
'material_part', $line[7],
'printingOutput', $line[8]
);
$this->db-where
(
'articleno',$line[0],
'product_description', $line[1] ,
'cust_name' , $line[2] ,
'size', $line[3] ,
'colour' , $line[4],
'process_description' , $line[5],
'output', $line[6],
'material_part', $line[7],
'printingOutput', $line[8]
);
$this->db->update('sindi_productprocess_temp');
}else{
$data = array(
'articleno' => $line[0] ,
'product_description' => $line[1] ,
'cust_name' => $line[2] ,
'size' => $line[3] ,
'colour' => $line[4],
'process_description' => $line[5],
'output' => $line[6],
'material_part' => $line[7],
'printingOutput' => $line[8]
);
$data['crane_features']=$this->db->insert('sindi_productprocess_temp', $data);
}
}
fclose($fp) or die("can't close file");
}
}
function get_car_features_info()
{
$get_cardetails=$this->db->query("select * from sindi_productprocess_temp");
return $get_cardetails;
}
}
?>