How to do that, if the registry exists, it does update, if it does not make an insert of the same one

3

I'm a beginner in the forum and in php and I have a question. I have a table with id auto_increment and name is primary key. It has like me to do IF and ELSE using UPDATE and INSERT. Where, if there is name just do update, otherwise do insert?

        <?php
        $sql = "SELECT * FROM usuarios";    
                    if(isset($_POST['enviar'])){
                                $result = $conn->query($sql);
                                $row = $result->fetch_assoc();
                    $nome = $_POST['nome'];
                    $cpf = $_POST['cpf'];

                    $result_dados_pessoais = "INSERT INTO usuarios (nome, cpf) VALUES ('$nome', '$cpf')";
                    $resultado_dados_pessoais= mysqli_query($conn, $result_dados_pessoais);
                    //ID do usuario inserido
                    }
        ?>
    
asked by anonymous 21.09.2017 / 20:16

3 answers

3

By PHP

    <?php

           if(isset($_POST['enviar'])){
                    $nome = $_POST['nome'];
                    $cpf = $_POST['cpf'];
                    $sql1 = ("SELECT * FROM usuarios Where nome='$nome'");
                    $result = $conn->query($sql);
                    $row = $result->fetch_assoc();

                if ($result->num_rows > 0) {
                    $result_dados_pessoais = ("UPDATE usuarios SET cpf='".$cpf."' Where nome='".$nome."'"); 
                }else{
                    $result_dados_pessoais = "INSERT INTO usuarios (nome, cpf) VALUES ('$nome', '$cpf')";
                    //ID do usuario inserido
                }
                $resultado_dados_pessoais= mysqli_query($conn, $result_dados_pessoais);
           }
    ?>

another way with if else

if(mysqli_query($conn, "SELECT * FROM usuarios WHERE nome = '".$nome."'")) {
    $result_dados_pessoais = mysqli_query($db, "UPDATE usuarios Set cpf = '".$cpf."' WHERE nome = '".$nome."'");
} else {
    $result_dados_pessoais = mysqli_query($conn, "INSERT INTO usuarios (nome, cpf) VALUES ('".$nome."', '".$cpf."')");
}
    
21.09.2017 / 20:52
3

You do not need PHP, at least you have to ignore it depending on the case.

In MySQL you have a similar feature that can be done natively with REPLACE , according to the manual.

  

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 13.2.5, "INSERT Syntax".

REPLACE works exactly with the INSERT, except if an old row in the table has the same value as the new row for the PRIMARY KEY or UNIQUE indexes, the old row will be deleted before the new row is inserted. / em>

This may be enough, but of course your database has to be properly configured and make use of UNIQUE (or PRIMARY KEY).

Another option is to use INSERT ... ON DUPLICATE KEY UPDATE , again if your database is using UNIQUE .

When using ON DUPLICATE KEY UPDATE , this will make a INSERT , but if there is already a line with these values, a UPDATE will be done on the line containing the values entered.

    
21.09.2017 / 21:10
0

You can do this through SQL even this way:

IF EXISTS (SELECT * FROM usuario WHERE nome = 'NOMEDOUSUARIO') 
BEGIN
   UPDATE ...
END
ELSE
BEGIN
    INSERT INTO ...
END

Try this way in php:

$rs = mysql_query("SELECT * FROM usuarios WHERE nome = '$nome'");
$num = mysql_num_rows($rs);

if($num > 0) {
    //Faça seu update aqui dentro
} else {
    //Faça seu insert aqui dentro
}
    
21.09.2017 / 20:20