CODEWITHSUNDEEP
phpmysqlpdo
Kendall Eley
Kendall Eley

Reputation: 67

PDOException' with message 'There is no active transaction'

I am getting the following error and I am unable to correct it

Fatal error: Uncaught exception 'PDOException' with message 'There is no active transaction' in /home1/jkendall/public_html/princeave.com/bo/before_care_finalize_billing.php:60 Stack trace: #0 /home1/jkendall/public_html/princeave.com/bo/before_care_finalize_billing.php(60): PDO->rollBack() #1 {main} thrown in /home1/jkendall/public_html/princeave.com/bo/before_care_finalize_billing.php on line 60

The code that is causing this is below. I have removed the insert statement and entered an echo statement and ran the code to verify that the data is getting passed and it is. Any suggestions would be appreciated.

<?php
if(isset($_POST['set']) && isset($_POST['set']) !="") {
    $size = count($_POST['charge_id']);
    $i = 0; 
    while ($i < $size) {
        $usid=$_SESSION['uid'];
        $approval_date = date('Y-m-d');
        $bill_date = date('Y-m-d');
        $transaction_date = date('Y-m-d');
        $customer_ID = $_POST['customer_ID'][$i];
        $student_ID = $_POST['student_ID'][$i];
        $full_name = $_POST['full_name'][$i];
        $account = $_POST['account'][$i];
        $description = $_POST['description'][$i];
        $bill_amount = $_POST['bill_amount'][$i];
        echo $usid." Approval ".$approval_date." Bill ".$bill_date." Transaction ".$transaction_date." ".$customer_ID." ".$student_ID." ".$full_name." ".$account." ".$description." ".$bill_amount."<br> ";
        try{
            $stmta[$i] = $db->prepare("INSERT INTO bo_transactions(transaction_date, customer_ID, student_ID, full_name, account_num, description, bill_amount, bill_date, user_id, approval_date) VALUES (:transaction_date, :customer_ID, :student_ID, :full_name, :account, :description, :charge, :bill_date, :usid, :approval_date)");
            $stmta[$i]->bindParam(':usid',$usid,PDO::PARAM_STR);
            $stmta[$i]->bindParam(':approval_date',$approval_date,PDO::PARAM_STR);
            $stmta[$i]->bindParam(':bill_date',$bill_date,PDO::PARAM_STR);
            $stmta[$i]->bindParam(':transaction_date',$transaction_date,PDO::PARAM_STR);
            $stmta[$i]->bindParam(':customer_ID',$customer_ID,PDO::PARAM_STR);
            $stmta[$i]->bindParam(':student_ID',$student_ID,PDO::PARAM_STR);
            $stmta[$i]->bindParam(':full_name',$full_name,PDO::PARAM_STR);
            $stmta[$i]->bindParam(':account',$account,PDO::PARAM_STR);
            $stmta[$i]->bindParam(':description',$description,PDO::PARAM_STR);
            $stmta[$i]->bindParam(':bill_amount',$bill_amount,PDO::PARAM_STR);
            $stmta[$i]->execute();
            //exit();
        }
            catch(PDOException $e){
                $db->rollBack();
                echo $e->getMessage();
                //exit();
            }
        ++$i;
    }
    header(sprintf('Location: before_care_select_students.php'));
}

Upvotes: 2

Views: 5866

Answers (1)

Jacobm001
Jacobm001

Reputation: 4539

Your code never starts a transaction. In order to rollback, you need to first create a transaction, otherwise it's just going to insert it. Below is your code where I've added both a beginTransaction() and commit() function.

<?php
if(isset($_POST['set']) && isset($_POST['set']) !="") {
    $size = count($_POST['charge_id']);
    $i = 0; 

    $db->beginTransaction();
    while ($i < $size) {
        $usid=$_SESSION['uid'];
        $approval_date = date('Y-m-d');
        $bill_date = date('Y-m-d');
        $transaction_date = date('Y-m-d');
        $customer_ID = $_POST['customer_ID'][$i];
        $student_ID = $_POST['student_ID'][$i];
        $full_name = $_POST['full_name'][$i];
        $account = $_POST['account'][$i];
        $description = $_POST['description'][$i];
        $bill_amount = $_POST['bill_amount'][$i];
        echo $usid." Approval ".$approval_date." Bill ".$bill_date." Transaction ".$transaction_date." ".$customer_ID." ".$student_ID." ".$full_name." ".$account." ".$description." ".$bill_amount."<br> ";

        try{
            $stmta[$i] = $db->prepare("INSERT INTO bo_transactions(transaction_date, customer_ID, student_ID, full_name, account_num, description, bill_amount, bill_date, user_id, approval_date) VALUES (:transaction_date, :customer_ID, :student_ID, :full_name, :account, :description, :charge, :bill_date, :usid, :approval_date)");
            $stmta[$i]->bindParam(':usid',$usid,PDO::PARAM_STR);
            $stmta[$i]->bindParam(':approval_date',$approval_date,PDO::PARAM_STR);
            $stmta[$i]->bindParam(':bill_date',$bill_date,PDO::PARAM_STR);
            $stmta[$i]->bindParam(':transaction_date',$transaction_date,PDO::PARAM_STR);
            $stmta[$i]->bindParam(':customer_ID',$customer_ID,PDO::PARAM_STR);
            $stmta[$i]->bindParam(':student_ID',$student_ID,PDO::PARAM_STR);
            $stmta[$i]->bindParam(':full_name',$full_name,PDO::PARAM_STR);
            $stmta[$i]->bindParam(':account',$account,PDO::PARAM_STR);
            $stmta[$i]->bindParam(':description',$description,PDO::PARAM_STR);
            $stmta[$i]->bindParam(':bill_amount',$bill_amount,PDO::PARAM_STR);
            $stmta[$i]->execute();
            //exit();
        }
            catch(PDOException $e){
                $db->rollBack();
                echo $e->getMessage();
                die("Bad insert");
            }
        ++$i;
    }

    $db->commit();
    header(sprintf('Location: before_care_select_students.php'));
}

Notes:

  1. Putting the transaction commands around your while loop insures that your database is only changed if all the records are successfully submitted.

  2. As someone else pointed out, putting exit() or die() in production code is generally a bad idea. It seemed that you had included exit() as a part of your debugging work, and wanted to put something in there that was slightly more helpful to that end.

Upvotes: 3

Related Questions