Import from . txt using php + mysql

Question

Good evening, I’ve tried other ways, but I couldn’t. I need to run the code query below at once instead of inserting row by row into the database. Someone would have salvation?

<?php

set_time_limit(0);
if (!isset($seguranca)) {
exit;
}
$arquivo_tmp = $_FILES['arquivo']['tmp_name'];

//ler todo o arquivo para um array
$dados = file($arquivo_tmp);

//Ler os dados do array
foreach ($dados as $linha) {
//Retirar os espaços em branco no inicio e no final da string
$linha = trim($linha);
//Colocar em um array cada item separado pela virgula na string
$valor = explode(',', $linha);

//Recuperar o valor do array indicando qual posição do array requerido e atribuindo para um variável
$data = $valor[0];
$hora = $valor[1];
$pis = $valor[2];



//Criar a QUERY com PHP para inserir os dados no banco de dados
$result_usuario = "INSERT INTO func_batidas (data, hora, pis) VALUES ('$data', '$hora', '$pis')";
//Executar a QUERY para inserir os registros no banco de dados com MySQLi
$resultado_usuario = mysqli_query($conectar, $result_usuario);
}
//Criar a variável global com a mensagem de sucesso
//Redirecionar o usuário com PHP para a página index.php
$_SESSION['msg'] = "<div class='alert alert-success'>Batidas importadas!</div>";
$url_destino = pg . "/listar/list_importa_batidas";
header("Location: $url_destino");

Answer 1

You can use transaction. If you do not know what this is, I recommend reading the question below.

• What is a Mysql Transaction for?

In the extension mysqli_*, we can initiate a transaction using the method begin_transaction or the function mysqli_begin_transaction, for example:

/**
 * Orientado à Objeto
 * Inicia a transação apenas para leitura
 */
$conn->begin_transaction(MYSQLI_TRANS_START_READ_ONLY);

/**
 * Procedural
 * Inicia a transação para leitura e escrita
 */
mysqli_begin_transaction($conn, MYSQLI_TRANS_START_READ_WRITE);

After initiating the transaction, we can inform our darlings through function mysqli_query or the method query, for example:

/**
 * Orientado à Objeto
 */
$conn->query("INSERT INTO ...");

/**
 * Procedural
 */
mysqli_query($conn, "INSERT INTO...");

Once this is done we can "commit" our transaction. It means whatever we do after the begin_transaction, will be executed in the database.

---

An alternative, is the function mysqli_multi_query or the method multi_query. As its name already says, here we can inform several darlings delimited by a character (usually the character is ponto-e-vírgula);

To do that, all we have to do is save the darlings in a array, for example:

$queries = [];

foreach ($dados as $linha) {
    $queries[] = "INSERT INTO func_batidas (data, hora, pis) VALUES ('$data', '$hora', '$pis')";
}

Note that we do not add a delimiter at the end, we will do this in the next step.

Now we just need to turn this array in string and also add our delimiter, for this we can use the function implode, for example:

/**
 * Orientado à Objeto
 */
$conn->multi_query( implode(";", $queries) );

/**
 * Procedural
 */
mysqli_multi_query($link, implode(";", $queries) );

Ready! That way the extension of MySQLI shall be responsible for executing all darlings at the same time.