Solved

php pdo loops

Posted on 2013-11-01
8
640 Views
Last Modified: 2013-11-01
Attached file:

This objct is defined in the xml file being imported:
$orderdesc2 = (string)$obj->body->ORDER->ORDERHEAD->FUNCDESC;

I have just noted that the data  in the xml file is repeated but the insert command I have only inserts once. Please can someone show me how to insert a loop for this variable
such that it inserts the total number of records for that variable from the xml file into the database

Insert statement:

$stmt = $dbh->prepare("INSERT INTO retailers (ret_name, ret_add, ret_add2) VALUES (:name, :orderdesc, :orderdesc2)");


$stmt->bindParam('name', $name);
$stmt->bindParam('orderdesc', $orderdesc);
$stmt->bindParam('orderdesc2', $orderdesc2);

//execute the query
$stmt->execute();
xmlimport-form.php
0
Comment
Question by:doctorbill
  • 4
  • 2
8 Comments
 
LVL 56

Assisted Solution

by:Julian Hansen
Julian Hansen earned 167 total points
ID: 39616311
can we see the xml file? Or at least a part of it that shows the structure and how items repeat?
0
 

Author Comment

by:doctorbill
ID: 39616321
Sorry - my mistake. I gace the incorrect repeat region:
The repeat region is: body-ORDER-ORDEDRLINE-PRODUCT
If I could just see the mechanism of how to enter the repeat

xml attached
Purchase-Order-FI2-4098098.xml
0
 
LVL 43

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 333 total points
ID: 39616335
You would need to loop through the node in your XML and run the query->execute() in there:

$dbh = new PDO("mysql:host=localhost;dbname=1callorders", "user", "pass");
		
$xml = file_get_contents('data.xml');
$obj = simplexml_load_string($xml);

//prepare the query
$stmt = $dbh->prepare("INSERT INTO retailers (ret_name, ret_add, ret_add2) VALUES (:name, :orderdesc, :orderdesc2)");

//bind the named parameter to PHP variables
$stmt->bindParam('name', $name);
$stmt->bindParam('orderdesc', $orderdesc);
$stmt->bindParam('orderdesc2', $orderdesc2);

$name = (string)$obj->header->manifest->document->name;
$orderdesc = (string)$obj->body->ORDER->ORDERHEAD->ORDERDESC;

//loop through the orderline to get each product description
foreach ($obj->body->ORDER->ORDERLINE as $orderline):
	$orderdesc2 = $orderline->PRODUCT->DESCRIPTION;	

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

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:doctorbill
ID: 39616352
How does this fit into the xmlimport-form.php file?

ie what does the xmlimport-form.php look like with this code inserted ?
0
 
LVL 43

Accepted Solution

by:
Chris Stanyon earned 333 total points
ID: 39616367
The only difference is the loop. Other than that your code stays the same (although for some reason you seem to be echoing out some information before the opening DOCTYPE!). Here's the PHP part in full:

<?php 
$dbh = new PDO("mysql:host=localhost;dbname=retailers", "user", "pass");
		
$file = $_POST['xmlfile'];
$filepath = "xmlimport/";

$xml = file_get_contents($filepath . $file);
$obj = simplexml_load_string($xml);
		
//prepare the query
$stmt = $dbh->prepare("INSERT INTO q_28282860 (ret_name, ret_add, ret_add2) VALUES (:name, :orderdesc, :orderdesc2)");

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

$name = (string)$obj->header->manifest->document->name;
$orderdesc = (string)$obj->body->ORDER->ORDERHEAD->ORDERDESC;

//loop through the orderline to get each product description
foreach ($obj->body->ORDER->ORDERLINE as $orderline):
	$orderdesc2 = $orderline->PRODUCT->DESCRIPTION;	

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

//run the second query
$stmt = $dbh->prepare("INSERT INTO ret_orders (ord_linetype) VALUES (:name)");
$stmt->bindParam('name', $name);
$stmt->execute();
?>

Open in new window

0
 

Author Comment

by:doctorbill
ID: 39616499
Thanks very much - working
It amazes me how you guys do this in seconds when it takes me ages to see the wood for the trees !!
0
 

Author Closing Comment

by:doctorbill
ID: 39616503
solution
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

730 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