Why do duplicate rows get inserted?

[busboy]

I am now using a unique index on multiple columns to prevent duplicate rows. However, I never did find a good reason on the web as to why duplicates happen in the first place. Is it something with my hosting company?

Can you help?

[RichardAskew]

Have you got any code you can share?

[guido2004]

Duplicates happen when you insert the same data more than once in your database.
It has nothing to do with the hosting, and everything with your code.

[K. Wolfe]

In the previous thread, we said we needed to see how you are populating the data. The problem is with some application logic that is staging your data for insert. The problem is not going to be with your hosting company.

EDIT: Sorry, what Guido said.

[busboy]

Ok, here is an example of the format I use when making an insert:

$thanksUpdate = "INSERT INTO notifications (dateAdded, priority, type, sender, recipient, subject, uID, message, dateScheduled) VALUES (


'$longDate',
'1',
'$type',
'$sender',
'$email',
'$subject',
'$uID',
'".mysql_real_escape_string($message)."',
'$longDate')";


mysql_query($thanksUpdate) OR die("<b>A fatal MySQL error occurred</b>.\n<br />Query: " . $thanksUpdate . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());

[K. Wolfe]

The problem is before your insert, not the insert itself. Something is probably causing that script to run more than once.

[busboy]

In the meantime, should I just use insert IGNORE to prevent error messages when the unique index sets an error message?

[r937]

In the meantime, should I just use insert IGNORE to prevent error messages when the unique index sets an error message?

how would you feel about that?

[K. Wolfe]

how would you feel about that?

That is the only thing that matters, isn't it?

[busboy]

Well, using IGNORE is what I learned from another mysql website:

"And when you insert you can use INSERT IGNORE INTO ... so that it ignores duplicates instead of throwing an error. (Useful when you batch insert and the duplicates are expected)"


http://www.tutorialspoint.com/mysql/...duplicates.htm


I just want to make sure that I'm on the right track.

[K. Wolfe]

What Rudy is saying, is that as the developer, you need to decide if this is the behavior that you want. Sure, this will silence your errors, but are you ok with not being notified on those errors.

[Jeff Mott]

In the meantime, should I just use insert IGNORE to prevent error messages when the unique index sets an error message?

This will hide the symptom, but not fix the problem.

The problem is that somewhere in your code, you're inserting twice. If you post all your code, then we could probably help you spot the issue.

[busboy]

Ok, here is the code that processes the form data. I'm not sure how to paste code in here so that it lines up the same way it looks in CODA. As you will see, there is only place where code is inserted into the notifications table, which comprises the email that thanks the author for submitting a new entry. And that particular code is the one that frequently produces duplicates.

Thank you!!

                        // !Update the searches table                                                $increaseAlerts = "UPDATE searches SET alerts = alerts +1, lastAlert = '$longDate' where sID = $sID";                                                $result = mysql_query($increaseAlerts) OR die("<b>A fatal MySQL error occurred</b>.\n<br />Query: " . $increaseAlerts . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());                    
                        //if ($result === false) {                                                        // An error occurred while trying to update the searches table                                                        //$insertFailures++;                                                                //}                                                                // !Update the users table                                                                    $updateLastMatch = "UPDATE users SET lastMatch = '$longDate' WHERE uID = '$uID'";                                                mysql_query($updateLastMatch) OR die("<b>A fatal MySQL error occurred</b>.\n<br />Query: " . $updateLastMatch . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());                                                 }
                                } // End sending them a match notification                                         } while($row = mysql_fetch_array($allMatchesResult));                                        print ("<tr>");            print ("<th colspan='$columns' align='center'>Total: $newMatches </th>");            print ("</tr>");                        print ("</table>");                                                            // Update the testimonies table                        $approveQuery = "UPDATE testimonies SET approved = 'Yes', testimony = '".mysql_real_escape_string($testimonialText)."', keywords = '$keywords', title = '".mysql_real_escape_string($testimonialTitle)."' where tID = $tID";
            mysql_query($approveQuery) OR die("<b>A fatal MySQL error occurred</b>.\n<br />Query: " . $approvedQuery . "<br />\nError: (" . mysql_errno() . ") " . mysql_error()); 



            // !Thank the author                        $message = "$authorFirstName, \n\n" .                        "The following testimonial has been approved and $newMatches members will now receive a match email. \n\n" .                        "$testimonialTitle \n\n" .                        "Click on the following link to verify how it looks on our website: \n\n" .                        "http://www.oil-testimonials.com/login.php?tID=$tID&vID=$uID \n\n" .                        "We appreciate the time you have given to help teach and encourage other people. \n\n\n" .                        "Best regards, \n\n" .                        "Support Team \n" .            "Oil-Testimonials.com \n" .            "Discover Essential Oil Truths \n\n\n" .                                    "Page: approveTestimonial.php \n";                                    $type       = "Thanks for Sharing";            $subject    = "Thank You $authorFirstName";
                                // Add the thank you for posting email to notifications                        $thanksUpdate = "INSERT IGNORE INTO notifications (dateAdded, priority, type, sender, recipient, subject, uID, message, dateScheduled) VALUES (                    '$longDate',             '1',            '$type',            '$sender',            '$authorEmail',             '$subject',             '$authorID',             '".mysql_real_escape_string($message)."',            '$longDate')";                mysql_query($thanksUpdate) OR die("<b>A fatal MySQL error occurred</b>.\n<br />Query: " . $thanksUpdate . "<br />\nError: (" . mysql_errno() . ") " . mysql_error()); 

[r937]

Ok, here is the code that processes the form data.

AAAAACK!! MINE EYES!! ZEY ARE BLEEDINK!! ZEE GOGGLES, ZEY DO NOSSINK!!

elseif ($action == "process")  {
  // !Process the testimonial
  
  $sender = "support@oil-testimonials.com";
  
  $authorID        = $_REQUEST['authorID'];         
  $tID           = $_REQUEST['tID'];
  $matchWords        = $_REQUEST['matchWords'];
  $exactWords        = $_REQUEST['exactWords'];  
  $testimonialsKeywords  = $_REQUEST['testimonialKeywords'];
  $testimonialTitle    = $_REQUEST['testimonialTitle'];
  $testimonialText    = $_REQUEST['testimonialText'];
  $authorFirstName    = $_REQUEST['authorFirstName'];
  $authorLastName      = $_REQUEST['authorLastName'];
  $authorEmail      = $_REQUEST['authorEmail'];
  $scheduledDate      = $_REQUEST['scheduledDate'];    // Date to send out the match email  
  $processMethod      = $_REQUEST['processMethod'];    // Either simulate execute or delete
  $pageTitle = "Testimonial #$tID Approved";
  
  include 'toolsHeader.php';
  if ($processMethod == "execute" || $processMethod == "simulate") {
    
    // Update database
      
    $keywords      = strtolower($keywords);
    $testimonialTitle  = ucwords($testimonialTitle);

    // !Determine any matches
    if ($exactWords) {
    
      $quotesQuery = " or keyword in ($exactWords)) ";
  
    } else {
    
      $quotesQuery = ")";
    
    }
    
    $time = strtotime($scheduledDate);
    $shortScheduledDate = date( 'Y-m-d', $time );
    
      
    $allMatches = "
    SELECT
      u.uID,
      u.firstName,
      u.lastName,
      u.email,
      u.sendMatches, 
      date_format(u.lastMatch, '%m-%d-%Y') as lastMatch,
      s.sID,
      s.searchDate as longSearchDate,
      date_format(s.searchDate, '%m-%d-%Y') as shortSearchDate,
      s.uID,
      s.type,
      s.keyword,
      s.alerts,
      date_format(s.lastAlert, '%m-%d-%Y') as lastAlert,
      datediff(now(), s.lastAlert) AS sinceLastAlert,
      s.clicks,
      date_format(s.lastClick, '%m-%d-%Y') as lastClick,
      date_format(u.lastLogin, '%m-%d-%Y') as lastLogin
    FROM searches s
    INNER JOIN users u
    ON s.uID = u.uID 
    WHERE (MATCH (keyword) AGAINST ('$matchWords' in boolean mode)" ."$quotesQuery" ."
      and u.sendMatches = 'Yes' 
      and u.bouncing = 'No' 
      and (date(s.lastAlert) <> '$shortScheduledDate' OR date(lastAlert) is null) 
      and s.type in ('basic', 'advanced')
    GROUP BY
       u.uID
    ORDER BY
       s.searchDate";
    
    $allMatchesResult = mysql_query($allMatches) OR die("<b>A fatal MySQL error occurred</b>.\n<br />Query: " . $allMatches . "<br />\nError: (" . mysql_errno() . ") " . mysql_error()); 

    if ($row = mysql_fetch_array($allMatchesResult)) {
      
      $newMatches    = 0;
      $deletedAlerts  = 0;

      print ("<br />");
      
      print ("<p>Testimonial: $testimonialTitle</p>");
      print ("<p>Exact Match: $exactWords</p>");
      print ("<p>Match Words: $matchWords</p>");  
      print ("<p>Query: $allMatches</p>");
      print ("<p>Process Method: $processMethod</p>");    
      
      print ("<table>");
      
      print ("<tr>");
      print ("<th>Member</th>");
      print ("<th>Last Login</th>");
      print ("<th>Last Match</th>");
      print ("<th>Date</th>");
      print ("<th>Search</th>");
      print ("<th>Alerts</th>");
      print ("<th>Last Alert</th>");
      print ("<th>Clicks</th>");
      print ("<th>Last Click</th>");
      print ("<th>CTR</th>");
      print ("</tr>");

      //$insertFailures = 0;

      do {
          
        $uID        =$row["uID"];            // ID of person conducting search
        $lastLogin      =$row["lastLogin"];      // Their last login date
        $sID        =$row["sID"];          // ID of zero result search
        $shortSearchDate  =$row["shortSearchDate"];  // Date of search
        $longSearchDate    =$row["longSearchDate"];  // Date of search
        $keyword      =$row["keyword"];      // Keyword used when the search originated
        $type        =$row["type"];        // The type of search they conducted
        $firstName      =$row["firstName"];      // Firstname of person who initiated the search
        $lastName      =$row["lastName"];      // Lastname of person who initiated the search
        $email        =$row["email"];        // Email address of person who initiated search
        $lastMatch      =$row["lastMatch"];      // The last match of any kind
        $alerts        =$row["alerts"];      // How many alerts have they been sent for this
        $lastAlert      =$row["lastAlert"];      // Last time we emailed them
        $sinceLastAlert    =$row["sinceLastAlert"];  // Number of days since our last alert
        $clicks        =$row["clicks"];         // How many times did they click to read more
        $lastClick      =$row["lastClick"];      // Last time they clicked on the link
        
        $columns = 11;
        
        $keyword = strtolower($keyword);
        
        $keyword = trim($keyword);
        
        
        if (empty($lastAlert)) {
          
          $lastAlert = "Today";
          
        }

        if (empty($lastClick)) {
          
          $lastClick = "-";
          
        }
        
        if ($clicks > 0) {
        
          $ctr = $clicks/$alerts;
          $percent = round($ctr * 100);
          $showPercent = "$percent%";
        
        } else {
        
          $percent = 0;
          $showPercent = "-";
        }

        // !Should the search be deleted?
        if ($alerts > 4 && $percent < 10 && $sinceLastAlert > 1) {
          
          $deletedAlerts++;
                  
          print ("
          
          <tr>
          <td><a href='logPopup.php?action=view&uID=$uID&lightbox[iframe]=true&lightbox[width]=600&lightbox[height]=460' class='lightbox'>$firstName $lastName</a></td>
          <td>$lastLogin</td>
          <td>$lastMatch</td>
          <td>$shortSearchDate</td>
          <td>$keyword</td>
          <td>$alerts</td>
          <td>$lastAlert</td>
          <td>$clicks</td>
          <td>$lastClick</td>
          <td><font color='Red'>Deleted</font></td>
          </tr>
          
          "); 
          
          if ($processMethod == "execute") {
          
            $deleteAlerts = "delete from searches where sID = '$sID'";
            
            $result2 = mysql_query($deleteAlerts) OR die("<b>A fatal MySQL error occurred</b>.\n<br />Query: " . $deleteAlerts . "<br />\nError: (" . mysql_errno() . ") " . mysql_error()); 
          }
          
        } else {  
              
          // Process the match  
          
          $alerts++;
          $newMatches++;
          
          if ($clicks > 0) {
          
            $ctr = $clicks/$alerts;
            $percent = round($ctr * 100);
            $showPercent = "$percent%";
          
          } else {
          
            $percent = "0";
            $showPercent = "-";
          }
          
          print ("
          <tr>
          <td><a href='logPopup.php?action=view&uID=$uID&lightbox[iframe]=true&lightbox[width]=600&lightbox[height]=460' class='lightbox'>$firstName $lastName</a></td>
          <td>$lastLogin</td>
          <td>$lastMatch</td>
          <td>$shortSearchDate</td>
          <td>$keyword</td>
          <td>$alerts</td>
          <td>$lastAlert</td>
          <td>$clicks</td>
          <td>$lastClick</td>
          <td>$showPercent</td>
          </tr>
          
          ");


          if ($processMethod == "execute") {
            $testimonialURL = "http://www.oil-testimonials.com/login.php?tID=$tID&sID=$sID";
            
            $combinedWords = "$matchWords $exactWords";

            // Add rows to the matches table
            
            $matches_update = "INSERT IGNORE INTO matches (uID, sID, dateAdded, searchDate, searchKeywords, relatedKeywords, testimonialTitle, testimonialURL, dateScheduled) VALUES (
            
            '$uID', 
            '$sID',
            '$longDate',
            '$longSearchDate',
            '$keyword', 
            '$combinedWords',
            '".mysql_real_escape_string($testimonialTitle)."', 
            '$testimonialURL',
            '$scheduledDate')";
            
            mysql_query($matches_update) OR die("<b>A fatal MySQL error occurred</b>.\n<br />Query: " . $matches_update . "<br />\nError: (" . mysql_errno() . ") " . mysql_error()); 

                  
            // !Update the searches table
            
            $increaseAlerts = "UPDATE searches SET alerts = alerts +1, lastAlert = '$longDate' where sID = $sID";
            
            $result = mysql_query($increaseAlerts) OR die("<b>A fatal MySQL error occurred</b>.\n<br />Query: " . $increaseAlerts . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
          
            //if ($result === false) {
                
                // An error occurred while trying to update the searches table
              
              //$insertFailures++;                
      
            //}
          
          
            // !Update the users table
                      
            $updateLastMatch = "UPDATE users SET lastMatch = '$longDate' WHERE uID = '$uID'";
            
            mysql_query($updateLastMatch) OR die("<b>A fatal MySQL error occurred</b>.\n<br />Query: " . $updateLastMatch . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());     
          
          }
        
        } // End sending them a match notification
        
       
      } while($row = mysql_fetch_array($allMatchesResult));
      
        
      print ("<tr>");
      print ("<th colspan='$columns' align='center'>Total: $newMatches </th>");
      print ("</tr>");
      
      print ("</table>");


      // Update the testimonies table
      
      $approveQuery = "UPDATE testimonies SET approved = 'Yes', testimony  = '".mysql_real_escape_string($testimonialText)."', keywords = '$keywords', title = '".mysql_real_escape_string($testimonialTitle)."' where tID = $tID";
      mysql_query($approveQuery) OR die("<b>A fatal MySQL error occurred</b>.\n<br />Query: " . $approvedQuery . "<br />\nError: (" . mysql_errno() . ") " . mysql_error()); 


      // !Thank the author
      
      $message = "$authorFirstName, \n\n" .
      
      "The following testimonial has been approved and $newMatches members will now receive a match email. \n\n" .
      
      "$testimonialTitle \n\n" .
      
      "Click on the following link to verify how it looks on our website: \n\n" .
      
      "http://www.oil-testimonials.com/login.php?tID=$tID&vID=$uID \n\n" .
      
      "We appreciate the time you have given to help teach and encourage other people. \n\n\n" .
      
      "Best regards, \n\n" .
      
      "Support Team \n" .
      "Oil-Testimonials.com \n" .
      "Discover Essential Oil Truths \n\n\n" .

      "Page: approveTestimonial.php \n";

      $type    = "Thanks for Sharing";
      $subject  = "Thank You $authorFirstName";
  
        
      // Add the thank you for posting email to notifications
      
      $thanksUpdate = "INSERT IGNORE INTO notifications (dateAdded, priority, type, sender, recipient, subject, uID, message, dateScheduled) VALUES (
    
      '$longDate', 
      '1',
      '$type',
      '$sender',
      '$authorEmail', 
      '$subject', 
      '$authorID', 
      '".mysql_real_escape_string($message)."',
      '$longDate')";
  
      mysql_query($thanksUpdate) OR die("<b>A fatal MySQL error occurred</b>.\n<br />Query: " . $thanksUpdate . "<br />\nError: (" . mysql_errno() . ") " . mysql_error()); 

    
    } else {
    
      // No results from query
      
      print "<h3>Match Notifications: <font color = 'red'>0</font></h3>";
    }
    
    
    if ($newMatches  > 0) {
      
      print ("<h3>Deleted Searches: <font color = 'red'>$deletedAlerts</font></h3>");
    } 
    
    //if ($insertFailures  > 0) {
      
      //print ("<h3>Failed Inserts: <font color = 'red'>$insertFailures</font></h3>");
    //}     
 
    include 'toolsFooter.php';
  
  } elseif ($processMethod == "delete") { 

[busboy]

Wow, how did you straighten up my code? Your version of it looks better than mine. The code looks perfect in CODA2, which is what I use to develop my website. But I don't know how to paste the code into WYSIWYG html editor without it messing up the formatting.

Ugh!

[r937]

Wow, how did you straighten up my code?

using search and replace in a text editor to remove all those COLOR and FONT tags and replace tabs with spaces

[Jeff Mott]

I don't know how it happened, but the code that r937 posted doesn't look anything like the OP's code. And I don't mean just the formatting...

Busboy, you need to try posting readable code. What you posted seems to be missing an awful lot of newlines. Try using attachments if you can't get copy-paste to work. Also, this definitely doesn't look like all your code. It looks like you copied just a portion from the middle of your script. If the small portion you choose to show doesn't contain the bug you're trying to find, then obviously none of us will be able to help.

[Jeff Mott]

Looks like SitePoint might be partly to blame for the bad formatting and the missing content.

http://i.imgur.com/HfDjekD.png

[Jeff Mott]

oh yes it does

Am I seeing things differently than everyone else? (See my screenshot above.)

[r937]

must of beed a glitch in the matrix

my search/replace removed what was obvioulsy some sitepoint BBCODE, not busboy's php code

i swear it was there (post #13) when i first saw it, but it's gone now

[Jeff Mott]

i swear it was there (post #13) when i first saw it, but it's gone now

It turns out to be a good thing that you made a copy, because now that's our only copy.

It occurs to me, Busboy, that we don't yet know which table you're getting duplicates in. I see that you're inserting into a "matches" table from within a do-while loop. Any chance the matches table is the one you're having problems with?

[busboy]

The table that I'm currently seeing duplicates in is the notifications table. This is where I store emails that will be emailed out using a cron job. I'm attaching a .pdf of my code, which will hopefully make things much easier to read. Rudy, you will likely recognize some of the advanced queries.

http://oil-testimonials.com/examples/myCode.pdf

[busboy]

Hey, where did everyone go? All I hear is crickets.

:-)

[Jeff Mott]

I ran your code (though I had to fix several errors before it would run at all), but the notification query ran only once. From the code shown here, I can't find any reason for the duplicate inserts. You'll probably need to get the help of someone who you can allow access to your full application and database, so that person can reproduce and trace the issue.

[K. Wolfe]

So I'm assuming $longDate does not include the time? My guess is there are multiple http requests happening here.