How do i delete data from database if one of the insert statement failed

Question

I have 5 inputs and one html table. I need to check the checkbox that are in the html table so I need to loop through the table and get the data. The 5 inputs and the data on html table are related so if one of the insert statement fails, this will be a problem.

So far validating the inputs and checking the html table checkbox are working, but what if one of my insert statement failed? That being said, I need to delete the data inserted if one fails.

This is the structure of my website:

Validate the inputs
Loop through html table where checkbox is checked
AJAX code here calling insert
if statement here to check if the checkbox is checked
if yes then
-> AJAX code here
-> Call another insert statement
else ->no data to be saved, because the inputs and html table value need each other

UPDATE:

if im going to use transaction how can i apply it? on my website im using two different php file for insert. one in the loop. and one for the 5 inputs this is my code

var checkedItems = $('#dataTable input[type="checkbox"]:checked').each(function() {
        $.ajax({
                    type: "POST",
                    url: "insertdocumentsignatory.php",
                    data: ({dtnum: tnum, dsignum: signum})
                })
                .done(function (msg) {
                    alert("Data Saved: " + msg);
                })
        .fail(function() {
            alert( "Posting failed." );
        }); 
        });
    });
    if (!checkedItems.size()) {
        alert ("Nothing checked");
        return;
    }else if (checkedItems.size()) {

        $.ajax({
                    type: "POST",
                    url: "insertdocument.php",
                    data: ({dtnum: tnum, ddoctitle: doctitle, ddoctype: doctype, ddoccontent: doccontent, ddocdatefilled: docdatefilled})
                })
                .done(function (msg) {
                    alert("Data Saved: " + msg);
                })
        .fail(function() {
            alert( "Posting failed." );
        });
    }

score 2 · Answer 1 · answered Nov 18 '15 at 04:43

2

you can write insert queries in transaction. If any of the insertion fails then it can rollback.

answered Nov 18 '15 at 04:43

Danish Ahmed

171
1
9

score 2 · Answer 2 · answered Nov 18 '15 at 04:48

You should be using PDO transactions. A sample transaction looks like this:

<?php
    $pdo->beginTransaction();
    $checkbox = 1; // suppose checkbox is checked
    if($checkbox) {
        $insert = $pdo->prepare("INSERT INTO anytable (anyrow) VALUES (?)");
        $insert->bindValue(1, "anyvalue");
        $insert->execute();
        if($insert->rowCount() > 0) {
            $insert2 = $pdo->prepare("INSERT INTO anytable2 (anyrow2) VALUES (?)");
            $insert2->bindValue(1, "anyvalue2");
            $insert2->execute();
            if($insert2->rowCount() > 0) {
                $pdo->commit();
            } else {
                $pdo->rollBack();
            }
        } else {
            $pdo->rollBack();
        }
    }
?>

Manual: http://php.net/manual/en/pdo.transactions.php

code above updated. how can i apply pdo transaction? if im using two php file and call using ajax? — knowmeifyou, Nov 18 '15 at 06:29

score 2 · Answer 3 · answered Nov 18 '15 at 04:50

2

Transaction will ensure that, either happen all or nothing. Most of the leading Database management system support transaction feature. Since you are using PHP I found below link, which explains how to use transaction with PHP + MySql

PHP + MySQL transactions examples

answered Nov 18 '15 at 04:50

Spider

514
1
10
22

code above updated. how can i apply pdo transaction? if im using two php file and call using ajax? – knowmeifyou Nov 18 '15 at 06:51

How do i delete data from database if one of the insert statement failed

3 Answers3