Synchronize Mysql database with XLS

Question

The situation is as follows. I have a MYSQL database and XLS file on an FTP server The code below downloads the XLS to read and update dos in the database from the XLS data.

The problem is that it updates only the existing data, I need to check in the MYSQL database if all XLS ID’s exist and if they do not exist do another type of query, giving Insert with the data of this new XLS line.

Follows the code in https://paste.ofcode.org/Vjt78dH5vwCLfDvQf3EbQq

Answer 1

You have two possible approaches: use the function mysqli_affected_rows or use the clause ON DUPLICATE KEY UPDATE mysql.

Using the function mysqli_affected_rows

Basically, after each update, you check if any lines have been modified in the database. If it has been modified it is because the registration already existed in the bank and has been updated. Otherwise, the record didn’t exist in the bank and you can do an Insert. You just need to check this with an if. Something like this:

// ALTERA NOME e DESCRIÇÃO
mysqli_query($conexao, "UPDATE ps_product_lang SET name = '$name',
description_short = '$desc_short' WHERE id_product = '$id_product'");

//nenhum registro foi alterado no banco, então faz um insert
if(mysqli_affected_rows($conexao) < 1){
    //faça o insert
    mysqli_query($conexao, 'insert into nome_tabela (campos) 
    values (valores)');
}

Using the ON DUPLICATE KEY UPDATE clause

In this approach, you need to have a unique field in your table (primary key or Unique, for example), so that you try to do Insert first and if it fails (the record already exists) you do the update. It’s like the previous example, only the opposite is done in sql. Example:

mysqli_query($conexao, 'INSERT INTO nome_tabela (campo1, campo2,
campo3) VALUES (valor1, valor2, valor3) ON DUPLICATE KEY UPDATE 
campo2=valor2, campo3=valor3 WHERE campo1=valor1);