How to check if it already exists in the database and update

Question

Well, I’m trying to update and check if what I’m updating already exists, I can check if it exists, but there’s a problem, as I’m updating a data, it will always exist, if I take the function that checks if it exists, the user can update the record and end up leaving 2 equal data. Have some correct way that allows you to check the data in the database and update?

Function to check if it already exists

    public function existsFunction($name){
        try{
            $command = ("SELECT * FROM function
                                WHERE function_nm = '$name'");
            $num_rows = $this->mysqli->query($command)->num_rows;
            if($num_rows < 1){
                return false;
            }else{
                return true;
            }
        }catch(Exception $err){
            echo 'Erro: ', $err->getMessage();
        }
    }

Function to update

    public function editFunction($id, $name, $access, $comment){
        try{
            if($this->existsFunction($name)){
                return "already_exists";
            }else{
                $command = ("UPDATE function
                                SET function_nm = '$name',
                                    access_cd = '$access',
                                    function_ds = '$comment'
                                        WHERE function_id = '$id'");
                $query = $this->mysqli->query($command);
                if(!$query){
                    return "error";
                }else{
                    return "success";
                }
            }
        }catch(Exception $err){
            echo 'Erro: ', $err->getMessage();
        }
    }

Answer 1

What you can do is: On condition of existence, check if there is and if is different from the Id being updated, for example:

public function existsFunction($name,$id){
    try{
        $command = ("SELECT * FROM function
                            WHERE function_nm = '$name' AND function_id <> '$id'");
        $num_rows = $this->mysqli->query($command)->num_rows;
        if($num_rows < 1){
            return false;
        }else{
            return true;
        }
    }catch(Exception $err){
        echo 'Erro: ', $err->getMessage();
    }
}

This way, when checking the existence, pass the id as parameter as well:

public function editFunction($id, $name, $access, $comment){
        try{
            if($this->existsFunction($name,$id)){
                return "already_exists";
            }else{
                $command = ("UPDATE function
                                SET function_nm = '$name',
                                    access_cd = '$access',
                                    function_ds = '$comment'
                                        WHERE function_id = '$id'");
                $query = $this->mysqli->query($command);
                if(!$query){
                    return "error";
                }else{
                    return "success";
                }
            }
        }catch(Exception $err){
            echo 'Erro: ', $err->getMessage();
        }
    }