Solved

php xml loop

Posted on 2013-11-04
15
418 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
  • 7
  • 5
  • 3
15 Comments
 
LVL 108

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 42

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
 
LVL 108

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 42

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 42

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

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

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 42

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 42

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
This article discusses four methods for overlaying images in a container on a web page
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

746 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