Import data in mysql from a layout

Asked

Viewed 125 times

0

I am importing some data in the table "tb_usuario" that comes from a text file, it works as a layout for the import of these records, I used semicolon to delimit the position of these data in the file, causing it to be inserted in the database numero and the situacao of each user. I wanted to add a field status also there in the BD, but the doubt is time to assemble the Insert because for each type of situation is assigned a different value:

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

I would have to compare the position situacao of each row before inserting into the BD, if the situation is "ACTIVE" assigns status=1 if it is "INACTIVE" assigns status=2

Ex:

txt file (Layout)

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

After the insert should look like this in the comic book:

tb_usuario

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

php importer. 

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

1 answer

1


Whereas your txt file is 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 think it is important to note that it is important to check if the file is really extension. txt if you want other users to use this application.

  • Thank you very much Christian, that’s right!

Browser other questions tagged php mysql

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