database record single line

The attached file "xmlimport_form_loop" does the following:

Imports an xml file and adds the order to a database in SEPARATE rows  by looping through and using "$orderline->PRODUCT->DESCRIPTION;" as an index
It is working perfectly for this job

I have attached a partially edited file called "xmlimport_form_loop_singleline" which I need to do the following:

Add the data in a SINGLE line and add "orderdesc2, orderdesc3 and orderdesc4" in 3 consecutive columns)
There will only be three of this column data to add

This line has been edited but I have not been able to go any further:
$stmt = $dbh->prepare("INSERT INTO ret_orders (ret_id, ord_prodtpe, ord_prodcdedesc, ord_proddesc2, ord_proddesc3, ord_proddesc4) VALUES (:ret_id, :name, :orderdesc, :orderdesc2, :orderdesc3, :orderdesc4 )");

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

Code for second script moved into the code snippet:
<?php 

$dbh = new PDO("mysql:host=localhost;dbname=1callorders", "root", "billadmin2006");
		
$i = 1;

do {
   echo $i;

if($_GET['xmlfile'.$i] !="") { // check if the value is empty

$file = $_GET['xmlfile'.$i];

// SKIP THE EMPTY FILE POSITIONS
//if (empty($file)) continue;

$filepath = "xmlimport/";

$xml = file_get_contents($filepath . $file);
$obj = simplexml_load_string($xml);

//echo out statements from xml
//$name = (string)$obj->header->manifest->document->name;
//$orderdesc = (string)$obj->body->ORDER->ORDERHEAD->ORDERDESC;
//$orderdesc2 = (string)$obj->body->ORDER->ORDERHEAD->FUNCDESC;

$space = "&nbsp;";
//echo "Name: " . $name . $space;
//echo "Desc: " . $orderdesc . $space;
//echo "Desc2: " . $orderdesc2 . $space;
//end statements
		
//prepare the query
$stmt = $dbh->prepare("INSERT INTO ret_orders (ret_id, ord_prodtpe, ord_prodcdedesc, ord_proddesc2, ord_proddesc3, ord_proddesc4) VALUES (:ret_id, :name, :orderdesc, :orderdesc2, :orderdesc3, :orderdesc4 )");

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

$ret_id = "DRM";
$name = (string)$obj->header->manifest->document->name;
$orderdesc = (string)$obj->body->ORDER->ORDERHEAD->ORDERDESC;
//$orderdesc2 = (string)$obj->body->ORDER->ORDERLINE->ORDER->ORDERHEAD->FUNCDESC;

//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
//$stmt2 = $dbh->prepare("INSERT INTO ret_orders (ord_linetype) VALUES (:name)");
//$stmt2->bindParam('name', $name);
///$stmt2->execute();
}
$i++;
} while ($i <= 5);

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body><br /><br />


<?php
$i = 1;
$break = "<br />";
$space = "&nbsp;";

do {
   //echo $i;
if($_GET['xmlfile'.$i]!=""){ // check if the value is empty
$filenames = $_GET['xmlfile'.$i];

echo "File Imported:".$space.$filenames.$break;
}
$i++;
} while ($i <= 5);

?>

</body>
</html>

Open in new window

xmlimport-form-loop.php
xmlimport-form-loop-singleline.php
doctorbillTechAsked:
Who is Participating?
 
Ray PaseurConnect With a Mentor Commented:
Thanks.  I cannot test this because I do not have your data base, but it looks more or less workable depending on a couple of things.  The most critical of the dependencies is the number of columns in the data base table.  If there are not the same number of columns in the table as the number of data elements in the XML document, the query will not work well, or may not work at all.

Looking at the XML, it appears that there is a one-to-many relationship between the ORDER and the ORDERLINE data.  That suggests that there could be one ORDERLINE or any arbitrarily large number of ORDERLINE elements.   With the data base in its current form, you need exactly three ORDERLINE elements for things to work out right.  Not sure you can count on that.

This would probably be the right time to get a professional DBA involved to show you some good designs that are a little more future-proof.  I'm sure that you need to refactor the data base structure, but without hands-on knowledge of the business rules, I can't really tell you how.  The DBA can study those rules and help you get to a workable design.

<?php // RAY_temp_doctorbill.php
error_reporting(E_ALL);

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

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$dsn = "mysql:host=$db_host;dbname=$db_name";
try
{
    $dbh = new PDO($dsn, $db_user, $db_word);
}
catch(PDOException $exc)
{
    var_dump($exc);
    die(' NO PDO Connection');
}


// SET PDO TO TELL US ABOUT WARNINGS OR TO THROW EXCEPTIONS
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );


// GET THE XML INTO AN OBJECT
$url = 'http://filedb.experts-exchange.com/incoming/2014/01_w03/828888/Purchase-Order-FI2-4098098.xml';
$xml = file_get_contents($url);
$obj = simplexml_load_string($xml);


// EXTRACT THE INFORMATION WE NEED FROM THE XML DOCUMENT
$ret_id = "DRM";
$name   = (string)$obj->header->manifest->document->name;

// THIS IS DIFFERENT FROM THE OTHER PRODUCT DESCRIPTIONS
$orderdesc[] = (string)$obj->body->ORDER->ORDERHEAD->ORDERDESC;

// GET THE PRODUCT DESCRIPTIONS
foreach ($obj->body->ORDER->ORDERLINE as $orderline)
{
    $orderdesc[] = (string)$orderline->PRODUCT->DESCRIPTION;
}


// CREATE THE VALUES CLAUSE
$values = '(:ret_id, :name, ';
foreach ($orderdesc as $key => $val)
{
    $values .= ':orderdesc' . $key . ',';
}
$values = rtrim($values, ',');
$values .= ')';


// CREATE THE SQL STATEMENT
$sql =
"
INSERT INTO ret_orders
( ret_id
, ord_prodtpe
, ord_prodcdedesc
, ord_proddesc2
, ord_proddesc3
, ord_proddesc4
)
VALUES $values
"
;
// PREPARE THE QUERY AND BIND THE DATA
try
{
    $stmt = $dbh->prepare($sql);
} catch(PDOException $e)
{
    trigger_error($e->getmessage(), E_USER_ERROR);
}
$stmt->bindParam('ret_id', $ret_id);
$stmt->bindParam('name', $name);
$stmt->bindParam('orderdesc0', $orderdesc[0]);
$stmt->bindParam('orderdesc1', $orderdesc[1]);
$stmt->bindParam('orderdesc2', $orderdesc[2]);
$stmt->bindParam('orderdesc3', $orderdesc[3]);


// SHOW THE QUERY STRING FOR VISUAL VERIFICATION THAT IT LOOKS OK
echo PHP_EOL . $sql;


// RUN THE QUERY
try
{
    $stmt->execute();
}
catch(PDOException $e)
{
    trigger_error($e->getmessage(), E_USER_ERROR);
}

Open in new window

0
 
Ray PaseurConnect With a Mentor Commented:
Please post the test data for the new script, thanks.  Without it any answer you get will be mostly speculative and untested.  With it we can make some tests and probably give you a better answer with a working code example.
0
 
doctorbillTechAuthor Commented:
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
doctorbillTechAuthor Commented:
Give me a little time to test this
0
 
doctorbillTechAuthor Commented:
Ray,
The file  (code) you posted above works fine - thanks so much for that

I wonder if I can be a little cheaky and ask 2 more questions - I can put this as an
formal additional question if you like

1. I have edited the file you gave me by putting in a loop which is intended to take
a maximum of five file inputs sent from another form and run through them,
putting the data into the database as individual rows (max o0f 5)
As it stands at the moment, if I send through 5 files only one file
is being processed (the first one on the form)

2. I have now been told that the xml file may contain different numbers of
input fields for the following - I will need to cater for up to 15
numbered sequentially:

$stmt->bindParam('ret_id', $ret_id);
$stmt->bindParam('name', $name);
$stmt->bindParam('orderdesc0', $orderdesc[0]);
$stmt->bindParam('orderdesc1', $orderdesc[1]);
$stmt->bindParam('orderdesc2', $orderdesc[2]);
$stmt->bindParam('orderdesc3', $orderdesc[3]);

As it stands at the moment If the xml file does not have exactly the right amount of input fields
I get an error message telling me that the bound parameters do not match with the input parameters
ray-passeur-xmlimport-singleline.php
0
 
Ray PaseurConnect With a Mentor Commented:
The most critical of the dependencies is the number of columns in the data base table.  If there are not the same number of columns in the table as the number of data elements in the XML document, the query will not work well, or may not work at all.
That's sort of what I expected to have happen next.  That's why you need a DBA to get hands-on with the design of the relational data base.  If you stay on this path everything will be perfect until the client says, "Oh, now i need 17 input fields."  And then it will be a huge amount of non-value-added work just to keep things working.

I wish I could help more, but it's not really a question that has any one answer -- it's a requirement for systems analysis and application development, and there are many layers of questions and answers that will be needed to get this working right.

Best of luck with the project, ~Ray
0
 
doctorbillTechAuthor Commented:
solved
0
 
Ray PaseurCommented:
Thanks for using EE and thanks for the points, ~Ray
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.