if one insert query fails, 'undo' the previous

[paddy_fields]

I realise 'undo' is the wrong term for this and I'm pretty sure it can't be done... but it's worth asking

 

When inserting a new user their details are put in different tables, a 'members' table with their login, a 'candidatePool' with other info, and a 'cvPool' with the the location of their cv. (a candidate doesn't need to be a member to be in the candidatePool, and one user can have many cv's so it can't all be in the same table)

 

When i insert the user into the member table i take the insert_id() and use that for the foreign key data for the next insert.

 

the problem I'm thinking is, what if the first query executes but the second fails? I would want the first query to then effectively delete what it's just inserted.

 

Is the solution to put a delete query in the 'failed query' part of the second, which will then delete the row from members according to the insert_id(), if that makes sense?

/* insert into members table */
if ($stmt = $db->prepare("INSERT INTO members (email, password, salt) VALUES (?, ?, ?)")) {
$stmt->bind_param('sss', $email, $password, $random_salt);
if (!$stmt->execute()) {
    header('Location: ../error.php?err=Member was not inserted');
    exit();
}
$member_id = $db->insert_id;
$stmt->close();
            
            
/* insert into candidatePool table */
$stmt = $db->prepare("INSERT INTO candidatePool (firstName,lastName,email,telephone,registered,members_id) VALUES (?,?,?,?,?,?)");
$stmt->bind_param('sssssi', $firstName, $lastName, $email, $telephone, $registered, $member_id);
if (!$stmt->execute()) {
    
   //SHOULD I PUT A DELETE QUERY HERE, TO DELETE THE RECORD I'VE JUST INSERTED INTO MEMBERS??

 
   header('Location: ../error.php?err=Candidate was not inserted');
   exit();

}
$candidate_id = $db->insert_id;
$stmt->close();

Edited January 22, 2014 by paddyfields

[Barand]

You need to start a SQL TRANSACTION.

 

If a query fails, ROLLBACK the transaction. If at the end there were no failures, COMMIT the transaction.

 

http://dev.mysql.com/doc/refman/5.0/en/commit.html

[paddy_fields]

Great, thank you. I shall give it a go this evening and undoubtably come back to you with more questions   

[paddy_fields]

I've tried to implement the ROLLBACK and COMMIT as suggested... and it appears to work. Well, it still creates both entries into each table and throws no errors at least.

 

Is there a way I can test the rollback?

$db->autocommit(FALSE); 

        /* insert into members table */ 
        if ($stmt = $db->prepare("INSERT INTO members (email, password, salt) VALUES (?, ?, ?)")) { 
        $stmt->bind_param('sss', $email, $password, $random_salt); 
        if (!$stmt->execute()) { 
            header('Location: ../error.php?err=Member was not inserted'); 
            exit(); 
        } 
        $member_id = $db->insert_id; 
        $stmt->close(); 
                     
                     
        /* insert into candidatePool table */ 
        $stmt = $db->prepare("INSERT INTO candidatePool (firstName,lastName,email,telephone,registered,members_id) VALUES (?,?,?,?,?,?)"); 
        $stmt->bind_param('sssssi', $firstName, $lastName, $email, $telephone, $registered, $member_id); 
        if (!$stmt->execute()) { 
           $db->rollback();  
           header('Location: ../error.php?err=Candidate was not inserted'); 
           exit(); 
        } 
        $candidate_id = $db->insert_id; 
        $stmt->close();
         
        $db->commit();
        $db->autocommit(TRUE);

[kicken]

force a failure by introducing a syntax error into your query for example.

[paddy_fields]

Thanks, it all works as expected once I introduce the errors.