delete or undo the first query if the second query did not work

Question

im confused about this. Is there any way like to test if all queries are working and not returning erros? if so execute them or return something.

I am building a signup method, this have 2 parts: loginInfo(username, password) and the usual Employee info (name, email, etc..).

The signup method inserts the Employee info into employee table, then it gets the PRIMARY key and insert it alongside with the loginInfo in the login table

the login table has an UNIQUE column, this should return error on duplicated. The problem is that the employee info are inserted without a login information.

How can i solve this problem?

My code:

public function signUp($personInfo, $employeeInfo, $loginCredit){
    try {
        $stmt = $this->pdo->prepare("INSERT 
            INTO `$this->employeeTable`
                (`name`, `birthDay`, `phnNmb`, `email`, `address`, `idnNmb`, `insNmb`, `bankInfo`)
            VALUES
                (?, ?, ?, ?, ?, ?, ?, ?);
        ");

        $stmt->execute([
            $personInfo["name"],
            $employeeInfo["birthDay"],
            $personInfo["phnNmb"],
            $personInfo["email"],
            json_encode($employeeInfo["address"]),
            $employeeInfo["idnNmb"],
            $employeeInfo["insNmb"],
            json_encode($employeeInfo["bankInfo"])
        ]);

        $employeeId = $this->pdo->lastInsertId();

        $insertloginCredit = $this->pdo->prepare("INSERT INTO `$this->loginTable` (`empId`, `userName`, `userPass`) VALUES ($employeeId, ?, ?);");
        $insertloginCredit->execute($loginCredit["userName"], md5($loginCredit["userPass"]));

        echo "done";

    } catch (\PDOException $err) {
        die($err->getMessage());
    }
}

Answer 1

What you're looking for here is called a transaction. The PDO docs have some explanation on them. In summary, you'd want to do the following.

try{
  $this->pdo->beginTransaction();
  $stmt1 = $this->pdo->prepare(...);
  $stmt1->execute(...);
  $stmt2 = $this->pdo->prepare(...);
  $stmt2->execute(...);
  $this->pdo->commit();
} catch (\PDOException $e) {
  $this->pdo->rollBack();
}

The rollBack function will restore the database to the state it was in when beginTransaction was called.