Why is this PDO Insert executing twice?

Question

This has been inserting each line from the csv into the database twice, and now today three times. Nothing else I put in the loop happens more than it should.

$file_handle = fopen("uploads/numbers.csv", "r");    
$stmt = $db->prepare("INSERT INTO database
(firstname,lastname,phonenumber) VALUES
(:field1,:field2,:field3)");

while (($line_of_data = fgetcsv($file_handle, 1000, ",")) !== FALSE) 
{
$stmt->execute(array(':field1' => $line_of_data [0], ':field2' => $line_of_data[1], ':field3' => $line_of_data[2]));
}

Verify that your script isn't actually being _called twice_. This commonly happens due to things like empty `src=''` attributes in images, stylesheet hrefs, or even if you have a rewrite rule routing everything into index.php and you are missing a favicon.ico. The empty attributes result in a second HTTP request to the main request's same URI and may be invisible to you when you are debugging in the browser. Start by looking in your web server's access logs to see if there are 2 requests for this script when you only expect one... — Michael Berkowski, Jun 08 '14 at 17:41
can you give the real sample content of `uploads/numbers.csv`? — Hieu Vo, Jun 08 '14 at 17:55
Maybe numbers.csv contains duplicates, or data already in database. — James, Jun 08 '14 at 17:58
The csv is like this: john,doe,15555555555 and contains no duplicates. I have tried this on two separate webhosts now. I don't know how to check the access logs for 1and1 but my appfog does show two entries at the same exact time for the url when I reload the page. It is happening twice at appfog and three times at 1and1. But I have pared the page down to literally nothing but the connection to the database, and what I wrote above. There is no html at all. So what could it be? — TestText, Jun 08 '14 at 18:34
Could be your browser trying to "preload" the page before you actually browse to it — hank, Jun 08 '14 at 19:53
@James Correct. Or each line in the CSV could have gremlins or stray newlines that are not easily viewable. — Giacomo1968, Jun 08 '14 at 19:59

score 0 · Accepted Answer · answered Jun 08 '14 at 19:46

0

Setup a proper primary key on database. Either (firstname, lastname) or (firstname, lastname, phonenumber) depending on the usage. Ta-da, no more duplicates.

I'm going to assume James was right in the columns by the fact that the CSV contains preexisting data in the database, but either way, a primary key will prevent duplicates.

If you use the firstname, lastname key and you want to have the script be able to update the phone number, you could use REPLACE instead of INSERT.

answered Jun 08 '14 at 19:46

Devon Bessemer

34,461
9
69
95

Well I will check this as it technically does fix the problem, though still leaving it unexplained. But I found that it works on my phone correctly, just not on my computer. I wonder if it could be a spyware issue. I'm going to download and run another anti-spyware program and see if that fixes it. Thanks everyone for the help. – TestText Jun 08 '14 at 20:24
@TestText, every table in your database should have a primary key. Just remember that. There should always be some unique way to identify rows, it doesn't have to be a single column, it can be a composite key like I recommended above. – Devon Bessemer Jun 08 '14 at 20:25

score 0 · Answer 2 · answered Jun 08 '14 at 19:55

Your check is here:

while (($line_of_data = fgetcsv($file_handle, 1000, ",")) !== FALSE)

First, you don’t need the !== FALSE. It can just be this:

while (($line_of_data = fgetcsv($file_handle, 1000, ",")))

Also, your code is just checking while that fgetcsv is not empty. So what if there is an empty line in the file? It gets run twice. So how about this:

while (($line_of_data = trim(fgetcsv($file_handle, 1000, ",")))) {
  if (!empty($line_of_data)) {
    $stmt->execute(array(':field1' => $line_of_data [0], ':field2' => $line_of_data[1], ':field3' => $line_of_data[2]));
  }
}

The idea is that when you call fgetcsv let’s trim the line to get rid of extra stuff like maybe a line break at the end of the line. Then the if (!empty($line_of_data)) { checks if the line is not empty & only acts on the query if it is definitely not empty. If somehow trim(fgetcsv(…)) doesn’t work, you can do it this way instead:

while (($line_of_data = fgetcsv($file_handle, 1000, ","))) {
  if (!empty(trim($line_of_data))) {
    $stmt->execute(array(':field1' => $line_of_data [0], ':field2' => $line_of_data[1], ':field3' => $line_of_data[2]));
  }
}

With all of that logic in if (!empty(trim($line_of_data))) {.

empty() would be useful to avoid PHP/SQL errors but it wouldn't fix the duplicate data (other than blank data) since $line_of_data would be empty on an empty line. — Devon Bessemer, Jun 08 '14 at 20:02
@Devon Fair enough, but there is just not much else to go on here. Most likely the code is being called twice & the original poster just doesn’t notice it. — Giacomo1968, Jun 08 '14 at 20:06

Why is this PDO Insert executing twice?

2 Answers2