Solved

php xml loop

Posted on 2013-11-04
15
427 Views
Last Modified: 2013-11-04
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
0
Comment
Question by:doctorbill
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
15 Comments
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 188 total points
ID: 39621306
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
 
LVL 43

Assisted Solution

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

Author Comment

by:doctorbill
ID: 39621331
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
Industry Leaders: 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!

 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 188 total points
ID: 39621340
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
 
LVL 43

Assisted Solution

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

Author Comment

by:doctorbill
ID: 39621350
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
 
LVL 43

Assisted Solution

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

Author Comment

by:doctorbill
ID: 39621366
yes please - so I can see the mechanism
0
 
LVL 110

Assisted Solution

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

Author Comment

by:doctorbill
ID: 39621375
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
 
LVL 43

Accepted Solution

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

Author Comment

by:doctorbill
ID: 39621412
I see what you mean about complications arising !!

Thanks
0
 
LVL 43

Assisted Solution

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

Author Comment

by:doctorbill
ID: 39621444
Exactly what I will be doing with it
Thanks very much all
0
 

Author Closing Comment

by:doctorbill
ID: 39621445
Solution
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

751 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