PHP form intermittently generating duplicate text when saved to MYSQL database

David Barman
David Barman used Ask the Experts™
on
MySQL form POST to database.  When INSERT INTO query is performed on what seems are random basis (or perhaps it is based on content of the data POSTed from the text area on the from, the data saved to the field in the database sometimes has duplicate information.  For example, if there are multiple sentences, a portion of one of the sentences will be duplicated in the paragraph of text.  Not sure what would cause something like this.  When using the form we can edit and try to save the same text several times, usually it will eventually save correctly.  I have several other application/database pairs on the same web/mysql server and don't seem to experience this issue.  So I am assuming the problem is coming from this specific website.

Here are some code snippets:

from the PHP form:

<div id="workOrderForm">
                  
                                                   
                              <form name="workOrderForm" method="post"  action="formdata.php">
               
                                       <div class="formLable">Client Code:</div>
                                       <input name="clientCode" type="text" class="formFiller" onkeypress="return disableEnterKey(event)" option value="<?php echo $result['clientCode'];?> "  />
                                    
                                    <div id="emailLable">Email:</div>
                                       <input name="email" type="text" id="emailFiller" onkeypress="return disableEnterKey(event)" option value="<?php echo $result['email'];?>" />
                                                                        
                                    <div class="formLable">Company Name:</div>
                                       <input name="companyName" type="text" class="formFiller" onkeypress="return disableEnterKey(event)" option value="<?php echo $result['companyName'];                                    ?> "/>
                                    
                                    <div id="phoneLable">Phone:</div>
                                       <input name="phone" type="text" id="phoneFiller" onkeypress="return disableEnterKey(event)" option value="<?php echo $result['phone'];?>"/>
                                    
                                    <div class="formLable">Contact:</div>
                                      <input name="contact" type="text" class="formFiller" onkeypress="return disableEnterKey(event)" option value="<?php echo $result['contact'];?>"/>
                                    
                                    <div id="faxLable">Service Type:</div>
                                       <select name="fax" id="faxFiller"/>
                                                 <option value="On-Site">On-Site</option>
                                                 <option value="In-House">In-House</option>
                                           <option value="Remote">Remote</option>
                                                 <option value="Warranty">Warranty</option>
                                                 <option value="No Charge">No Charge</option>
                                    </select>
                       
                                    <div class="formLable">Address 1:</div>
                                     <input name="address" type="text" class="formFiller" onkeypress="return disableEnterKey(event)" option value="<?php echo $result['address'];?>"/>
                                                  
                                    <div id="dateLable">Date:</div>      
                                    <input name="date"  type="text" onkeypress="return disableEnterKey(event)" option value="<?php echo date("m/d/y");?> " id="dateFiller" />
                                                         
                                       <div class="formLable">Address 2:</div>
                                      <input name="address2" type="text" class="formFiller" onkeypress="return disableEnterKey(event)" option value="<?php echo $result['address2'];?>" />
                                                   
                                      <div id="techLable">Tech:</div>      
                                      <select name="tech" id="techFiller" tabindex=1 />
                                                 <option value="David">David</option>
                                                 <option value="Jeff">Jeff</option>
                                           <option value="Sharon">Sharon</option>
                                                 <option value="Sandy">Sandy</option>
                                                 <option value="Crew">Crew</option>
                                    </select>            
                                    <p>&nbsp;</p>
                                    <div class="formLable">City:</div>
                                      <input name="city" type="text" class="formFiller" onkeypress="return disableEnterKey(event)" option value="<?php echo $result['city'];?>" />
                                    
                                    <div id="jobDateLable">Job Date:</div>      
                                      <input name="jobDate" type="text" onkeypress="return disableEnterKey(event)" option value="<?php echo date("m/d/y");?> " id="jobDateFiller"/>
                                    
                                    <div class="formLable">State:</div>
                                    <input name="state" type="text" id="WorkOrderStateSelect" onkeypress="return disableEnterKey(event)" option value="<?php echo $result['state'];?>" />
                                                                                                            
                                      <div id="jobStartTimeLable">Job Start Time:</div>      
                                      <input name="jobStartTime" onkeypress="return disableEnterKey(event)" type="text" id="jobStartTimeFiller" tabindex=2/>
                                                            
                                    <div id="zipCodeLable">Zip:</div>
                                       <input name="zip" type="text" id="zipCodeFiller" onkeypress="return disableEnterKey(event)"  option value="<?php echo $result['zip'];?>" />
                                                                                                        
                                       <div id="jobFinishTimeLable">Job Finish Time:</div>      
                                      <input name="jobFinishTime" onkeypress="return disableEnterKey(event)" type="text" id="jobFinishTimeFiller" tabindex=3 />
                                    
                                    <div id="PurposeCall">Purpose of call: (4 Lines Max)</div>
                                      <textarea name="purpose" wrap="soft" tabindex=4 maxlength="300" class="contactFillerMessage"></textarea>
                                    
                                    <div id="WorkDone">Work Done: (18 Lines Max)</div>
                                    <textarea name="workDone" wrap="hard" maxlength="1200" tabindex=5 id="workOrderFillerMessage"></textarea>
                                       
                                    <div id="PartsUsed">Parts Used: (4 Lines Max)</div>
                                      <textarea name="partsUsed" wrap="hard" maxlength="300" tabindex=6 class="contactFillerMessage"></textarea>
                                    
                                    <div id="Action">Action yet to be taken: (4 Lines Max)</div>
                                          <textarea name="action" wrap="hard" maxlength="300" tabindex=7 class="contactFillerMessage"></textarea>
                                    
                                    <input type="submit" name="Submit" value="Save" tabindex=8 id="formSubmit" />
                                       </form>
                                    
                              <form name="cancel" method="get">
                                    <input type="Submit" name="Submit" value="Cancel" tabindex=9 id="clearWorkorder" />
                              </form>      
                  </div> <!--end Contact form-->

from the formdata.php file to save to the database:

require_once('database.php');
 $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$q = "INSERT INTO servicereport (clientCode, companyName, contact, address, address2, city, state, zip, phone, fax, email, date, tech, jobStartTime, jobFinishTime, jobDate, purposeCall, workDone, partsUsed, posted, post, furtherWork, trackPost)       
      VALUES (:clientCode, :companyName, :contact, :address, :address2, :city, :state, :zip, :phone, :fax, :email, :date, :tech, :jobStartTime, :jobFinishTime, :jobDate, :purposeCall, :workDone, :partsUsed, :posted, :post, :action, :trackPost)";


$statement = $db->prepare($q);

$result1 = $statement->execute(array(
      ':clientCode' => $clientCode,
      ':companyName' => $companyName,
      ':contact' => $contact,
      ':address' => $address,
      ':address2' => $address2,
      ':city' => $city,
      ':state' => $state,
      ':zip' => $zip,
      ':phone' => $phone,
      ':fax' => $fax,
      ':email' => $email,
      ':date' => $date,
      ':tech' => $tech,
      ':jobStartTime' => $jobStartTime,
      ':jobFinishTime' => $jobFinishTime,
      ':jobDate' => $jobDate,
      ':purposeCall' => $purpose,
      ':workDone' => $workDone,
      ':partsUsed' => $partsUsed,
      ':posted' => "",
      ':post' => "",
      ':action' => $action,
      ':trackPost' => 0
));

database.php contents:

<?php
    $dsn = 'mysql:host=localhost;dbname=ubcustomer';
    $username = '****';
    $password = '********';

   try {
        $db = new PDO($dsn, $username, $password);
    } catch (PDOException $e) {
        $error_message = $e->getMessage();
 
        include('database_error.php');
        exit();
    }
?>
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Year 2008
Top Expert 2008

Commented:
If I understood you correctly, you are saying that the duplication happens on one (or all of these):
...
      ':purposeCall' => $purpose,
      ':workDone' => $workDone,
      ':partsUsed' => $partsUsed,
...

Open in new window

What you did not show is how you are initializing those variables ($purpose, $workDone, or $partsUsed).

Also, you did not show the code for disableEnterKey().  Are you sure it is not messing duplicating the text?  You mentioned that the problem manifests it self when there are multiple lines -- translation: multiple Enter key codes --  and the name of that javascript function suggests that it will react to the presence of "Enter" key codes!

Author

Commented:
Thank you for the response.  The duplicatation only seems to occur in the workDone field.  But that is also the only field that paragraph of data is entered.

Perhaps you are correct. Maybe it is in the disableEnterKey.

The disableEnterKey definition:

function disableEnterKey(e)
 {
      var key;
 
     if(window.event)
           key = window.event.keyCode;     //IE
      else
           key = e.which;     //firefox
 
     if(key == 13)
           return false;
      else
           return true;
 }

in the formdata.php the variable initialization is done by:

// Get the product data
// var_dump($_POST);

// $clientCode="";
$email = "";
$companyName = "";
$phone = "";
$contact = "";
$fax = "";
$address = "";
$date = "";
$address2 = "";
$tech = "";
$city = "";
$jobDate = "";
$state = "";
$jobStartTime = "";
$zip = "";
$jobFinishTime = "";
/*
$purpose = mysql_real_escape_string($_POST['purpose']);
$workDone = mysql_real_escape_string($_POST['workDone']);
$partsUsed = mysql_real_escape_string($_POST['partsUsed']);
$action = mysql_real_escape_string($_POST['action']);
*/
$purpose = "";
$workDone = "";
$partsUsed = "";
$action = "";

$clientCode = $_POST["clientCode"];
$email = $_POST['email'];
$companyName = $_POST['companyName'];
$phone = $_POST['phone'];
$contact = $_POST['contact'];
$fax = $_POST['fax'];
$address = $_POST['address'];
$date = $_POST['date'];
$address2 = $_POST['address2'];
$tech = $_POST['tech'];
$city = $_POST['city'];
$jobDate = $_POST['jobDate'];
$state = $_POST['state'];
$jobStartTime = $_POST['jobStartTime'];
$zip = $_POST['zip'];
$jobFinishTime = $_POST['jobFinishTime'];
/*
$purpose = mysql_real_escape_string($_POST['purpose']);
$workDone = mysql_real_escape_string($_POST['workDone']);
$partsUsed = mysql_real_escape_string($_POST['partsUsed']);
$action = mysql_real_escape_string($_POST['action']);
*/
$purpose = $_POST['purpose'];
$workDone = $_POST['workDone'];
$partsUsed = $_POST['partsUsed'];
$action = $_POST['action'];

Thanks again.  Any insight?
Expert of the Year 2008
Top Expert 2008

Commented:
>> if(key == 13)
Try changing that to :
if(key===10 || key === 13)

Open in new window


>> The duplication only seems to occur in the workDone field
Do you have some other javascript files linked onto the page in question?  The workDone field is a textarea which does NOT have the onkeypress attribute.  That's why I am wondering if some other js file is binding some other event upon loading.

Just so we are clear, when you say:
 "PHP form intermittently generating duplicate text"

are you saying that when workDone has multiple lines you end up getting multiple rows? Or do you get a single row per submission and the duplicate text appears only in workDone?

Do you have the form publicly available somewhere?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
We have the jquery and jqueryslidemenu JS files.

The duplication that appears (which usually only seems to occur when a large paragraph of text is entered) can vary from one word (with a line break following) or a fragment of one of the sentences is duplicated.  When the record is saved the ensuing page looks fine but when you go back and edit the record you can see the duplication. Therefore, after a batch of records is entered we are going to "edit" to make sure the paragraph was "saved" as desired.  If not, we removed the duplicated information and re-save the record.

Access to live site is not something that I can offer at this time.  I could send you the source files if necessary.
This application was written about 5 years ago when our programming skills were in their early stages.  Ultimately I may just re-write the application, but it works "good enough" if I can figure out this particular quirk.
Expert of the Year 2008
Top Expert 2008

Commented:
Regarding "function disableEnterKey(e){...}", if I am not mistaken, when you are typing on a text field and hit enter, the form will submit immediately, but only if there is just one text field on the form.  Since you have multiple text fields, you don't need to have an onkeypress event handler on those <input type="text"> fields.

So, to rule out that function, try "disabling" it by returning false immediately":
function disableEnterKey(e){
return false;
...
}

Open in new window

Also, on some OS, the Enter key generates ascii 10, others generate ascii 13, hence the reason to change the line to
key===10 || key===13.

Does the problem occur on different browsers?

Author

Commented:
Of course, I can remove the disableEnterKey.  It's been quite a while, but I believe the purpose was to prevent new line/enter characters in the textarea field.  However, I just tried and it doesn't seem to be doing that presently.  Do you suspect this is causing the duplication issue?
Expert of the Year 2008
Top Expert 2008

Commented:
>> Do you suspect this is causing the duplication issue?
If not the exact version that you posted, perhaps something similar to it.  What you posted initially, none of the <textarea> nodes have an "onkeypress" attribute like the "<input type='text'>" nodes do.  So, at first glance that rules out "disableEnterKey"; unless you have some jQuery selector (either later on or on some external JS file) that is binding disableEnterKey to the textarea nodes (or if it is not binding disableEnterKey, it may be trying to remove the newline from the textarea content).

Author

Commented:
No, there isn't any java tied to those fields.  So where does that leave us?  Anything else you can think of that could effect it?  Is there anything in the way we are writing the data to the DB or passing the data from the form?
Expert of the Year 2008
Top Expert 2008

Commented:
>> Is there anything in the way we are writing the data to the DB
No. The php is simply grabbing what in $_POST['workDone'] and inserting it to the DB as received. Hence my earlier question:

  "are you saying that when workDone has multiple lines you end up getting multiple rows? Or do you get a single row per submission and the duplicate text appears only in workDone?"

Earlier you also stated:
  "...When the record is saved the ensuing page looks fine but when you go back and edit the record you can see the duplication..."
If you examine your db directly (via whatever DB management tool you have) and you find it does not have duplicate text, then perhaps the problem is in whatever web page is retrieving the submitted data for viewing/editing  (and not on the page that is inserting/POSTing the data).

Author

Commented:
It has been a while since I looked directly at the DB when dealing with this issue.  But I believe the data in the DB also showed the duplicate text.
Expert of the Year 2008
Top Expert 2008

Commented:
Try inspecting what the browser is actually POSTing.

Open your form in Chrome and then do a right-click > Inspect.  Then click on the "Network" tab. In the "View" options click on "Preserve log".  Below the "Preserve log" option click on the "All" option as well.

Then complete your web form, but make sure that the "workDone" textarea contains multiple lines of text and then submit it.

Upon submission you may see a lot of network traffic (depending on what external resources are used by your page ).  Under the "Method" column, look for "POST" requests.  Click on it.  You should see the request details.  On the request details, scroll to the "Form data" section.  You should be seeing the data that is actually being sent to the server.  If you see the duplicated text there, then you know the problem is at submission time.  Otherwise the problem is most likely on the editing page.

Author

Commented:
Ok.  I guess I will need to experiment with that.  However, I really don't think it is on the editing page, for two reasons.  Once, sometimes even before I go into the edit screen, I can tell by the save output page that the data isn't formatted correctly.  Two, I have seen times when I run into the issue that I have to re-edit the same paragraph several times to get it to save correctly.  Which seem really odd that it does it inconsistently.  So that was why I was wondering if my save routine had some incorrect or old/outdated method that would be causing an issue.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial