How to do so, if the record exists, it updates, if it does not do an Insert of the same

Question

I am a beginner in the forum and in php and I have a question. I have a table with id auto_incremento and name is primary key. There is how I do IF and ELSE using UPDATE and INSERT. Where, if there is name only 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
                    }
        ?>

Answer 1

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."')");
}

Answer 2

You don’t need PHP, at least you can ignore it depends on the case.

---

In Mysql you have a similar feature, which can be done natively with the 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 INSERT, unless an old row in the table has the same value as the new row for PRIMARY KEY or UNIQUE, the old row will be deleted before the new row is inserted.

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

---

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

When to use the ON DUPLICATE KEY UPDATE, this will make a INSERT, but if there is already a line with these values, a UPDATE in the line containing the figures reported.

Answer 3

You can do this by 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
}