?
Solved

database record single line

Posted on 2014-01-17
8
Medium Priority
?
249 Views
Last Modified: 2014-02-07
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
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
  • 4
  • 4
8 Comments
 
LVL 111

Assisted Solution

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

Author Comment

by:doctorbill
ID: 39788557
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 39788755
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
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!

 

Author Comment

by:doctorbill
ID: 39788909
Give me a little time to test this
0
 

Author Comment

by:doctorbill
ID: 39796571
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
 
LVL 111

Assisted Solution

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

Author Closing Comment

by:doctorbill
ID: 39843330
solved
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39843406
Thanks for using EE and thanks for the points, ~Ray
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
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.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

800 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