How to batch update with PHP and Mysql

Asked

Viewed 1,136 times

1

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

I made a code following a hint, it went 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 a echo in the variable $sqlUp Give me that back: UPDATE produtos SET status = 0 WHERE id_produto = ��1

php mysql

  • don’t get it, this code of yours already solves the problem? when you say "my contribution" is the status you are so far?

  • It was not I who put this code, but an issue of @Marcelo Gomes, also did not understand.

  • Now it was the end even... instead of responding, he edits the post and puts a code...

  • To not delete the @Marcelo Gomes' contribution, http://pastebin.com/wR2stu2a I put in Pastebin.

1 answer

2


Use the REPLACE INTO.

The REPLACE INTO works as INSERT or UPDATE in a single instruction.

Example

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

Multiple records are updated in a single query.

Just be aware that if a particular 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. As this is not the focus of the question, I find it irrelevant to explain this part.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.