xml and php

I am trying to import the attached xml file into a mysql database using php
The problem is that there are records that are repeated in the file

Can someone please give me some pointers please
Purchase-Order-FI2-4098098.xml
doctorbillTechAsked:
Who is Participating?
 
Chris StanyonConnect With a Mentor Commented:
Right. Nowhere in your code are you binding data to the query. You also seem to be trying to insert the value of $p into your database, which is handled in a loop, so you would only ever insert the last value. Finally, you don't name Query parameters the same as PHP - they don't have the $.

I would trim this code right back, just to get the DB part working - Ray's code is really a learning resource and isn't meant to be used as a final solution.

Have a look at this simplified version:

<?php
$dbh = new PDO("mysql:host=localhost;dbname=yourDb", "user", "pass");
		
$xml = file_get_contents('data.xml');
$obj = simplexml_load_string($xml);
		
$name = (string)$obj->header->manifest->document->name;
echo "Name: " . $name;

//prepare the query with a named parameter called 'name'
$stmt = $dbh->prepare("INSERT INTO retailers (ret_name) VALUES (:name)");

//bind the named parameter to a PHP variable (called $name)
$stmt->bindParam('name', $name);

//execute the query
$stmt->execute();	
?>

Open in new window

0
 
Chris StanyonConnect With a Mentor Commented:
If your database is setup correctly (i.e. your table column is set as UNIQUE INDEX), then when you try to insert a duplicate, you will generate a error. Just catch this error and move on.
0
 
Chris StanyonConnect With a Mentor Commented:
Further to my last comment, the duplicate key error number is 1062. Using PDO, you would do something like this:

try {

   $stmt = $dbh->prepare("INSERT into yourTable (column1, column2) VALUES (:column1, :column2)");

   $data = array(
      'column1' => 'Value 1',
      'column2' => 'Value 2'
   );

   $stmt->execute($data);	

} catch (Exception $e) {
   if ($e->errorInfo[1] == 1062) {
      echo "Duplicate Entry";
   }
	
   var_dump($e->errorInfo);
}

Open in new window

If you want to ignore the error silently, just change your IF statement in the catch block:

if ($e->errorInfo[1] != 1062) {
   //we have an error other than a duplicate
   var_dump($e->errorInfo);
}

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Ray PaseurConnect With a Mentor Commented:
The problem is that there are records that are repeated in the file
Why is that a problem?

This looks like a perfectly understandable purchase order for furniture.
http://www.laprbass.com/RAY_temp_doctorbill.php

<?php // RAY_temp_doctorbill.php
error_reporting(E_ALL);
echo '<pre>';

// ACQUIRE THE XML AND MAKE AN OBJECT
$url = 'http://filedb.experts-exchange.com/incoming/2013/10_w44/685195/Purchase-Order-FI2-4098098.xml';
$xml = file_get_contents($url);
$obj = simplexml_load_string($xml);
print_r($obj);

Open in new window

Please post the CREATE TABLE statement for your data base tables and show us how the XML tags relate to the columns of the tables.
0
 
doctorbillTechAuthor Commented:
I haven't started that yet.
I am in the process of setting this up
How does the php code take the xml data and place it in the database? Can you show me using a dummy script so I get the just of the workings
0
 
Chris StanyonConnect With a Mentor Commented:
Which data do you want inserting? How you do it will also depend on your data table structure, so we'll need to see that, and as Ray said - how does the XML file relate to the tables
0
 
doctorbillTechAuthor Commented:
This is the data I need inserting:
[body] => SimpleXMLElement Object
        (
            [ORDER] => SimpleXMLElement Object
                (
                    [ORDERHEAD] => SimpleXMLElement Object
                        (
                            [@attributes] => Array
                                (
                                    [ORDERTYPE] => PUO
                                    [FUNCCODE] => FIO
                                )

                            [ORDERDESC] => Purchase Order
                            [FUNCDESC] => Firm Order
                            [CURRENCY] => SimpleXMLElement Object
                                (
                                    [@attributes] => Array
                                        (
                                            [CURRTYPE] => ORD
                                            [CURRCODE] => GBP
                                        )

                                    [CURRTYPEDESC] => Order Currency
                                    [CURRCODESC] => United Kingdom Pounds
                                )

                        )

                    [REFERENCE] => Array
                        (
                            [0] => SimpleXMLElement Object
                                (
                                    [@attributes] => Array
                                        (
                                            [REFTYPE] => CUR
                                        )

                                    [REFDESC] => Customers Reference
                                    [REFCODE] => STKPO434968
                                )

                            [1] => SimpleXMLElement Object
                                (
                                    [@attributes] => Array
                                        (
                                            [REFTYPE] => CCN
                                        )

                                    [REFDESC] => Customers Customer Name
                                    [REFCODE] => Mrs V. M BAKER
                                )

                            [2] => SimpleXMLElement Object
                                (
                                    [@attributes] => Array
                                        (
                                            [REFTYPE] => ACR
                                        )

                                    [REFDESC] => Alternate Customer Reference
                                    [REFCODE] => 001A002130
                                )

                        )

My table is just consisting of col1,col2 etc...
It is called orders_db
database: postorders

Sorry to appear so vague but this is totally new to me. If I can have a pointer as to the structure of the php code to do this I could then work on it. Is it possible to put the code (above) together to show me how it all works as a unit ?
0
 
Ray PaseurConnect With a Mentor Commented:
This is more of a systems-analysis problem than a question with any one answer.  I can show you how to tease information out of the XML document by using OO notation and the SimpleXML object.  But you'll be the one who has to design the data base (or you'll hire a data base administrator to design it for you).

Please see: http://www.laprbass.com/RAY_temp_doctorbill.php

<?php // RAY_temp_doctorbill.php
error_reporting(E_ALL);
echo '<pre>';

// ACQUIRE THE XML AND MAKE AN OBJECT
$url = 'http://filedb.experts-exchange.com/incoming/2013/10_w44/685195/Purchase-Order-FI2-4098098.xml';
$xml = file_get_contents($url);
$obj = simplexml_load_string($xml);

// SHOW HOW TO ACCESS SOME PARTS OF THE OBJECT
$x = (string)$obj->header->delivery->message->messageID;
echo "messageID: $x";
echo PHP_EOL;

$x = (string)$obj->header->manifest->document->name;
echo "name: $x";
echo PHP_EOL;

// SHOW HOW TO USE AN ITERATOR TO ACCESS REPEATED PARTS OF THE OBJECT
foreach ($obj->body->ORDER->REFERENCE as $r)
{
    echo ' REFTYPE: ' . $r['REFTYPE'];
    echo ' REFCODE: ' . $r->REFCODE;
    echo PHP_EOL;
}

foreach ($obj->body->ORDER->SUPPLIER->ADDRESS->NAME as $n)
{
    echo "NAME: $n";
    echo PHP_EOL;
}

// USE AN ITERATOR TO ACCESS A NESTED OBJECT
foreach ($obj->body->ORDER->ORDERLINE as $o)
{
    // TEASE OUT PARTS OF THE NESTED OBJECT
    $p = (string)$o->PRODUCT->DESCRIPTION;
    echo "PRODUCT DESCRIPTION: $p";
    echo PHP_EOL;
}

// ACTIVATE THIS TO SHOW THE ENTIRE OBJECT
// print_r($obj);

Open in new window

0
 
doctorbillTechAuthor Commented:
Ray - thanks so much for the code - it really explains exactly what is going on
Final question:
If my database has a name "dbname" and columns called "col1", "col2" etc ..., what would the insert php code look like to take the above echoed variables into the databse
0
 
Ray PaseurConnect With a Mentor Commented:
It wouldn't have those names and columns, I don't  think.  It would be a relational data base, with several tables that form a structure of some sort to mirror the XML document.

Can you tell us a little more about what you want to do with the data base and how much data you expect to receive?
0
 
doctorbillTechAuthor Commented:
basically how to use a sql INSERT statement to put the variables into a database
0
 
Ray PaseurConnect With a Mentor Commented:
Like I said earlier this isn't a question with any succinct answer -- it's a systems analysis problem with many, many moving parts.  You or someone you hire will have to understand the content, meaning and value of the information in the XML documents, and come up with a design strategy for the data base, including how many tables, how many columns, the relational nature of the tables, etc.

To load the data into tables, you would use the same kind of SQL INSERT that is used for any string variable.  For example if you wanted to insert the product description $p (created at line 37, above) the query would say something like this:

INSERT INTO myTable ( productDescription ) VALUES ( '$p')

If you have more variables, you just list the column names separated by commas, and list the values enclosed in single quotes separated by commas.

If you're new to using SQL, this book will be a welcome addition to your professional library.  Buy the latest edition and when they come out with a new edition, throw your old copy away and buy the new one.
http://www.amazon.com/PHP-MySQL-Web-Development-Edition/dp/0321833899
0
 
doctorbillTechAuthor Commented:
Last question I promise:
How would the $r variables be inserted as repeats into the table  on line 22-23?
0
 
Ray PaseurConnect With a Mentor Commented:
I don't really know how to answer.  $r has attribute(s) and value(s).  

    echo ' REFTYPE: ' . $r['REFTYPE'];  // Attribute
    echo ' REFCODE: ' . $r->REFCODE;  // Value

You would probably want to cast these data elements to string and assign the strings to variables that you would put into the INSERT query.  Maybe something like this pidgin code:

$rt = (string)$r['REFTYPE'];
$rc = (string)$r->REFCODE;
$sql = INSERT... VALUES ('$rt', '$rc')
0
 
doctorbillTechAuthor Commented:
This is my attempt to get 2 variables into the database (sorry - I know it is one more question):
-------------------------------
<?php
//Connect to the database
$dbh = new PDO("mysql:host=localhost;dbname=1callorders", "name", "password");

error_reporting(E_ALL);
echo '<pre>';

// ACQUIRE THE XML AND MAKE AN OBJECT
$url = 'http://localhost/comwebsites/1callorders/site/xmlimport/Purchase-Order-FI2-4098098.xml';
$xml = file_get_contents($url);
$obj = simplexml_load_string($xml);

// SHOW HOW TO ACCESS SOME PARTS OF THE OBJECT
$x = (string)$obj->header->delivery->message->messageID;
echo "messageID: $x";
echo PHP_EOL;

$x = (string)$obj->header->manifest->document->name;
echo "name: $x";
echo PHP_EOL;

// SHOW HOW TO USE AN ITERATOR TO ACCESS REPEATED PARTS OF THE OBJECT
foreach ($obj->body->ORDER->REFERENCE as $r)
{
    echo ' REFTYPE: ' . $r['REFTYPE'];
    echo ' REFCODE: ' . $r->REFCODE;
    echo PHP_EOL;
}

foreach ($obj->body->ORDER->SUPPLIER->ADDRESS->NAME as $n)
{
    echo "NAME: $n";
    echo PHP_EOL;
}

// USE AN ITERATOR TO ACCESS A NESTED OBJECT
foreach ($obj->body->ORDER->ORDERLINE as $o)
{
    // TEASE OUT PARTS OF THE NESTED OBJECT
    $p = (string)$o->PRODUCT->DESCRIPTION;
    echo "PRODUCT DESCRIPTION: $p";
    echo PHP_EOL;
}

// ACTIVATE THIS TO SHOW THE ENTIRE OBJECT
// print_r($obj);

//prepare the query
$stmt = $dbh->prepare("INSERT INTO retailers (ret_id, ret_name) VALUES (:$x, :$p)");


      $stmt->execute();

?>

Result:
I just get this printed to the page:

messageID:
name:
0
 
Ray PaseurConnect With a Mentor Commented:
@doctorbill: We can't help with this file:

$url = 'http://localhost/comwebsites/1callorders/site/xmlimport/Purchase-Order-FI2-4098098.xml';

That's on your computer and we can't get to it.  The script I posted here at EE worked just fine with the test data you posted here at EE.  But life's too short to guess at what you have hidden on your local machine!

We can answer question for you and the like, but we can't teach you advanced concepts in PHP.  Some of it has to come through a foundation of knowledge in the principles of computer science and of the language.  To that end, this article will help you get a good foundation, and more importantly will help you avoid the many bad "learning resources" that litter the internet.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

trying to import the attached xml file into a mysql database
It's a plain fact that anyone can see from the length of the dialog here, that this is not a question.  It's a requirement for application development and for something like that you should expect to hire a professional developer.  If you want to become a professional application developer, be prepared to give a lot of time and attention to learning all of the things that a professional application developer already knows.  The article above will help guide your learning.

If you want to use PDO prepared statements, this article shows exactly how to do it.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

I write these articles because I don't have enough personal time to answer the same questions over and over again.  If you read the articles and do not understand some part of them, please post a comment in the article or a question here about the article and I will clarify them as soon as I can.

Best of luck with your project, ~Ray
0
 
doctorbillTechAuthor Commented:
that works !!!

I have tried this for an extra variable but it gives an error:
Name: P-ORD-EXPDesc: PHP Warning: PDOStatement::execute() [function.PDOStatement-execute]: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in I:\CommercialWebsites\1callorders\site\xmlimport.php on line 20

Extra variable I am trying to add:
<ORDERDESC>Purchase Order</ORDERDESC>

---------
<?php
$dbh = new PDO("mysql:host=localhost;dbname=1callorders", "user", "password");
            
$xml = file_get_contents('xmlimport/Purchase_Order_FI2-4098098.xml');
$obj = simplexml_load_string($xml);
            
$name = (string)$obj->header->manifest->document->name;
$orderdesc = (string)$obj->header->manifest->document->orderdesc;
echo "Name: " . $name;
echo "Desc: " . $orderdesc;

//prepare the query with a named parameter called 'name'
$stmt = $dbh->prepare("INSERT INTO retailers (ret_name, ret_add) VALUES (:name, :orderdesc)");

//bind the named parameter to a PHP variable (called $name)
$stmt->bindParam('name', $name);
$stmt->bindParam('desc', $orderdesc);

//execute the query
$stmt->execute();      
?>
0
 
Chris StanyonConnect With a Mentor Commented:
Right. Firstly this doesn't exist (unless your XML file is different to what you've posted):

$obj->header->manifest->document->orderdesc;

Look at your XML file and you'll see that the header->manifest->document has a 'description', not an 'orderdesc'. This however, does exist:

$obj->body->ORDER->ORDERHEAD->ORDERDESC

so maybe that's what you need (CASE is important!).

The error you get is because you've named your parameters 'name' and 'orderdesc' in your query, but bound them to 'name' and  'desc'. You need this:

$stmt->bindParam('name', $name);
$stmt->bindParam('orderdesc', $orderdesc);
0
 
doctorbillTechAuthor Commented:
Thanks to all - I really do appreciate the time you have all spent teaching me in this exercise.
0
 
doctorbillTechAuthor Commented:
0
 
doctorbillTechAuthor Commented:
Excellent learning experience
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.