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

0
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 registry and end up leaving 2 equal data. Is there any correct way 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();
        }
    }
    
asked by anonymous 26.05.2018 / 04:57

1 answer

0

What you can do is: In existence, verify that exists and 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();
    }
}

In this way, when checking for existence, pass the id as a parameter too:

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();
        }
    }
    
26.05.2018 / 07:19