How to batch update with PHP and MySQL

Question

How to batch update with PHP and MySQL

Navigation

#1 by (2 votes)

1

I have a .csv file with two columns, one with id and another with status of my product, I need to change these products to status 0 (zero), that is, to inactivate them , but the quantity of products exceeds 400 records, how can I read this file and perform a batch update? I could not make anything that worked.

I made a code following a tip, it looked like this:

        // ARQUIVO NO SERVIDOR
    $arquivo = 'c:\inativos.txt';

    // ARRAY QUE RECEBERÁ DADOS IMPORTADOS
    $arquivoArr = array();

    // SOMENTE LEITURA
    $arq = fopen($arquivo, 'r');

    $i = 0;
    while(!feof($arq)){

            $conteudo = fgets($arq);
            $arquivoArr[$i] = $conteudo;                
            $i++;
    }


  // CONEXÃO COM BD
  require_once('Connections/conexao.php');     

  foreach($arquivoArr as $linha):

        mysql_select_db($database_conexao, $conexao);
        $sqlUp = "UPDATE produtos SET status = 0 WHERE id_produto = $linha";    
        $fim = mysql_query($sqlUp,$conexao) or die ( "Erro alterando dados no Banco de Dados" );

  endforeach;

But giving echo in variable $sqlUp returns me this: UPDATE produtos SET status = 0 WHERE id_produto = ��1

php mysql

asked by anonymous 10.09.2015 / 18:28

1 answer

Abbreviate content inside cell (HTML table) What is the use of window.history.replaceState?

score 2 · Accepted Answer

Use REPLACE INTO .

The REPLACE INTO functions as INSERT or UPDATE in a single statement.

Example

REPLACE INTO tabela
(coluna_id, coluna_status)
VALUES
(1,0),
(2,0),
(3,0)

In a single query, multiple records are updated.

Just be aware that if a given id does not exist, instead of updating, it will be added as new.

Obviously, you need to read the .csv file and prepare the query. Since this is not the focus of the question, I find it irrelevant to explain that part.