Insert MySQL data with .txt file

Question

Insert MySQL data with .txt file

Navigation

#1 by (3 votes)

2

I have the following somewhat simpler file:

new_user.txt

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

My table in the database was created as follows:

users

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

My PHP code looks like this:

<?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 echo to $query , it 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 it takes into consideration, the queries are apparently correct, and in the validator I put it, it returns the error and does not insert anything into the table.

The connection is working perfectly.

php mysql

asked by anonymous 10.02.2017 / 13:18

1 answer

JDBC - Execute a query and use its return as a parameter to another query How to put texts in progress

score 3 · Answer 1

The mysqli_query () function only excures one SQL statement at a time, the semicolon makes delimitation between the statements then it generates a syntax error.

There are two ways to solve this. The first and most practical is to just send an insert with several VALUES , this approach ensures that all or no insert is 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));

Instead 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');

It 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 mysqli_multi_query ()