• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 660
  • Last Modified:

php pdo loops

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
doctorbill
Asked:
doctorbill
  • 4
  • 2
3 Solutions
 
Julian HansenCommented:
can we see the xml file? Or at least a part of it that shows the structure and how items repeat?
0
 
doctorbillAuthor Commented:
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
 
Chris StanyonCommented:
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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
doctorbillAuthor Commented:
How does this fit into the xmlimport-form.php file?

ie what does the xmlimport-form.php look like with this code inserted ?
0
 
Chris StanyonCommented:
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
 
doctorbillAuthor Commented:
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
 
doctorbillAuthor Commented:
solution
0

Featured Post

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.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now