[Resolved] error trapping an insert statement

[cbrknight]

I have a page that is importing data from a csv file. I am then inserting certain parts of that data into a database.

If the data is unique then the insert completes fine. However if there is a duplicate entry... ie.. employee tries to import the same data twice it give a duplicate entry "field" key 1 error.

Is there any way I can trap this error so I can send the user to a new page and give instructions on how to get rid of duplicate entries?

Thanks..

[bastien]

The trick here is to write all the error data to a text file and present that to the user as bad data

instead of having the execution of the query look like

$result = mysql_query($sql) or die(mysql_error());

which kills the script, you need to trap the error like this

//loop to load the csv data

$result = mysql_query($sql); 

if (mysql_errno($result)!= 0){
  //write out the bad data to a file
  ...
}

//end loop
?>

[/code]

[cbrknight]

I tried to do it but it is not doing anything.. I commented out

//$importdata = mysql_query($query_importdata, $importer) or die(mysql_error());

and added

$importdata = mysql_query($query_importdata, $importer);

if (mysql_errno($importdata)!= 0){
//write out the bad data to a file
echo ("there is an error in the database...");
}

now it skips the insert query alltogether... can you look at my code and tell me what I did wrong?

Thanks....

<start code>

<?php require_once('importer.php'); ?>
<?
$filename = $_POST['filename'];

$row = 1;
$fp = fopen ("$filename","r");
while ($data = fgetcsv ($fp, 1000, ",")) {
$num = count ($data);
$first = $data[0];
$last= $data[1];
$valid = $data[7];
mysql_select_db($database_importer, $importer);
$query_importdata = "INSERT into users_copy (fName, lName, validation) values ('$first', '$last', '$valid')";
//$importdata = mysql_query($query_importdata, $importer) or die(mysql_error());

$importdata = mysql_query($query_importdata, $importer);

if (mysql_errno($importdata)!= 0){
//write out the bad data to a file
echo ("there is an error in the database...");
}

//end loop

/*

*/
$row++;
for ($c=0; $c < $num; $c++) {
// print $data[$c] . "<br>\n";

}

}
fclose ($fp);
print "Records from $filename were successfully inserted!<br><br>";

$return = @unlink($filename);

if ($return == true){
echo "<br>$filename was deleted from the server";
}
?>

</end code>

[Roger_Ramjet]

I'm not really clear what it is you want.

my guess

You want to add the new data that is unique
Not have this insert fail even if there is duplicate data
Report back to the user if there is duplicate data and list what it is.

My pragmatic solution, robust and reliable, and one that I use all the time because I get this all the time

Insert the data into an intermediate table.
Compare it to the main table and list duplicates.
Delete the duplicates and then run the insert from the intermediate table to the main table if there is anything still left.

This approach also allows me to run validation checks on the imported data before I add it to the main table. Beats having some dolt corrupt my main table.

As it is, any duplicate keys will abort the insert unless you use the ON DUPLICATE KEY IGNORE syntax. This does not mean that it will allow duplicate keys, only that the error is downgraded to a warning so the insert continues to run. I have not yet found any way to get a list of the records that generated warnings.

[bastien]

Your script wasn't off much..more my error in the error checking (wrong variable for the link)

try this

<?php 
require_once('importer.php'); 

$filename = $_POST['filename']; 

$row = 1; 
$fp = fopen ("$filename","r"); 
while ($data = fgetcsv ($fp, 1000, ",")) 
{ 

$first = $data[0]; 
$last  = $data[1]; 
$valid = $data[7]; 

mysql_select_db($database_importer, $importer); 

$query_importdata = "INSERT into users_copy (fName, lName, validation) values ('$first', '$last', '$valid')"; 

$importdata = mysql_query($query_importdata, $importer); 

if (mysql_errno($importer)!= 0){ 
	//write out the bad data to a file 
	echo ("there is an error in the database..."); 
} //end if

}//wend 
fclose ($fp);

print "Records from $filename were successfully inserted!<br><br>"; 

$return = @unlink($filename); 

if ($return == true){ 	
	echo "<br>$filename was deleted from the server"; 
} //end if
?>