Import data into mysql from a layout

Question

Import data into mysql from a layout

Navigation

#1 by (1 votes)

0

I'm importing some data into the table "tb_usuario" that comes from a text file, it works as a layout for importing these records, I used a semicolon to delimit the position of this data in the file, causing it to be inserted in the database of data, numero and situacao of each user. I would like to add a status field also in the DB, but the question is when it is time to insert the insert because for each type of situation a different value is assigned:

status = 1 for active and status = 2 for inactive.

I would have to compare the position situacao of each line before inserting in the DB, if it is the situation is "ACTIVE" assign status = 1 if it is "INACTIVE" assign status = 2

Ex:

file.txt (Layout)

NUMERO | SITUAÇÃO  
1      | ATIVO  
2      | INATIVO

After insert should look like this in DB:

tb_user

ID  | NUMERO | SITUACAO | STATUS  
1     1        ATIVO       1         
2     2        INATIVO     2

importer.php

function Inserir($itens, Pdo $pdo){
   $sts = $pdo->prepare("
        INSERT  INTO tb_usuario(numero,situacao) 
        VALUES(?,?);
        ");
    $sts->bindValue(1, $itens[0], PDO::PARAM_STR);
    $sts->bindValue(2, $itens[1], PDO::PARAM_STR);
    $sts->execute();
    $sts->closeCursor();
    $sts = NULL;

}
if (!empty($_FILES['arquivo']))
{
    $Pdo     = new PDO("mysql:host=localhost;dbname=teste", "root", "");
    $file    = fopen($_FILES['arquivo']['tmp_name'], 'r');
    while (!feof($file)){
        $linha = fgets($file);          
        $itens = explode(';', $linha);          
        Inserir($itens, $Pdo);
    }
}

?>
<!DOCTYPE HTML>
<html>
<head>
    <meta charset="utf-8">
    <title>Importar Arquivo</title>
</head>
<body>
    <form action="<?php echo $_SERVER['PHP_SELF'];?>" enctype="multipart/form-data" method="post">
        <input type="file" name="arquivo" id="arquivo">
        <input type="submit" name="enviar" value="Enviar">
    </form>
</body>
</html>

php mysql

asked by anonymous 01.02.2018 / 11:09

1 answer

Menu with doubt dropdown Recover field without include / require

score 1 · Accepted Answer

Considering that your txt file looks something like this:

1      ; ATIVO  
2      ; INATIVO
3      ; INATIVO
4      ; INATIVO 
5      ; ATIVO

If I understand the question correctly, your PHP code will look like this:

<?php function Inserir($itens, Pdo $pdo){
    if($itens[1] == 'INATIVO')
    {
        $status = 2;
    }
    else {
        $status = 1;
    }
   $sts = $pdo->prepare("
        INSERT  INTO tb_usuario(numero,situacao, status) 
        VALUES(?,?,?);
        ");
    $sts->bindValue(1, $itens[0], PDO::PARAM_STR);
    $sts->bindValue(2, $itens[1], PDO::PARAM_STR);
    $sts->bindValue(3, $status, PDO::PARAM_INT);
    $sts->execute();
    $sts->closeCursor();
    $sts = NULL;

}
if (!empty($_FILES['arquivo']))
{
    $Pdo     = new PDO("mysql:host=localhost;dbname=teste", "root", "");
    $file    = fopen($_FILES['arquivo']['tmp_name'], 'r');
    while (!feof($file)){
        $linha = fgets($file);          
        $itens = explode(';', $linha); 
        for($i = 0; $i < count($itens); $i++)
        {
            $itens[$i] = trim($itens[$i]);
        }
        Inserir($itens, $Pdo);
    }
}

?>

In this case I considered that the "situation" column will always be the second column.

I also find it important to note that it is important to verify that the file is actually .txt extension if you want other users to use this application.