php xml loop

Code I am interested in:

The following code in the attached file is supposed to add the "orderdesc2" val;ues into sequential database columns named it2, it3 and it4   using the $i value in a loop. The first two values ($name and $orderdesc) are added but the values assigned to the $i variable do not bget anything

-------------------------------

//prepare the query
$stmt = $dbh->prepare("INSERT INTO orders(custName, orderDetails, it$i) 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
for ($i=2; $i <= 4; $i++) {
      $orderdesc2 = $orderline->PRODUCT->DESCRIPTION;      }
      //execute the query
      $stmt->execute();
Purchase-Order-FI2-4098098-edit.xml
xmlimport-form-loop.php
doctorbillTechAsked:
Who is Participating?
 
Chris StanyonConnect With a Mentor Commented:
You need something like this:

<?php
$dbh = new PDO("mysql:host=localhost;dbname=yourDB", "user", "pass");
$xml = file_get_contents('data.xml');
$obj = simplexml_load_string($xml);
		
$name = $obj->header->manifest->document->name;
$orderdesc = $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;	
endforeach;

$sqlString = "INSERT INTO orders (custName, orderDetails, it2, it3, it4) VALUES (:name, :orderdesc, :it2, :it3, :it4)";
$stmt = $dbh->prepare($sqlString);

$data = array(
	'name' => $name,
	'orderdesc' => $orderdesc,
	'it2' => isset($orderdesc2[0]) ? $orderdesc2[0] : null,
	'it3' => isset($orderdesc2[1]) ? $orderdesc2[1] : null,
	'it4' => isset($orderdesc2[2]) ? $orderdesc2[2] : null
);

$stmt->execute($data);
?>

Open in new window

0
 
Ray PaseurConnect With a Mentor Commented:
There is only one occurrence of "ORDERDESC" in the XML document.  There are four occurrences of PRODUCT->DESCRIPTION.  What do you want to do with the information in these fields?
0
 
Chris StanyonConnect With a Mentor Commented:
That won't work because you preparing the query with a variable, so it will be prepared with the value of $i at that time. It does seem a little weird doing it like that. You'll end up with 4 rows of data, all containing the same information, but in different columns - is that really what you intended?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
doctorbillTechAuthor Commented:
What I want is the following:
1 row of date
CustName column has the $name value
OrderDetails column has the $orderdesc value
it$i (it2, it3, it4) columns have the values from the $orderdesc2 as a sequence

So, there will be 1 row and 5 columns added
0
 
Ray PaseurConnect With a Mentor Commented:
I can get you this far... You will have the variables in easy-to-access form. There are exactly five data elements.  You can put them into your query.  Use array notation to access the string contents of the array elements.  The ORDERDESC is a string.

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

// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28284598.html

// ACQUIRE THE DATA
$url = 'http://filedb.experts-exchange.com/incoming/2013/11_w45/686193/Purchase-Order-FI2-4098098-edit.xml';
$xml = file_get_contents($url);
$obj = simplexml_load_string($xml);

// ACTIVATE THIS TO SEE THE ENTIRE DATA OBJECT
// var_dump($obj);

// TEASE OUT THE PARTS WE WANT
$orderdesc = (string)$obj->body->ORDER->ORDERHEAD->ORDERDESC;

foreach ($obj->body->ORDER->ORDERLINE as $o)
{
    $product_descriptions[] = (string)$o->PRODUCT->DESCRIPTION;
}

// SHOW THE EXTRACTED DATA
var_dump($orderdesc);
var_dump($product_descriptions);

Open in new window

HTH, ~Ray
0
 
Chris StanyonConnect With a Mentor Commented:
OK. Let me just point out that your database design is flawed. What would you do if there were 30 products on the order - you certainly wouldn't create 30 columns to hold date. You should be creating 2 tables for your order information. One for the order details and one for the products ordered.
0
 
doctorbillTechAuthor Commented:
I quite agree but the database I am working with is set like it is
Is there a way of doing what I am trying to do with the $i loop ?
0
 
Chris StanyonConnect With a Mentor Commented:
Yes there is, but it will break at some point!!

Do you want to just add the first 3 product descriptions to columns it2, it3 and it4, and just drop the other product descriptions?
0
 
doctorbillTechAuthor Commented:
yes please - so I can see the mechanism
0
 
Ray PaseurConnect With a Mentor Commented:
Don't do it with a "$i loop" - just address each of the data elements in the $product_descriptions array individually.  The loop is confusing you.  The data elements you want are named like this:

$product_descriptions[0]
$product_descriptions[1]
etc.

Also, what ChrisStanyon said.  This data base design is nearly unusable and will cause you to lose the data if anyone orders five or more things.  You should probably stop what you're doing and get a data base administrator to help you redesign the application.
0
 
doctorbillTechAuthor Commented:
Can you put that in the code please

Yes - I will take on board what you say re. the database and start using 2 tables
0
 
doctorbillTechAuthor Commented:
I see what you mean about complications arising !!

Thanks
0
 
Chris StanyonConnect With a Mentor Commented:
Yup :)

It's a process called database normalisation. You would have 2 tables - one for orders and one for products (orderlines). Your orders table could have an order id, order description, order date etc, and your orderlines table would have order id, product description etc. Then when you create an order you would add one line to the orders table, and x number of lines to the orderlines table - each line tagged with the orderid
0
 
doctorbillTechAuthor Commented:
Exactly what I will be doing with it
Thanks very much all
0
 
doctorbillTechAuthor Commented:
Solution
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.