I am working on a project where I download a CSV and update a MySQL table. At the end of my sql insert statement, I have the statement "on duplicate key update ...".
My problem: PDO rowCount () seems to return 2x for updated rows. For example, when I load CSV for the first time, I get a total of 100 lines (the number of csv lines), and rowCount returns 100, which makes sense because I inserted 100 lines.
However, if I download the same file again, all 100 lines are updated (I update the unix timestamp), and rowCount returns 200. I assume this is because rowCount returns 2 for each update and 1 for insertion.
Are my assumptions correct? Has anyone come across this before and is there a solution that does not include 100 separate insert statements? I would like to be able to display the total number of lines in csv, insert new new lines and update full lines.
$sql = 'INSERT INTO projects (' . implode($fields,',') . ') VALUES'; $rowCount = count($csvData); $tmp = array(); for( $i = 0; $i < $rowCount; $i++ ){ $placeholders = array(); foreach( $fields as $key=>$val ){ switch($val){ case 'description': $value = !empty($csvData[$i][$_POST[$val]]) ? $csvData[$i][$_POST[$val]] : NULL; array_push($tmp,$value); break; case 'country': $value = !empty( $csvData[$i][$_POST[$val]] ) ? implode(' ',array_unique(explode(' ', $csvData[$i][$_POST[$val]]))) : NULL; $value = str_replace(array(',','.','\''),'',$value); array_push($tmp,$value); break; case 'add_unixtime': array_push($tmp,time()); break; case 'project_type': array_push($tmp,strtolower($formData['project_type'])); break; default: $value = !empty($csvData[$i][$_POST[$val]]) ? str_replace(array(',','.','\''),'',$csvData[$i][$_POST[$val]]) : NULL; array_push($tmp,$value); break; } array_push($placeholders,'?'); } $sql .= ' (' . implode($placeholders,',') . '),'; } $sql = rtrim($sql,','); $sql .= 'ON DUPLICATE KEY UPDATE'; foreach($fields as $key=>$val){ $sql .= ' ' . $val . ' = VALUES(' . $val . '),'; } $sql = rtrim($sql,','); $query = $this->dbc->prepare($sql); if( $query->execute($tmp) ){ $result = array('total_rows'=>$rowCount,'modified_rows'=>$query->rowCount()); } return $result;
Here is the query generated for a 3-line insert.
INSERT INTO projects (project_number, project_value, project_name, address1, address2, city, state, zip, country, description, project_type, add_unixtime ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?), (?,?,?,?,?,?,?,?,?,?,?,?), (?,?,?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE project_number = VALUES(project_number), project_value = VALUES(project_value), project_name = VALUES(project_name), address1 = VALUES(address1), address2 = VALUES(address2), city = VALUES(city), state = VALUES(state), zip = VALUES(zip), country = VALUES(country), description = VALUES(description), project_type = VALUES(project_type), add_unixtime = VALUES(add_unixtime);