I have a problem using the INSERT INTO ON DUPLICATE KEY UPDATE statement which I use to upload an excel with a list of products which must be updated daily, and the sentence I use to update the ones that already exist and insert the new ones products that are in the excel. The two functions are performed correctly, the problem is that the value inserted in the id field of my table to have new products in the excel does not match. Here is a view of my table:
The products from id 51 to 54 were the ones that I update, that's all right
The product with id 109 was the one that inserted new. The problem is that you should insert the product with id 55 and not with id 109.
The only thing that occurs to me is that it's like I'm adding up the 54 previous ids and doing the increment from there.
The code I use is this:
<?php
require 'PHPExcel/Classes/PHPExcel/IOFactory.php';
require 'conexion.php';
header("Content-Type: text/html;charset=utf-8");
set_time_limit(1000);
$nombreArchivo = 'lista.xlsx';
$objPHPExcel = PHPEXCEL_IOFactory::load($nombreArchivo);
$objPHPExcel->setActiveSheetIndex(0);
$numRows = $objPHPExcel->setActiveSheetIndex(0)->getHighestRow();
echo '<center><table border=1><tr><td>zip</td><td>city</td></tr>';
for($i = 13; $i <= $numRows; $i++){
$cod = $objPHPExcel->getActiveSheet()->getCell('A'.$i)->getCalculatedValue();
$descri = $objPHPExcel->getActiveSheet()->getCell('B'.$i)->getCalculatedValue();
$uxc = $objPHPExcel->getActiveSheet()->getCell('C'.$i)->getCalculatedValue();
$uni = $objPHPExcel->getActiveSheet()->getCell('D'.$i)->getCalculatedValue();
$price = $objPHPExcel->getActiveSheet()->getCell('E'.$i)->getCalculatedValue();
echo '<tr>';
echo '<td>'.$cod.'</td>';
echo '<td>'.$descri.'</td>';
echo '<td>'.$uxc.'</td>';
echo '<td>'.$uni.'</td>';
echo '<td>'.$price.'</td>';
echo '</tr>';
$sql = "INSERT INTO productos(codigo,descripcion,uxc,uni)VALUES('$cod','".utf8_decode($descri)."','$uxc','$uni')
ON DUPLICATE KEY UPDATE codigo = '".$cod."',descripcion = '".$descri."',uxc = '".$uxc."',uni = '".$uni."' ";
$result = $mysqli->query($sql);
}
echo mysqli_error($mysqli);
echo '</table></center>';
I really do not understand why this happens, thanks for the help.