Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Parse XML file into MySQL Db - Db error message handling

Status
Not open for further replies.

mcmon999

IS-IT--Management
Mar 3, 2005
21
GB
Hi all,

I'm attempting to parse an XML file into a MySQL database using simpleXML but keep encountering the error

"Duplicate entry '203581' for key 'PRIMARY'"

Is it possible to skip these transactions? so the error does not occur.

Would it potentially be possible to do a IF statement and update the existing transaction?

Sorry if these are basic questions....the code is below:

<?php

$db_host = "localhost";
$db_name = "test";
$db_username = "root";
$db_password = "";

$connection = @mysql_pconnect($db_host, $db_username, $db_password) or
die("Unable to make connection to database server $db_host");
@mysql_select_db($db_name, $connection) or
die("Unable to find database $db_name");


if (file_exists('test.xml')) {
$xml = simplexml_load_file('test.xml');

} else {
exit('Failed to open test.xml.');
}

/* For each node, we insert a new record*/
foreach ($xml->enquiry as $enquiry) {

$enquiry_id = $enquiry->enquiry_id;
$type = $enquiry->type;
$user_id = $enquiry->user_id;
$merchant_id = $enquiry->merchant_id;
$merchant_name = $enquiry->merchant_name;
$network_merchant_id = $enquiry->network_merchant_id;
$affiliate_link = $enquiry->affiliate_link;
$date = $enquiry->date;
$amount = $enquiry->amount;
$commission = $enquiry->commission;
$reference = $enquiry->reference;
$customer_reference = $enquiry->customer_reference;
$description = $enquiry->description;
$notes = $enquiry->notes;
$created = $enquiry->created;
$sent_to_merchant = $enquiry->sent_to_merchant;
$resolution_deadline = $enquiry->resolution_deadline;

mysql_query("INSERT INTO enquiry VALUES ('$enquiry_id','$type', '$user_id','$merchant_id','$merchant_name', '$network_merchant_id', '$affiliate_link', '$date', '$amount', '$commission', '$reference', '$customer_reference', '$description', '$notes', '$created', '$sent_to_merchant', '$resolution_deadline') ") or die(mysql_error());

}

?>

Thanks in advance!
 
remove the 'or die()'. command. instead just log the error so you can check manually later.
Code:
$query = "INSERT INTO enquiry VALUES ('$enquiry_id','$type', '$user_id','$merchant_id','$merchant_name', '$network_merchant_id', '$affiliate_link', '$date', '$amount', '$commission', '$reference', '$customer_reference', '$description', '$notes', '$created', '$sent_to_merchant', '$resolution_deadline')  " ;
$result = mysql_query($query);

if (!$result){
  $error = mysql_error();
  echo <<<HTML
error with query:
$query
Error was:  $error <br/><hr/>
HTML;
 
Excellent thanks jpadie, this worked perfectly.

It now outputs all the query errors, the majority of them being for illegal characters or where the data has been encoded for XML.

Such as:

&apos;

But fails when importing these into the database.

Is there a small way to remove or encoded them so that MySQL will import them into the Db?

Many thanks.
 
maybe something like this? i can't see why utf-8 strings should cause the code to fail though.
Code:
foreach ($xml->enquiry as $enquiry) {
$enquiry = cleanse($enquiry);

$enquiry_id = $enquiry->enquiry_id;
$type = $enquiry->type;
$user_id = $enquiry->user_id;
$merchant_id = $enquiry->merchant_id;
$merchant_name = $enquiry->merchant_name;
$network_merchant_id = $enquiry->network_merchant_id;
$affiliate_link = $enquiry->affiliate_link;
$date = $enquiry->date;
$amount = $enquiry->amount;
$commission = $enquiry->commission;
$reference = $enquiry->reference;
$customer_reference = $enquiry->customer_reference;
$description = $enquiry->description;
$notes = $enquiry->notes;
$created = $enquiry->created;
$sent_to_merchant = $enquiry->sent_to_merchant;
$resolution_deadline = $enquiry->resolution_deadline;
}
function cleanse($obj){
  $n = new stdclass; 
  foreach ($obj as $f=>$v){
     $n->$f = mysql_real_escape_string($v);
   }
   return $n;
}
 
Brilliant, thanks very much for your help!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top