Insert single xml element into mysql database.

Opt1kal · September 24, 2010

Ok,

So here's my issue, I'm having trouble finding a way to extract a single xml element and insert it into a mysql database column. Here's my code so far:

<?php
class shipwire{

function orderFulfillmentService($type = null){
	if(!isset($type))
		$this->fullFillOrder();
	else
		$this->fullFillOrder();	
}

function checkout_process_wo_cron(){
	//Generate a unique id to serve as lock. 
	$rand = mt_rand(0, 32);
	$lock_id = md5($rand.time());		

	//Get five pending orders.Also let other threads know I am working on these orders and they need not use these.
	$query = "Update ".TABLE_ORDERS." set lock_id = ".$this->quote($lock_id).", lock_timestamp = now() where 
	lock_id is null and shipwire_notified is null limit 5";		
	tep_db_query($query);

	//Get the orders that are locked by me
	$query = "select orders_id, delivery_name, delivery_street_address, delivery_suburb, delivery_city, delivery_state,
	delivery_country, delivery_postcode, customers_telephone, customers_email_address from ".TABLE_ORDERS." where shipwire_notified is null and 
	lock_id = ".$this->quote($lock_id);

	$pending_orders = tep_db_query($query);

	//Generate XML for these pending orders which has to be sent to Shipwite

	//If there is atleast one pending order, transmit them to Shipwire		
	if(isset($resp['order_list']) && !empty($resp['order_list'])){			 
		$this->fullFillOrder( $xml, $resp['order_list'] );
	}

	//Free the locks as I am done
	$query = "Update ".TABLE_ORDERS." set lock_id = null, lock_timestamp = null where lock_id = ".$this->quote($lock_id);
	tep_db_query($query);

	//Need to free up dangling locks which has been inactive for more than an hour
	$query = "Update ".TABLE_ORDERS." set lock_id = null, lock_timestamp = null where shipwire_notified is null and 
	lock_id is not null and ( now() - lock_timestamp ) > 3600 ";
	tep_db_query($query);
}

//This function will be called when running using a cron. It checks all pending orders which has not been notified to Shipwire, generates 
//XML and calls the Shipwire API
function checkout_process_w_cron(){		
	//Finding out all orders pending to be transmitted to Shipwire
	$query = "select orders_id, delivery_name, delivery_street_address, delivery_suburb, delivery_city, delivery_state, delivery_country,delivery_postcode, customers_telephone, customers_email_address from orders";

	$pending_orders = tep_db_query($query);

	//Generating XML for pending orders

	//Calling Shipwire Fullfilment service				
	if(isset($resp['order_list']) && !empty($resp['order_list']))			 
		$this->fullFillOrder( $xml, $resp['order_list'] );
}

//This function will transmit pending orders to Shipwire.
function fullFillOrder(){

	$query = "select orders_id, delivery_name, delivery_street_address, delivery_suburb, delivery_city, delivery_state, delivery_country,delivery_postcode, customers_telephone, customers_email_address from orders where shipwire_notified is null";
	$order_list = array();
	$pending_orders = tep_db_query($query);

	$order = tep_db_fetch_array($pending_orders);

		//getting all productsfor a order and creating items sql	
		$query = "select products_model, products_quantity from ".TABLE_ORDERS_PRODUCTS." where orders_id = ".$order['orders_id'];
		$items = tep_db_query($query);
		$i = 0;
		while($item = tep_db_fetch_array($items)){
			$item_xml .= '<itemlist><item+prodid="'.$item['products_model'].'"+quantity="'.$item['products_quantity'].'"+/></itemlist>';		
		}

	$url_to_file = 'https://www.papesinc.com/pls/papes/ext?data=<?xml+version="1.0"+encoding="UTF-8"?>+<order>+<ctid>BD5DE1625F694EA4A892C860ADD2B1F5</ctid><shpaddr>+<name>'.urlencode($order['delivery_name']).'</name>+<street1>'.urlencode($order['delivery_street_address']).'</street1><street2>'.urlencode($order['delivery_suburb']).'</street2>+<city>'.urlencode($order['delivery_city']).'</city>+<state>'.urlencode($order['delivery_state']).'</state><zip>'.urlencode($order['delivery_postcode']).'</zip></shpaddr><shipmthd>UPS</shipmthd>'.$item_xml.'</order>';

$local_file = 'order.xml';
$ch = curl_init($url_to_file);
$fp = fopen($local_file, "w");

curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); 
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true); 
curl_setopt($ch, CURLOPT_AUTOREFERER, true); 
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 120); 
curl_setopt($ch, CURLOPT_TIMEOUT, 120); 
curl_setopt($ch, CURLOPT_MAXREDIRS, 10); 
curl_setopt($ch, CURLOPT_VERBOSE, 1); 
curl_setopt($ch, CURLOPT_FILE, $fp); 
curl_setopt($ch, CURLOPT_HEADER, 0); 
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 1); 
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
curl_exec($ch);
curl_close($ch);
fclose($fp);
tep_db_connect() or die('Unable to connect to database);
$oDOM = new DOMDocument();
$oDOM->loadXML(file_get_contents($local_file));
foreach ($oDOM->getElementsByTagName('orderid') as $porderid)
{
   $sSQL = sprintf(
       "INSERT INTO orders (papes_id) VALUES ('%s') where order_id = ' . $this->quote(orderid) . ' ",
       mysql_real_escape_string($porderid->getElementsByTagName('orderid')->item(0)->nodeValue)
   );
   $rResult = mysql_query($sSQL);

   if(mysql_errno() > 0)
   {
       printf(
           '<h4 style="color: red;">Query Error:</h4>
           <p>(%s) - %s</p>
           <p>Query: %s</p>
           <hr />',
           mysql_errno(),
           mysql_error(),
           $sSQL
       );
   }
}
}
function quote($value)
{
	// Stripslashes
	if (get_magic_quotes_gpc()) {
		$value = stripslashes($value);
	}
	// Quote if not integer
	if (!is_numeric($value)) {
		$value = "'" . mysql_real_escape_string($value) . "'";
	}
	return $value;
}
}
?>

Now the only part of the code that needs to run is the

function fullFillOrder()

This is the xml that I need to get the element from, I need the Order ID

<?xml version="1.0" encoding="utf-8" ?>
<!-This is a response document that you can expect for an order request-->
<order>
<!-- an Order ID, you will use this value to request shipping information -->
<orderid>999999999</orderid>
<!-this is detailed information about what you ordered and exact amounts charged."amteach"
is the amount that Pape's will charge the dealer when invoiced. As you can see, the
precision can be arbitrary due to discount structures.
-->
<itemlist> <item prodid="ABC123" quantity="42" amteach="99999.32938" /><item
prodid="DEF456" quantity="21" amteach="0.0123" />
</itemlist>
</order>

Wondering if anyone is able to help me out.

♥mdtaylorlrim · September 24, 2010

Mario, from working with you on the last project I assume that your $local_file is being written to correctly and that you have a valid xml file as a result of posting the order, is that right?

Can you test this with a test order to be sure the data is good?

If the $local_file is a good xml then try this.

$oDOM = new DOMDocument();
$oDOM->loadXML(file_get_contents($local_file));
$order = $oDOM->getElementsByTagName('order');
$porderid = $order->getElementsByTagName('orderid');
echo $porderid;  // debuging

See what value is echo'd, if any. Hopefully I did not conflict with any variable names.

Opt1kal · September 24, 2010

Plugged in that code and got this error.

Fatal error: Call to undefined method DOMNodeList::getElementsByTagName() in /home/content/15/5050415/html/catalog/includes/classes/shipwire.php on line 101

I did test this with a test order before plugging in that code and was able to get a(what I thought to be) valid xml back, here's what that was:

<?xml version="1.0" encoding="utf-8" ?>

































<!--

This is a response document that you can expect for an order request

-->

<order>

<!-- an Order ID, you will use this value to request shipping information -->

<orderid>79009</orderid>



<!--

	this is detailed information about what you ordered and exact amounts charged.

	"amteach" is the amount that Pape's will charge the dealer when invoiced. As

	you can see, the precision can be arbitrary due to discount structures.

-->

<itemlist>





		<item prodid="6302" quantity="1" amteach="0.98" />



</itemlist>

</order>

I'm wondering if the xml quotes is throwing everything off.

♥mdtaylorlrim · September 24, 2010

I'm wondering if the xml quotes is throwing everything off.

I'm gonna do some live test with that xml file and see if I can get it for you..

Opt1kal · September 24, 2010

oook.

♥mdtaylorlrim · September 25, 2010

Incorporate this code.. and since you only have one order returned per xml file you can trash the foreach loop.

<?php

$local_file = 'order.xml';

$oDOM = new DOMDocument();
$oDOM->loadXML(file_get_contents($local_file));
$order = $oDOM->getElementsByTagName('order');
$porderid = $order->item(0)->nodeValue; 
echo $porderid;  // debuging


?>

Opt1kal · September 25, 2010

Awesome man, that worked. Thank you so much, now to just get the rest of what needs to be done...which right now is updating the database with the orderid.

Mark, I swear if we ever meet man, drinks are on me all night. =)

Opt1kal · September 25, 2010

Ok, I didn't notice this before, but this sql command:

update orders set papes_id = ' . $porderid . ' where date_purchased = now ()

Is outputting this:

update orders set papes_id = 

79481








' where date_purchased = now ()

How would I get rid of all the enters? because it should look like:

update orders set papes_id = 79481 where date_purchased = now ()

♥mdtaylorlrim · September 25, 2010

Maybe this...

update orders set papes_id = $porderid where date_purchased = now ()

or if the porderid must be in quotes...

update orders set papes_id = "'" . $porderid . "'" where date_purchased = now ()

Opt1kal · September 25, 2010

Ok, So I tried this...

function fullFillOrder(){

	$query = "select orders_id, delivery_name, delivery_street_address, delivery_suburb, delivery_city, delivery_state, delivery_country,delivery_postcode, customers_telephone, customers_email_address from orders where papes_id is null";
	$order_list = array();
	$pending_orders = tep_db_query($query);

	$order = tep_db_fetch_array($pending_orders);

		//getting all productsfor a order and creating items sql	
		$query = "select products_model, products_quantity from ".TABLE_ORDERS_PRODUCTS." where orders_id = ".$order['orders_id'];
		$items = tep_db_query($query);
		$i = 0;
		while($item = tep_db_fetch_array($items)){
			$item_xml .= '<itemlist><item+prodid="'.$item['products_model'].'"+quantity="'.$item['products_quantity'].'"+/></itemlist>';		
		}

	$url_to_file = 'https://www.papesinc.com/pls/papes/ext?data=<?xml+version="1.0"+encoding="UTF-8"?>+<order>+<ctid>BD5DE1625F694EA4A892C860ADD2B1F5</ctid><shpaddr>+<name>'.urlencode($order['delivery_name']).'</name>+<street1>'.urlencode($order['delivery_street_address']).'</street1><street2>'.urlencode($order['delivery_suburb']).'</street2>+<city>'.urlencode($order['delivery_city']).'</city>+<state>'.urlencode($order['delivery_state']).'</state><zip>'.urlencode($order['delivery_postcode']).'</zip></shpaddr><shipmthd>UPS</shipmthd>'.$item_xml.'</order>';

$local_file = 'order.xml';
$temp_file = 'orderid.tmp';
$ch = curl_init($url_to_file);
$fp = fopen($local_file, "w");

curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); 
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true); 
curl_setopt($ch, CURLOPT_AUTOREFERER, true); 
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 120); 
curl_setopt($ch, CURLOPT_TIMEOUT, 120); 
curl_setopt($ch, CURLOPT_MAXREDIRS, 10); 
curl_setopt($ch, CURLOPT_VERBOSE, 1); 
curl_setopt($ch, CURLOPT_FILE, $fp); 
curl_setopt($ch, CURLOPT_HEADER, 0); 
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 1); 
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
curl_exec($ch);
curl_close($ch);
fclose($fp);
$oDOM = new DOMDocument();
$oDOM->loadXML(file_get_contents($local_file));
$order = $oDOM->getElementsByTagName('order');
$porderid = $order->item(0)->nodeValue;
foreach($order_list as $order_id){
	$ssql = 'Update orders set papes_id = ' . $porderid . ' where orders_id = '.$this->quote($order_id);
	mysql_query($ssql);
}
 }

And got:

Internal Server Error

The server encountered an internal error or misconfiguration and was unable to complete your request.

Please contact the server administrator, support@supportwebsite.com and inform them of the time the error occurred, and anything you might have done that may have caused the error.

More information about this error may be available in the server error log.

Yeah......

♥mdtaylorlrim · September 25, 2010

Well, that is such a generic error message you have to look in the error_log to see the exact source of the error.

But...in looking at the logic of your function this is what should happen:

SQL to select all orders that do not yet have a papes_order_id

loop through the result set and for each:

read the items on the order

Send the url to place the order at papes

receive an xml file with the papes order id

sql to update the local order with the papes orderid

end loop

Is that how you think it should be?

What I see in your code is this:

SQL to select orders that do not yet have a papes_order_id

loop through the result set and for each

read the items on the order

send a url to place the order at papes

receive and xml file with the papes order id

loop through $order_list ?? what is this?

end loop

So, this code:

       foreach($order_list as $order_id){
               $ssql = 'Update orders set papes_id = ' . $porderid . ' where orders_id = ' . $this->quote($order_id) ;
               mysql_query($ssql);
       }

I'm not real sure what you are doing. You only need to write the one value to the one line in the table. This should not take a loop to do. I think, what you are thinking is looping through the items on the order.... but you only need to mark the order in the orders table which does not contain the items. So perhaps your code should be something like this:

               $ssql = 'Update orders set papes_id = ' . $porderid . ' where orders_id = '. $order['orders_id'] ;
               mysql_query($ssql);

Although, I think the Internal Server Error is due to the quotes. It takes me a while to get them right some times.

Opt1kal · September 25, 2010

Ok, so I'm now using this code:

function fullFillOrder(){

	$query = "select orders_id, delivery_name, delivery_street_address, delivery_suburb, delivery_city, delivery_state, delivery_country,delivery_postcode, customers_telephone, customers_email_address from orders where papes_id is null";
	$order_list = array();
	$pending_orders = tep_db_query($query);

	$order = tep_db_fetch_array($pending_orders);

		//getting all productsfor a order and creating items sql	
		$query = "select products_model, products_quantity from ".TABLE_ORDERS_PRODUCTS." where orders_id = ".$order['orders_id'];
		$items = tep_db_query($query);
		$i = 0;
		while($item = tep_db_fetch_array($items)){
			$item_xml .= '<itemlist><item+prodid="'.$item['products_model'].'"+quantity="'.$item['products_quantity'].'"+/></itemlist>';		
		}

	$url_to_file = 'https://www.papesinc.com/pls/papes/ext?data=<?xml+version="1.0"+encoding="UTF-8"?>+<order>+<ctid>BD5DE1625F694EA4A892C860ADD2B1F5</ctid><shpaddr>+<name>'.urlencode($order['delivery_name']).'</name>+<street1>'.urlencode($order['delivery_street_address']).'</street1><street2>'.urlencode($order['delivery_suburb']).'</street2>+<city>'.urlencode($order['delivery_city']).'</city>+<state>'.urlencode($order['delivery_state']).'</state><zip>'.urlencode($order['delivery_postcode']).'</zip></shpaddr><shipmthd>UPS</shipmthd>'.$item_xml.'</order>';

$local_file = 'order.xml';
$temp_file = 'orderid.tmp';
$ch = curl_init($url_to_file);
$fp = fopen($local_file, "w");

curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); 
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true); 
curl_setopt($ch, CURLOPT_AUTOREFERER, true); 
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 120); 
curl_setopt($ch, CURLOPT_TIMEOUT, 120); 
curl_setopt($ch, CURLOPT_MAXREDIRS, 10); 
curl_setopt($ch, CURLOPT_VERBOSE, 1); 
curl_setopt($ch, CURLOPT_FILE, $fp); 
curl_setopt($ch, CURLOPT_HEADER, 0); 
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 1); 
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
curl_exec($ch);
curl_close($ch);
fclose($fp);
$oDOM = new DOMDocument();
$oDOM->loadXML(file_get_contents($local_file));
$order = $oDOM->getElementsByTagName('order');
$porderid = $order->item(0)->nodeValue;
foreach($order_list as $order_id){
$ssql = 'Update orders set papes_id = ' . $porderid . ' where orders_id = '.$this->quote($order_id);
   mysql_query($ssql);
   }
 }

And it's not giving me any errors that I can see, but it's also not entering anything into the database...no idea why...

♥mdtaylorlrim · September 25, 2010

Ok, so I'm now using this code:

And it's not giving me any errors that I can see, but it's also not entering anything into the database...no idea why...

What did you change to stop the error? Please don't make me look for it, LOL.

We posted about the same time...

Opt1kal · September 25, 2010

It was actually something that wasn't related to this at all that I know of,...somehow the orders_id column got set to NOT auto-increment. No idea how or what did it, but after some trial and error I figured it out, set it back, and it's fine now. So I don't know....

♥mdtaylorlrim · September 25, 2010

Oh, it looks like nothing is being written to the database because $order_list is not populated with anything, I think. See my post about that foreach loop above. I don't think it is necessary, but I may be missing something.

Opt1kal · September 25, 2010

Alright just tried this code:

$oDOM = new DOMDocument();
$oDOM->loadXML(file_get_contents($local_file));
$order = $oDOM->getElementsByTagName('order');
$porderid = $order->item(0)->nodeValue;
$ssql = 'Update orders set papes_id = ' . $porderid . ' where orders_id = '. $order['orders_id'];
   mysql_query($ssql);
 }

And got this error:

Fatal error: Cannot use object of type DOMNodeList as array in /home/content/15/5050415/html/catalog/includes/classes/order.php on line 102

♥mdtaylorlrim · September 25, 2010

Alright just tried this code:

$oDOM = new DOMDocument();
$oDOM->loadXML(file_get_contents($local_file));
$order = $oDOM->getElementsByTagName('order');
$porderid = $order->item(0)->nodeValue;
$ssql = 'Update orders set papes_id = ' . $porderid . ' where orders_id = '. $order['orders_id'];
   mysql_query($ssql);
 }

And got this error:

Fatal error: Cannot use object of type DOMNodeList as array in /home/content/15/5050415/html/catalog/includes/classes/order.php on line 102

Yeah, I see why now. I use the variable $order in the line of code $order = $oDOM->getElementByTagName('order') and you had previously used the variable $order way up in $order = tep_db_fetch_array($pending_orders);

So, you will have to rename the one I used to something else in the code above so that the $order['orders_id'] value is preserved throughout the function.

Understand?

Opt1kal · September 25, 2010

Yeah, I see why now. I use the variable $order in the line of code $order = $oDOM->getElementByTagName('order') and you had previously used the variable $order way up in $order = tep_db_fetch_array($pending_orders);

So, you will have to rename the one I used to something else in the code above so that the $order['orders_id'] value is preserved throughout the function.

Understand?

Mark, I want to hump your leg right now....haha, thank you so much for the help.

♥mdtaylorlrim · September 25, 2010

Mark, I want to hump your leg right now....haha, thank you so much for the help.

And with that I am going to retire for the night. Or try to. And hope I don't have nightmares over this....

Insert single xml element into mysql database.

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived