Check CPF and Email fields before sending to the database (PHP)

Asked

Viewed 401 times

0

Good night! I would like to know how I can implement a function (maybe?) or some method to check if the fields Cpf and e-mail in my code already exist in the bank before registering them, and if it exists, display a message for example: "CPF already registered!".

Follows the code:

<?php
session_start();
include_once '../banco/conexao.php';

$SendCadPet = filter_input(INPUT_POST, 'SendCadPet', FILTER_SANITIZE_STRING);
if ($SendCadPet) {
    $nome = filter_input(INPUT_POST, 'nome', FILTER_SANITIZE_STRING);
    $cpf = filter_input(INPUT_POST, 'cpf', FILTER_SANITIZE_STRING);
    $endereco = filter_input(INPUT_POST, 'endereco', FILTER_SANITIZE_STRING);
    $cidade = filter_input(INPUT_POST, 'cidade', FILTER_SANITIZE_STRING);
    $estado = filter_input(INPUT_POST, 'estado', FILTER_SANITIZE_STRING);
    $telefone = filter_input(INPUT_POST, 'telefone', FILTER_SANITIZE_STRING);
    $email = filter_input(INPUT_POST, 'email', FILTER_VALIDATE_EMAIL);
    $usuario = filter_input(INPUT_POST, 'usuario', FILTER_SANITIZE_STRING);
    $senha = crypt(addslashes(filter_input(INPUT_POST, 'senha', FILTER_SANITIZE_STRING)), $security_salt);
    $imagem = '../avatar/default.png';

    //Inserir
    $res = "INSERT INTO usuarios (nome, cpf, endereco, cidade, estado, telefone, email, usuario, senha, imagem, nivel) 
    VALUES (:nome, :cpf, :endereco,
    :cidade, :estado, :telefone, :email, :usuario, :senha, :imagem, :nivel)";

    $insert = $pdo->prepare($res);
    $insert->bindParam(':nome', $nome);
    $insert->bindParam(':cpf', $cpf);
    $insert->bindParam(':endereco', $endereco);
    $insert->bindParam(':cidade', $cidade);
    $insert->bindParam(':estado', $estado);
    $insert->bindParam(':telefone', $telefone);
    $insert->bindParam(':email', $email);
    $insert->bindParam(':usuario', $usuario);
    $insert->bindParam(':senha', $senha);
    $insert->bindParam(':imagem', $imagem);
    $insert->bindValue(':nivel', "2");

    if ($insert->execute()) {
        $_SESSION['msg'] = "<center><div class='alert alert-success'>Usuário Cadastrado com Sucesso!</div></center>";
        header("Location: ../views/Cadastros.php");
    } else {
        $_SESSION['msg'] = "<center><div class='alert alert-warning'>Falha no Cadastro!</div></center>";
        header("Location: ../views/Cadastros.php");
    }
} else {
    $_SESSION['msg'] = "<center><div class='alert alert-warning'>Falha no Cadastro!</div></center>";
    header("Location: ../views/Cadastros.php");
}

NOTE: The include_once is including the connection to the bank.

php database sql-insert
  • 1

    Just check if the CPF already exists in the correct bank? CPF is unique.

  • That’s right.....

  • Even checking before, check also after the INSERT. It is that it may occur that another process in parallel enters the same CPF moments before ("competition of processes").

3 answers

1

  • First, you have to create an index UNIQUE for the column cpf and email. This guarantees the oneness.

    Therefore, if there is an attempt to insert a duplicate line, the Database Engine will return an error message indicating that the restriction UNIQUE was violated and the row will not be added to the table.

  • Use these fields with Not Null

    Mysql allows multiple Nulls in a column with a constraint UNIQUE. This is not true for all databases.

    The restriction NOT NULL ensures that a column does not admit NULL values. This means that an INSERT operation that places a NULL value in that column will be aborted.

  • About the code itself, the considerations are commented on in it

     /* ###### A condicional não está correta. Vai sempre ser redirecionado para a ../views/Cadastros.php
           e não vai ser possível fazer inserts. No else tem que fazer algo que não seja um redirecionamento.
           Exemplo, mostrar uma mensagem de erro.
 ######################################################################################################### */

 if ($insert->execute()) {
     $_SESSION['msg'] = "<center><div class='alert alert-success'>Usuário Cadastrado com Sucesso!</div></center>";
     header("Location: index.php");
 } else {

     // ##### aqui seria politicamente correto tratar os erros ##############################################
             $array = $insert->errorInfo();

              // vai mostrar um array que mostra tipo de erro
             //print_r($array);

             //exemplos de erros MySQL:

             //Array ( [0] => 23000 [1] => 1048 [2] => Column 'cpf' cannot be null ) 
             //Array ( [0] => 23000 [1] => 1062 [2] => Duplicate entry '000.000.001-01' for key 'cpf' ) 

             //e de acordo com o erro emitir a mensagem adequada.

     //######################################################################################################

       /* #### isso, como dito acima, não está correto #################################### */

         //$_SESSION['msg'] = "<center><div class='alert alert-warning'>Falha no Cadastro 1!</div></center>";
         //header("Location: index.php");

     //######################################################################################################
 }

  • I’ll try and give feedback!

  • Leo, I implemented your code in mine, but it’s as if he didn’t consult the CPF, he’s registering even having a CPF equal.

  • @Alicia, to find out what’s wrong you should post all the code, connection and html

  • Ok! Jaja put on.

  • Leo, it is necessary to make that connection with the bank being that I already have a include with the connection?

  • @Woss, answer me, which of the 3 cannot be considered an atomic operation - ring the bell - fill a glass of water - turn off the light

  • @Woss, car=='yes'? dog="no" :dog="yes". The suggested answers do not completely solve. The non-Insert may be occurring due to other errors. Treating everything completely amounts to giving a BUILDING This without considering that an answer is with Mysql* already in disuse

Show 2 more comments

1

Hi, so that there is never a REPEATED CPF, you should leave in the BD the column as UNIQUE KEY (UNIQUE KEY) in this way, you would not need to test in the bank with this select, the BD itself would make the validations and just your code handle the bank errors, not caring for the rules defined by the DBA.

  • So, I made these rules in the bank, but I had no idea how to return these validations in message, I’m beginner in php! Do you have any ideas that might help me?

  • if(!$Insert->execute()){ print_r($Insert->errorInfo(); } This is the basics, will give the messages as it comes from the bank, you can create a function for error handling, which receives the error number and show a friendlier message or in English, as your case. and that can give the output (die) in case of impossibility to continue as for example if there was a key violation, or if the bank connection fell....

-1

<?php
session_start();
include_once '../banco/conexao.php';

$SendCadPet = filter_input(INPUT_POST, 'SendCadPet', FILTER_SANITIZE_STRING);
if ($SendCadPet) {
    $nome = filter_input(INPUT_POST, 'nome', FILTER_SANITIZE_STRING);
    $cpf = filter_input(INPUT_POST, 'cpf', FILTER_SANITIZE_STRING);
    $endereco = filter_input(INPUT_POST, 'endereco', FILTER_SANITIZE_STRING);
    $cidade = filter_input(INPUT_POST, 'cidade', FILTER_SANITIZE_STRING);
    $estado = filter_input(INPUT_POST, 'estado', FILTER_SANITIZE_STRING);
    $telefone = filter_input(INPUT_POST, 'telefone', FILTER_SANITIZE_STRING);
    $email = filter_input(INPUT_POST, 'email', FILTER_VALIDATE_EMAIL);
    $usuario = filter_input(INPUT_POST, 'usuario', FILTER_SANITIZE_STRING);
    $senha = crypt(addslashes(filter_input(INPUT_POST, 'senha', FILTER_SANITIZE_STRING)), $security_salt);
    $imagem = '../avatar/default.png';


     if (validaCampoVazio($email,$cpf)) 
     {
       echo "errorCampoVazios";
     }
       elseif (validaEmail($email))
     {
       echo "errorEmail";
     }
       elseif (!ValidaCpf($cpf)) 
     {
       echo "errorCpf";
     }
       else
     {



    //Inserir
    $res = "INSERT INTO usuarios (nome, cpf, endereco, cidade, estado, telefone, email, usuario, senha, imagem, nivel) 
    VALUES (:nome, :cpf, :endereco,
    :cidade, :estado, :telefone, :email, :usuario, :senha, :imagem, :nivel)";

    $insert = $pdo->prepare($res);
    $insert->bindParam(':nome', $nome);
    $insert->bindParam(':cpf', $cpf);
    $insert->bindParam(':endereco', $endereco);
    $insert->bindParam(':cidade', $cidade);
    $insert->bindParam(':estado', $estado);
    $insert->bindParam(':telefone', $telefone);
    $insert->bindParam(':email', $email);
    $insert->bindParam(':usuario', $usuario);
    $insert->bindParam(':senha', $senha);
    $insert->bindParam(':imagem', $imagem);
    $insert->bindValue(':nivel', "2");

    if ($insert->execute()) {
        $_SESSION['msg'] = "<center><div class='alert alert-success'>Usuário Cadastrado com Sucesso!</div></center>";
        header("Location: ../views/Cadastros.php");
    } else {
        $_SESSION['msg'] = "<center><div class='alert alert-warning'>Falha no Cadastro!</div></center>";
        header("Location: ../views/Cadastros.php");
    }
} else {
    $_SESSION['msg'] = "<center><div class='alert alert-warning'>Falha no Cadastro!</div></center>";
    header("Location: ../views/Cadastros.php");
}
}


Funcoes adicionais
      // funcao para validacao do email
        function validaEmail($email){
          // Verifica se o email NÃO bate no requisitos
          if (filter_var($email,FILTER_VALIDATE_EMAIL)) {
            return false;
          }else {
            return true;
          }
        }

 
        // funcao para validar se os campos estão vazios
        function validaCampoVazio($email,$cpf){
          // verifica se tem campo vazio
            if (empty($email) || empty($cpf)){
              return true;
            }else {
              return false;
            }
        }

        // funcao valida CPF
        function ValidaCpf($cpf){
          // Extrai somente os números
          $cpf = preg_replace( '/[^0-9]/is', '', $cpf );
          // Verifica se foi informado todos os digitos corretamente
          if (strlen($cpf) != 11) {
            return false;
          }
          // Verifica se foi informada uma sequência de digitos repetidos. Ex: 111.111.111-11
          if (preg_match('/(\d)\1{10}/', $cpf)) {
            return false;
          }
          // Faz o calculo para validar o CPF
          for ($t = 9; $t < 11; $t++) {
            for ($d = 0, $c = 0; $c < $t; $c++) {
              $d += $cpf{$c} * (($t + 1) - $c);
            }
            $d = ((10 * $d) % 11) % 10;
            if ($cpf{$c} != $d) {
              return false;
            }
          }
          return true;
        }

Summary of what I added: -> After the variable calls via POST, you already enter a loop that wraps the entire code; -> It validates whether the fields are empty; -> Checks the CPF using the validation calculation; -> Checks the Email as well.

The functions are commented at the end of the code.

The functions Return, I advise you to receive them with AJAX, without needing to reload the page, you must match it to Return, comparing it, and then you can make an Alert, or just return a div.

If you want, I use a javascript plugin, to style these Alerts, called Sweetalert2, I’ll leave his link here: https://sweetalert2.github.io/ , is very easy to use.

If you want to look at AJAX documentation: https://developer.mozilla.org/en-US/docs/Web/Guide/AJAX/Getting_Started

  • I’ll try and make a comeback!

Browser other questions tagged php database sql-insert

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