Solved

xml and php

Posted on 2013-10-29
22
392 Views
Last Modified: 2013-10-30
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
0
Comment
Question by:doctorbill
  • 10
  • 7
  • 5
22 Comments
 
LVL 42

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 208 total points
ID: 39608275
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
 
LVL 42

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 208 total points
ID: 39608286
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
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 292 total points
ID: 39608370
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
 

Author Comment

by:doctorbill
ID: 39608487
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
 
LVL 42

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 208 total points
ID: 39608524
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
 

Author Comment

by:doctorbill
ID: 39608589
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
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 292 total points
ID: 39608642
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
 

Author Comment

by:doctorbill
ID: 39608812
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
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 292 total points
ID: 39608925
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
 

Author Comment

by:doctorbill
ID: 39609186
basically how to use a sql INSERT statement to put the variables into a database
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 292 total points
ID: 39609250
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:doctorbill
ID: 39609661
Last question I promise:
How would the $r variables be inserted as repeats into the table  on line 22-23?
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 292 total points
ID: 39609701
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
 

Author Comment

by:doctorbill
ID: 39611251
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
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 208 total points
ID: 39611298
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
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 292 total points
ID: 39611345
@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
 

Author Comment

by:doctorbill
ID: 39611355
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
 
LVL 42

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 208 total points
ID: 39611387
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
 

Author Comment

by:doctorbill
ID: 39611431
Thanks to all - I really do appreciate the time you have all spent teaching me in this exercise.
0
 

Author Comment

by:doctorbill
ID: 39611697
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 292 total points
ID: 39611785
0
 

Author Closing Comment

by:doctorbill
ID: 39611967
Excellent learning experience
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now