ignore existing lines in the database when importing php file

Question

ignore existing lines in the database when importing php file

Asked 6 years ago

Viewed 91 times

0

I have the following line of code that performs the txt file import.

<?php
    function Inserir($itens, Pdo $pdo){
      $sts = $pdo->prepare("INSERT INTO dados(loja, cod_prod, cod_acesso, desc_prod, estoq_disp, data, estoq_validade) VALUES(?,?,?,?,?,?,?);");
      $sts->bindValue(1, $itens[0], PDO::PARAM_STR);
      $sts->bindValue(2, $itens[1], PDO::PARAM_STR);
      $sts->bindValue(3, $itens[2], PDO::PARAM_STR);
      $sts->bindValue(4, $itens[3], PDO::PARAM_STR);
      $sts->bindValue(5, $itens[4], PDO::PARAM_STR);
      $sts->bindValue(6, $itens[5], PDO::PARAM_STR);
      $sts->bindValue(7, $itens[6], PDO::PARAM_STR);
      $sts->execute();
      $sts->closeCursor();
      $sts = NULL;
}
if (!empty($_FILES['arquivo']))
{
    $Pdo     = new PDO("mysql:host=localhost;dbname=vencimento", "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>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>

I need that at the time of entering in the database the lines of the file that are exactly the same as those already existing within the database it ignore that line and insert only those that are not equal.

Before calling the function Inserir(), you need to check if that current line does not already exist in the bank, if it exists, does nothing, if it does not exist, calls the function Inserir().

– Leandro Lima

2018/10/23 at 14:49
@Leandrolima I understood, I just don’t know how I would put this function inside this code, you can help me in this?

– Alex Sousa

2018/10/23 at 14:53

1 answer

Browser other questions tagged php filing-cabinet sql-insert

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

by Gabriel Queiroz Schicora • **507** points · Answer 1 · 2018-10-23T17:15:10+00:00

Follow the code to check if this line is already registered in the database:

function Inserir($itens, Pdo $pdo){
  $sts = $pdo->prepare("INSERT INTO dados(loja, cod_prod, cod_acesso, desc_prod, estoq_disp, data, estoq_validade) VALUES(?,?,?,?,?,?,?);");
  $sts->bindValue(1, $itens[0], PDO::PARAM_STR);
  $sts->bindValue(2, $itens[1], PDO::PARAM_STR);
  $sts->bindValue(3, $itens[2], PDO::PARAM_STR);
  $sts->bindValue(4, $itens[3], PDO::PARAM_STR);
  $sts->bindValue(5, $itens[4], PDO::PARAM_STR);
  $sts->bindValue(6, $itens[5], PDO::PARAM_STR);
  $sts->bindValue(7, $itens[6], PDO::PARAM_STR);
  $sts->execute();
  $sts->closeCursor();
  $sts = NULL;
}
if (!empty($_FILES['arquivo']))
{
    $pdo     = new PDO("mysql:host=localhost;dbname=vencimento", "root", "");
    $file    = fopen($_FILES['arquivo']['tmp_name'], 'r');
    while (!feof($file)){
        $linha = fgets($file);          
        $itens = explode(';', $linha); 

        $query = "SELECT COUNT(*) as 'total' FROM dados
                                        WHERE loja = '{$itens[0]}' and
                                        cod_prod = '{$itens[1]}' and
                                        cod_acesso = '{$itens[2]}' and
                                        desc_prod = '{$itens[3]}' and
                                        estoq_disp = '{$itens[4]}' and
                                        data = '{$itens[5]}' and
                                        estoq_validade = '{$itens[6]}';";

        $check = $pdo->query($query);

        $result = $check->fetch(PDO::FETCH_ASSOC);

        //Verificase o valor retornado é 0 (Nenhum dado igual cadastrado)
        if($result['total'] == 0){
            Inserir($itens, $pdo);
        }else{
            //Vai cair aqui caso ja exista essa linha cadastrada
        }
    }
}