MySQL Records Occassionally Overwritten

mysql version 5.0.51a

In order to describe what is going on here, I need to be a bit descriptive. I will try my best to explain what is important here. The tables are involved in an in-house credit application. The salesperson fills out a form for the company applying for the credit. They can then save that application to complete later or send off to the accounting dept for approval. The accountant can save the record for later, return it to the salesperson, or approve it. In any of these operations the entire form is either inserted (when first created) or updated in the table record.

When any operations that transfer the access to the form to the salesperson or accountant, an e-mail is sent to the appropriate party, which includes a link to the record. The salesperson only has access to the records they have created. This is done by simply checking their login username, held in a session variable, with a field in the table that also holds their username.

At the top of the form is a select box that holds records waiting to be processed. What is available for the salesperson in that box is records he/she has stored or records sent back to him/her for corrections and re-submission. They can pull up a form by simply selecting one. They can also retrieve a form by clicking on the e-mail link, sent to them when they submit a form or the admin (accountant) returns a form to them. Likewise, the accountant can do the same, by both methods, with records sent to them to process.

Each transaction in this process is logged in a Transaction Detail Table.

There are many error checks that prevent a record from being accessed inappropriately. (Bear with me, this is all important) Salesperson and Accountant do not have access to the record at the same time and, once an application is approved, neither have access except to view.

The Problem

Everything is dependent on the ID field, which is an auto-increment mysql field in the CreditApp table. This number is stored in the log file in the "AppID" field. In about 1% of these transactions, either when the salesperson submits the form to the admin (accountant) or the accountant approves it (I'm not sure which), rather than updating the correct record, a completely unrelated record is updated. Each overwritten record is a record that has been previously processed (meaning "approved" by the accountant), which means there is no more allowed access to the record, except to view. More often than not, the record overwritten is 2 or 3 years old.

Although I'm not sure whether the record is overwritten on submit by the salesperson or on approval by the admin, the other peculiar thing is that, when this overwrite happens, the entries in the log table, on submit by the salesperson, do not have the AppID that relates them to the form record. It is blank. This probably indicates that the problem is on submit by the salesperson, or, at least, begins there.

So here is a simplified mock up of the database processing (I am sure there is a much more eloquent way to do this but alas...):

    if($Process == "RegularSave") // Salesperson storing record for later
    {
        $Status = "Store";
        $StoreTitle = $NewTitle;
        if(empty($StoreTitle)){$Error[] = "Title cannot be blank. Record was not saved!";}
        $Q = "SELECT ID, StoreTitle FROM CreditApp WHERE ID = '$ID' OR UniqueID = '$UniqueID'";  // UniqueID prevents double entry on refresh of new record
        $Result = mysql_query($Q);
        if(!$Result){$Error[] = "Database error in storage result!";}
    }
    elseif($Process == "RegularSubmit") //Sslesperson submitting record
    {
        $Status = "Received";
        $StoreTitle = $NewTitle;
        if(empty($StoreTitle)){$Error[] = "Title cannot be blank. Record was not saved!";}
        $Q = "SELECT ID FROM CreditApp WHERE ID = '$ID' OR UniqueID = '$UniqueID'";  // UniqueID prevents double entry on refresh of new record
        $Result = mysql_query($Q);
        if(!$Result){$Error[] = "Database error in ID Check!";}
    }
    elseif($Process == "AdminProcess" || $Process == "AdminSave" || $Process == "AdminReturn")
    {
         // Status variable set here as to "Revised", "Rejected", "Approved", etc.
         // THEN:
        $Q = "SELECT ID FROM CreditApp WHERE ID = '$ID'";
        $Result = mysql_query($Q);
        if(!$Result){$Error[] = "Database error in ID Check!";}
    }
    elseif($Process == "AdminSend")
    {
        // Setup for e-mail from admin when returning record for corrections
        $ReturnDate = dFormat($Time,41);
        $FromName    = $AdminName;
        $FromEmail  = $AdminAddress;
        $ReturnUser = $_SESSION['FullName'];
        $DetailMsg  = nl2br($Message);
        $NoteString = '======================='."\n".$ReturnUser.': '.$Today."\n".$Message."\n".'======================='."\n".$Notes;
        $R = mysql_query("UPDATE CreditApp SET Notes = '$NoteString', Status = 'Return', ReturnDate = '$ReturnDate', ReturnUser = '$ReturnUser', AdminID = '$_SESSION[User]' WHERE ID = '$ID'");
        $M = mysql_query("INSERT INTO CustAcctStatsDetail (AppID,Action,Detail,Form,TranUser) VALUES ('$ID','Return for Corrections','$DetailMsg','$FormName','$_SESSION[User]')");
        $HTMLData    = ('Your credit request for '.$AcctName.' has been returned for the following reasons:<br /><br />'.nl2br($Message).'<br /><br />
                            FormLink: You can access the record from <a href="'.$MainDir.'credit.php?iKey='.$ID.'&ret=1">this link</a>.<br />
                            You will also find it available in your stored records list at the top of the Credit Application Request form.
                            <br /><br />');
    }
    if(count($Error) == 0 && $Process != "AdminSend")
    {
        if(mysql_num_rows($Result) != 0) // Indicates record already exists
        {
            #=====================================================#
            #  Update Existing Record                             #
            #=====================================================#
            $X = mysql_fetch_array($Result);
            $Q = "UPDATE CreditApp ... WHERE ID = '$X[ID]'"; // Standard Update set of fields
            $Result = mysql_query($Q);
            if(!$Result){$Error[] = "Database update error! (1) ApproveDate: ".$ApproveDate.' '.mysql_error();}
            else
            {
                // Here related tables are updated (simple one-to-many relationships for form data)
                // THEN:
                #=====================================================#
                #  Log any changes                                    #
                #=====================================================#
                $LQ = "UPDATE CustAcctStats SET StoreTitle = '$StoreTitle', Company = '$AcctName',";
                if($AppType == "New"){$ApprovalString = $Approval;}
                elseif($Approval == "Approved"){$ApprovalString = "Completed";} // Revised entry
                else{$ApprovalString = $Approval;}
                if($_SESSION[GVars][Approval] != $Approval || $AppType == "Revised")
                {
                    $StatusString = $Status.'/Credit';
                    $LQ .= " Status = '$StatusString', CreditApproval = '$ApprovalString', CreditDate = '$ThisDate',";
                }
                $TAction=array(); $TDetail=array();
                if($_SESSION[GVars][SubmitDate]  != $SubmitDate)
                {
                    $TAction[] = 'Form Submitted';
                    $TDetail[] = $AppType != "Revision" ? "Credit Application submitted for approval" : "Credit Revision Request submitted";
                    $LQ .= " SubmitDate = '$SubmitDate'";
                }
                if($_SESSION[GVars][Approval] != $Approval || $_SESSION[GVars][SubmitDate] != $SubmitDate || $AppType == "Revised")
                {
                    if(substr($LQ,-1) == ','){$LQ = substr($LQ,0,-1);}
                    $LQ .= " WHERE AppID = '$ID'";
                    $Result = mysql_query($LQ);
                    if(!$Result){$Error[] = "Log File Error! [1] ".mysql_error();}
                }
                if($_SESSION[GVars][Approval] != $Approval || $AppType == "Revised")
                {
                    if($AppType != "Revised")
                    {
                        if($Approval == "Approved")
                        {
                            $TAction[] = '<span class="LogBlue">Credit Application Approved</span>'; $TDetail[] = 'This Credit Application has been approved for '.$CreditAmt;
                        }
                        elseif($Approval == "Declined")
                        {
                            $TAction[] = '<span class="LogRed">Credit Application Declined</span>'; $TDetail[] = 'This Credit Application has been declined';
                        }
                    }
                    else
                    {
                        if($Approval == "Approved")
                        {
                            $TAction[] = '<span class="LogBlue">Credit Revision Approved</span>'; $TDetail[] = 'This submitted credit revision has been approved and completed.';
                        }
                        elseif($Approval == "Rejected")
                        {
                            $TAction[] = '<span class="LogRed">Credit Revision Rejected</span>'; $TDetail[] = 'This Credit Revision has been rejected';
                        }
                    }
                }
                if($_SESSION[GVars][Status] != $Status)
                {
                    $TAction[] = 'Status Change';
                    if(!empty($_SESSION[GVars][Status]))
                    {
                        $TDetail[] = 'Status change from '.$_SESSION[GVars][Status].' to '.$Status;
                    }
                    else
                    {
                        $TDetail[] = 'Status change set to '.$Status;
                    }
                }
                if($_SESSION[GVars][StoreTitle]  != $StoreTitle)
                {
                    $TAction[] = 'Store Title Change';
                    if(empty($_SESSION[GVars][StoreTitle]))
                    {
                        $TDetail[] = 'Store Title created: '.$StoreTitle;
                    }
                    else
                    {
                        $TDetail[] = 'Store Title change from '.$_SESSION[GVars][StoreTitle].' to '.$StoreTitle;
                    }
                }
                $TranCount = count($TAction);
                for($a=0;$a<$TranCount;$a++)
                {
                    $Q = "INSERT INTO CustAcctStatsDetail (AppID,Action,Detail,Form,TranUser) VALUES ('$ID','$TAction[$a]','$TDetail[$a]','$FormName','$_SESSION[FullName]')";
                    $Result = mysql_query($Q);
                    if(!$Result){$Error[] = "Log File Error! [2]";}
                    if($Status == "Processed")
                    {
                        $Q = "UPDATE CustAcctStats SET StoreTitle = '$StoreTitle', Company = '$AcctName', CreditDate = CURDATE(), Date = NOW(), AdminUser = '$_SESSION[User]' WHERE AppID = '$ID'";
                    }
                    else
                    {
                        $Q = "UPDATE CustAcctStats SET StoreTitle = '$StoreTitle', Company = '$AcctName', Date = NOW() WHERE AppID = '$ID'";
                    }
                    $Result = mysql_query($Q);
                }
                switch($Process)
                {
                    // Text is set here to display result and status to the user
                }
            }
        }
        elseif(!$_SESSION['Admin']) // Record is new entry. Admin only deals with records in process
        {
            #=====================================================#
            #  Create New Record                                  #
            #=====================================================#
            $Q = "INSERT INTO CreditApp ...";  // Standard Insert set of fields
            $Result = mysql_query($Q);
            if(!$Result){$Error[] = "Error in database insert! (1) ".mysql_error($Conn);}
            else
            {
                // Here related tables are updated (simple one-to-many relationships for form data)
                // THEN:
                #=====================================================#
                #  Create new Log Entry                               #
                #=====================================================#
                $CreditApproval    = !empty($Approval) ? $Approval : "";
                if(!empty($ApproveDate)){$CreditDate = $ApproveDate;}
                if(!empty($DeclineDate)){$CreditDate = $DeclineDate;}
                if($Process == "RegularSave")
                {
                    if($AppType != "Revision")
                    {
                        $Action = "Record Created";
                        $Detail = "A new record was created but stored to submit at a later date.";
                        $StatusString = "Store/Credit";
                    }
                    else
                    {
                        $CreditApproval = "Current";
                        $Action = "Credit Revision";
                        $Detail = "A Credit Revision was created but stored to submit at a later date.";
                        $StatusString = "Store/Credit";
                    }
                }
                elseif($Process == "RegularSubmit")
                {
                    if($AppType != "Revision")
                    {
                        $Action = "Record Created";
                        $Detail = "A new record was created and sent to Administration for approval.";
                        $StatusString = "Received/Credit";
                    }
                    else
                    {
                        $CreditApproval = "Current";
                        $Action = "Credit Revision";
                        $Detail = "A Credit Revision was sent to Administration.";
                        $StatusString = "Received/Credit";
                    }
                }
                else
                {
                    $Action = "Error!";
                    if(empty($Process)) // "Detail text added 2/19/15 (Previously was blank)
                    {
                        $Detail = "Process variable is empty";
                    }
                    else
                    {
                        $Detail = $Process.' should equal RegularSave or RegularSubmit';
                    }
                }
                if(empty($CreditDate)){$CreditDate = "0000-00-00";}
                if(empty($SubmitDate)){$SubmitDate = "0000-00-00";}
                $Result = mysql_query("INSERT INTO CustAcctStats (AppID,AppType,User,StoreTitle,Company,Status,CreditApproval,CreditDate,SubmitDate,Date)
                                              Values ('$ID','$AppType','$_SESSION[User]','$StoreTitle','$AcctName','$StatusString','$CreditApproval','$CreditDate','$SubmitDate',NOW())");
                if(!$Result){$Error[] = "Log File Error! [3] ".mysql_error();}
                else
                {
                    $Result = mysql_query("INSERT INTO CustAcctStatsDetail (AppID,Action,Detail,Form,TranUser,TranDate) VALUES ('$ID','$Action','$Detail','$FormName','$_SESSION[FullName]',NOW())");
                    if(!$Result){$Error[] = "Log File Error! [4]";}
                }
            }
        }
        elseif($_SESSION['Admin'])
        {
            $Error[] = "Record not found!<br />Please exit Admin mode if you want to save a new record!";
        }
    }
    if(($_POST['Submit'] == "Submit" || $Process == "AdminSend" || $Process == "AdminProcess") && count($Error) == 0)
    {
         // Here the e-mail is generated
    }

Select all Open in new window