Solved

database record single line

Posted on 2014-01-17
8
240 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
  • 4
  • 4
8 Comments
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 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 108

Accepted Solution

by:
Ray Paseur earned 500 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
 

Author Comment

by:doctorbill
ID: 39788909
Give me a little time to test this
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 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 108

Expert Comment

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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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 how to create an extensible mechanism for linked drop downs.
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…
The viewer will learn how to dynamically set the form action using jQuery.

747 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

12 Experts available now in Live!

Get 1:1 Help Now