Insert Mysql data with file . txt

Question

I have the following file, somewhat simple:

new_user.txt

JOAO|18|JOAO VITOR BARBOSA|MANDAQUI
ROGERIO|38|ROGERIO JOSE DE BARROS|CAPAO REDONDO

My table in the bank was created as follows:

users

create table users (
    primeiro_nome varchar(100),
    idade varchar(2),
    nome varchar(255),
    bairro varchar(255)
);

My PHP code is as follows:

<?php
require_once('../includes/conecta.php');

$file = fopen('new_user.txt', 'r'); 

while(!feof($file)){
    $data = explode('|', fgets($file)); 
    $query = "INSERT INTO users (primeiro_nome, idade, nome, bairro) VALUES ('".implode("', '", $data)."');";

    $executa = mysqli_query($conexao, $query);

    if($executa){
        echo 'DADOS INSERIDOS COM SUCESSO';
    } else {
        echo 'OCORREU UM ERRO!';
    }

}

When I give one echo in $query, he returns me the following:

INSERT INTO users (primeiro_nome, idade, nome, bairro) VALUES ('JOAO', '18', 'JOAO VITOR BARBOSA SOUZA', 'MANDAQUI ');INSERT INTO users (primeiro_nome, idade, nome, bairro) VALUES ('ROGERIO', '38', 'ROGERIO JOSE DE BARROS', 'CAPAO REDONDO');

If you take into account, apparently the queries are right, and in the validator I put, it returns the error and inserts nothing in the table.

The connection is working perfectly.

Answer 1

The function mysqli_query() excuta only one SQL statement at a time, the semicolon makes the delimitation between the instructions so this generates a syntax error.

There are two ways to solve this the first and most practical is to send only one Insert with several keys VALUES, this approach ensures that all or no Serts are made.

$query = "INSERT INTO users (primeiro_nome, idade, nome, bairro) VALUES "; 
$values = trim("('".implode("','", $data)."'),", ',');
$executa = mysqli_query($conexao, $query) or die(mysqli_error($conexao));       

In place of:

INSERT INTO users (primeiro_nome, idade, nome, bairro) VALUES ('JOAO', '18', 'JOAO VITOR BARBOSA SOUZA', 'MANDAQUI ');

INSERT INTO users (primeiro_nome, idade, nome, bairro) VALUES ('ROGERIO', '38', 'ROGERIO JOSE DE BARROS', 'CAPAO REDONDO');

Will be generated:

INSERT INTO users (primeiro_nome, idade, nome, bairro) VALUES
('JOAO', '18', 'JOAO VITOR BARBOSA SOUZA', 'MANDAQUI '),
('ROGERIO', '38', 'ROGERIO JOSE DE BARROS', 'CAPAO REDONDO')

The other is to use function mysqli_multi_query()