The situation is as follows. I have a MYSQL database and an XLS file on an FTP server The code below downloads XLS to read and UPDATE from the database from the XLS data.
The problem is that it only updates the existing data, I need to check in the MYSQL database if all the XLS IDs exist and if they do not exist, make another type of query, giving insert with the data of this new XLS line. p>
Follow the code at link
You have two possible approaches: use the mysqli_affected_rows function or use the ON DUPLICATE KEY UPDATE of mysql.
mysqli_affected_rows
Basically, after each update, you check to see if any lines have been modified in the database. If it has been modified it is because the registry already existed in the database and has been updated. Otherwise, the record did not 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)');
}
In this approach, you need to have a unique field in your table (primary or unique key, for example), so that you try to insert it first and if it fails (the record already exists) you do the update. It's like the previous example, only the opposite and 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);