Problem using INSERT INTO ON DUPLICATE KEY UPDATE

0

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.

    
asked by Alejo Mendoza 29.07.2018 в 18:47
source

1 answer

0

The problem is that you have deleted tuples from your table, then the id continues to increase by the last tuple you entered and not the last one existing. You should take the id from the last inserted tuple and increase it by one. And that value give it to the id of the new tuple.

    
answered by 29.07.2018 в 20:04