I'm working on a project where I upload a CSV and update a MySQL table. At the end of my sql insert statement I have an "on duplicate key update..." statement.
My problem is, PDO rowCount() seems to be returning 2x for updated rows. For example, when I upload the CSV the first time, I get a total of 100 rows (count of csv rows) and rowCount returns 100, which makes sense because I inserted 100 rows.
However, if I upload the same file again, all 100 rows are updated (I update a unix timestamp), and rowCount returns 200. I assume this is because rowCount returns 2 for each update and 1 for an insert.
Are my assumptions correct? Has anyone run into this before and is there a solution that doesn't involve 100 separate insert statements? I would like to be able to display the total number of rows in the csv, the total new rows inserted, and the total rows updated.
$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 ){
/* do some post processing for special characters */
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,',') . '),';
}
/*
detect duplicate projects based on project_number & project_type
mysql unique index created with (project_number + project_type)
if duplicate found, update row
*/
$sql = rtrim($sql,',');
$sql .= 'ON DUPLICATE KEY UPDATE';
foreach($fields as $key=>$val){
$sql .= ' ' . $val . ' = VALUES(' . $val . '),';
}
$sql = rtrim($sql,',');
/* update database */
$query = $this->dbc->prepare($sql);
if( $query->execute($tmp) ){
$result = array('total_rows'=>$rowCount,'modified_rows'=>$query->rowCount());
}
/* return result */
return $result;
Here is the query generated for a 3 row 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);
ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE. The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's CLIENT_FOUND_ROWS flag is set.
PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.
The Insert on Duplicate Key Update statement is the extension of the INSERT statement in MySQL. When we specify the ON DUPLICATE KEY UPDATE clause in a SQL statement and a row would cause duplicate error value in a UNIQUE or PRIMARY KEY index column, then updation of the existing row occurs.
According to the MySQL manual:
With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated and 0 if the existing row is set to its current values.
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With