Importing xml into mysql inventory

Opt1kal · September 19, 2010

Ok, so I don't know if this would go here, but I've been looking all over for some sort of help on this and have found no where to be very helpful as of yet.

I am in need of some help in updating a MySQL database that contains the inventory for an online store. The distributor has given me the xml that needs to be send and an example of what the xml response is going to look like. The response is going to consist of around 15,000 items with model number, quantity, along with manufacturer and a few others. What I need to do is set up a cron that will send the xml to their api take the response and update the mysql database with the required information. And I'm at a complete loss as to how to approach this, I have tried several different ways and each way has not worked.

The xml that needs to be sent:

<?xml version="1.0" encoding="utf-8" ?>
<inventory><ctid>ACCOUNTNUMBER</ctid>
</inventory>

The response from the distributor:

<?xml version="1.0" encoding="utf-8" ?>
<inventory>
<itemlist>
<item prodid="AA02" quantity="42" price="1.16" map="0.0" mfgitem="AA02" mfgname="MAPLE LEAF PRESS
INC" />
<item prodid="ACXR410" quantity="0" price="321.91" map="379.99" mfgitem="775011" mfgname="EASTON
TECHNICAL PRODUCTS" />
<item prodid="AS1" quantity="6" price="10.67" map="13.99" mfgitem="MBC SOX LOW S" mfgname="TEAM J
SALES AND MARKETIN" />
</itemlist>
</inventory>

This was my latest crack at it that didn't work:

function stock_update() {

 $url = 'https://www.papesinc.com/pls/papes/ext';
 $username = variable_get('username', '');

 $xml = '<?xml version="1.0" encoding="utf-8" ?>';
 $xml .= '<inventory><ctid>'.$username.'</ctid>';
 $xml .= '</inventory>';

 $params = array('InventoryUpdateXML' => $xml);

 $response = _request($url, $params);
 if (!empty($response->errorid)) {
   watchdog('papes', (string)$response->errordata, array(), WATCHDOG_ERROR);
 }
 else {
   foreach ($response->item as $product) {
     // See if we have existing stock levels
     $sku = $product['prodid'];
     $qty = $product['quantity'];
     $nid = db_result(db_query("SELECT nid FROM {products} WHERE sku='%s'", $sku));
     if ($nid) {
       db_query("UPDATE {products} SET stock=%d WHERE sku='%s' AND nid=%d", $qty, $sku, $nid);
     }
     else {
       $nid = db_result(db_query("SELECT nid FROM {product_adjustments} WHERE model = '%s'", $sku));
       if ($nid) {
         db_query("INSERT INTO {products} (sku, nid, active, stock, threshold) VALUES ('%s', %d, %d, %d, %d)", $sku, $nid, 1, $qty, 0);
       }
     }
   }
 }
}

I'm wondering if anyone is able to help me out a bit or at least point me in the right direction as to where to look.

Thank you in advance for any help.

♥geoffreywalton · September 19, 2010

Did you look at http://addons.oscommerce.com/info/7335

HTH

G

Opt1kal · September 19, 2010

Did you look at http://addons.oscommerce.com/info/7335

HTH

G

Yeah I did, but the only thing with that is I need it to send an XML document to a url, and then take the response and enter in the attributes into the database.

♥mdtaylorlrim · September 19, 2010

Yeah I did, but the only thing with that is I need it to send an XML document to a url, and then take the response and enter in the attributes into the database.

If you are sending your XML to the server and receiving a valid response you can combine your efforts with the contribution. For example, as you receive the response write it to a temporary file, then use the code in the contribution to help you do the updates.

The contribution, however, has no provision for adding an item in the response that is not currently in stock, so you will have to deal with that.

Opt1kal · September 19, 2010

If you are sending your XML to the server and receiving a valid response you can combine your efforts with the contribution. For example, as you receive the response write it to a temporary file, then use the code in the contribution to help you do the updates.

The contribution, however, has no provision for adding an item in the response that is not currently in stock, so you will have to deal with that.

And thats another point I'm currently stuck at. I've been trying to figure out a way to send the xml to the server using curl and then to write the response to a file. But it seems that that solution is still eluding me. This is what I have to run as a cron for that:

<?
$path = "https://www.papesinc.com/pls/papes/ext"; //Relative path to the file with $_POST parsing
$ch = curl_init($path); //Initialise curl resource with above file
$data = "<?xml version='1.0' encoding='utf-8' ?>
	<inventory><ctid>'BD5DE1625F694EA4A892C860ADD2B1F5'</ctid>
	</inventory>"; 
//Data to be sent to the file

curl_setopt($ch, CURLOPT_POSTFIELDS, $data); //Send the data to the file?
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); 
curl_setopt($ch, CURLOPT_HEADER, 0);
$val = curl_exec($ch);   
curl_close($ch); //Close curl session
$fp = fopen('data.xml', 'w');
fwrite($fp, $val);
fclose($fp);
?>

♥mdtaylorlrim · September 19, 2010

Try this...

<?

$xml_data = "<?xml version='1.0' encoding='utf-8' ?>
               <inventory><ctid>'BD5DE1625F694EA4A892C860ADD2B1F5'</ctid>
               </inventory>"; 
//Data to be sent to the file

$URL = "https://www.papesinc.com/pls/papes/ext";

		$ch = curl_init($URL);
		curl_setopt($ch, CURLOPT_MUTE, 1);
		curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
		curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
		curl_setopt($ch, CURLOPT_POST, 1);
		curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: text/xml'));
		curl_setopt($ch, CURLOPT_POSTFIELDS, "$xml_data");
		curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
		$output = curl_exec($ch);
		curl_close($ch);

?>

Then see what the value of $output is.

Opt1kal · September 19, 2010

Try this...

<?

$xml_data = "<?xml version='1.0' encoding='utf-8' ?>
               <inventory><ctid>'BD5DE1625F694EA4A892C860ADD2B1F5'</ctid>
               </inventory>"; 
//Data to be sent to the file

$URL = "https://www.papesinc.com/pls/papes/ext";

		$ch = curl_init($URL);
		curl_setopt($ch, CURLOPT_MUTE, 1);
		curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
		curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
		curl_setopt($ch, CURLOPT_POST, 1);
		curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: text/xml'));
		curl_setopt($ch, CURLOPT_POSTFIELDS, "$xml_data");
		curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
		$output = curl_exec($ch);
		curl_close($ch);

?>

Then see what the value of $output is.

Well it's running as a cron, so then to see the value of $output would I add

echo "$output"

Or how would I have $output show up in the cron email?

Opt1kal · September 19, 2010

Try this...

<?

$xml_data = "<?xml version='1.0' encoding='utf-8' ?>
               <inventory><ctid>'BD5DE1625F694EA4A892C860ADD2B1F5'</ctid>
               </inventory>"; 
//Data to be sent to the file

$URL = "https://www.papesinc.com/pls/papes/ext";

		$ch = curl_init($URL);
		curl_setopt($ch, CURLOPT_MUTE, 1);
		curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
		curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
		curl_setopt($ch, CURLOPT_POST, 1);
		curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: text/xml'));
		curl_setopt($ch, CURLOPT_POSTFIELDS, "$xml_data");
		curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
		$output = curl_exec($ch);
		curl_close($ch);

?>

Then see what the value of $output is.

Sorry, went brain dead there for a bit earlier...here's the response that was saved in data.xml

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



























<error>

<timestamp>Sun Sep 19 15:25:57 EDT 2010</timestamp>

<errorid>99999</errorid>

<errordata>PROGRAM EXCEPTION: No XML data received from client</errordata>

</error>

♥mdtaylorlrim · September 19, 2010

Well that's progress. At least you know the server is getting *something* from you and you are saving the response back...

♥mdtaylorlrim · September 19, 2010

This may be nothing, but try transposing the single quotes and the double quotes in the xml format.

$xml_data = '<?xml version="1.0" encoding="utf-8" ?>
               <inventory>
               <ctid>BD5DE1625F694EA4A892C860ADD2B1F5</ctid>
               </inventory>';

And you shouldn't need the quotes around your account number

Opt1kal · September 19, 2010

Same response saved in the data.xml file.

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



























<error>

<timestamp>Sun Sep 19 17:10:57 EDT 2010</timestamp>

<errorid>99999</errorid>

<errordata>PROGRAM EXCEPTION: No XML data received from client</errordata>

</error>

I'm wondering if this I am taking the wrong approach to this...

mdtaylorlrim: If you wouldn't mind pm'ing me your email, I'll send you the pdf document that was sent to me from the distributor. Maybe you can get more from it then I have.

Opt1kal · September 20, 2010

Ok, so I've gotten ALOT of help from mdtaylorlrim, but I'm getting a couple warnings in my email response from the cron job. Here are the warnings:

<br />
<b>Warning</b>: XMLReader::read() [xmlreader.read]: /home/content/15/5050415/html/catalog/admin/inventory.tmp:1: parser error : Space required after the Public Identifier in <b>/home/content/15/5050415/html/catalog/admin/get_inventory.php</b> on line <b>157</b><br />
<br />
<b>Warning</b>: XMLReader::read() [xmlreader.read]: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN""http://www.w3.org/TR/html4/str in <b>/home/content/15/5050415/html/catalog/admin/get_inventory.php</b> on line <b>157</b><br />
<br />
<b>Warning</b>: XMLReader::read() [xmlreader.read]: ^ in <b>/home/content/15/5050415/html/catalog/admin/get_inventory.php</b> on line <b>157</b><br />
<br />
<b>Warning</b>: XMLReader::read() [xmlreader.read]: An Error Occured while reading in <b>/home/content/15/5050415/html/catalog/admin/get_inventory.php</b> on line <b>157</b><br />

Now here's line 157 of the get_inventory.php:

while ($xml->read()){

So I'm a little confused as to what needs to be done...and knowing my luck it's a simple fix that I should know....

Opt1kal · September 20, 2010

So I took at a look at products.xml and it contains this code and no idea how to fix it....


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN""http://www.w3.org/TR/html4/strict.dtd">

<HTML><HEAD><TITLE>Bad Request</TITLE>

<META HTTP-EQUIV="Content-Type" Content="text/html; charset=us-ascii"></HEAD>

<BODY><h2>Bad Request</h2>

<hr><p>HTTP Error 400. The request is badly formed.</p>

</BODY></HTML>

I'm using the http://addons.oscommerce.com/info/7335 contribution and I'm pretty sure everything was edited correctly. Here's that code:

<?php

// 
// 1.00 Original release
//


/* Name this file get_xml_inventory.php or whatever you want, just change it in the cron job below also.
  This program must reside in your admin directory! If you want it elsewhere you will have to hard code the paths below

  Use the following cron job to execute this. Edit the absolute path to your admin directory.

              php /absolute/path/to/shop/admin/get_xml_inventory.php

*/

// have to include the admin configure file
 require('includes/configure.php');

// set options

// Set mode of operation                                     // use for setting test mode or live mode.
$test_mode = 1;                                               // 1 = test mode,  0 = live mode


// file and directory names - must be absolute paths and not URLs
$working_dir = DIR_FS_ADMIN;                      // Uses admin directory from configure.php file
$local_file = DIR_FS_ADMIN . 'products.xml';      // Uses admin directoy / local and temp file name where you will store the file on your server
$temp_file = DIR_FS_ADMIN . 'inventory.tmp';      // It will run cleaner if you create these files manually beforehand.


// this is the method you will use to get your data feed
$get_method = 'http';                                         // valid values are 'ftp' or 'http' or 'off'   NOTE that http method uses curl and so will not work under xampp
                                                            // Off is useful for testing or when a supplier 'puts' a file on your server instead of you having to go out and get it

// these are required if you use ftp as your get method
$ftp_server = 'ftp://domain.com';                            // Change to fully qualified domain name
$ftp_user_name = 'your_user_name';                           // Change to your user name
$ftp_user_passs = 'password';                                // Change to your password
$remote_dir = '' ;                                           // Change to something other than entry directory
$remote_file = 'products.csv';                               // name of products file to get from vendor

// this is required is you use the http get method. It should be a valid URL that you get get with a browser
$url_to_file = 'https://www.papesinc.com/pls/papes/ext?data=<?xml version="1.0" encoding="utf-8" ?>
			<inventory><ctid>BD5DE1625F694EA4A892C860ADD2B1F5</ctid></inventory>';                            // If http is Get_method this must be a valid URL to the file 

// These are general options
$use_map = 0;                                                // set to 1 to enable map
$use_markup = 0;                                             //set to 1 to enable markup calculations



/* Set your common field
  For this to work your data feed must have a unique field (like products_id or products_model) that mataches one for one with a field in
  your database.in order to work properly. Some shops have
  installed custom fields. This is where you will define that field. */
define('PRODUCT_ID', 'products_id');                    // default is 'products_model' Change to whatever your common field is

// //////////////// There are three additional edits below. FIND THEM! /////////////////////////////



// Let's change to the working directory
chdir($working_dir);                                          // this does not work under xampp so just comment it out

// Start the download
switch ($get_method) {
case "off":
break;
case "ftp":

// open some file to write to.
$handle = fopen($local_file, 'w');

// set up basic connection
$conn_id = ftp_connect($ftp_server);

// login with username and password
$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);

// change directory if need to
if (isset($remote_dir)) {
ftp_chdir($conn_id, $remote_dir);
}

// try to download $remote_file and save it to $handle
if (ftp_fget($conn_id, $handle, $remote_file, FTP_ASCII, 0)) {
echo "successfully written to $local_file\n";                                // may have to comment this out after testing
} else {
echo "There was a problem while downloading $remote_file to $local_file\n";  // may have to comment this out after testing
}

// close the connection and the file handle
ftp_close($conn_id);
fclose($handle);

// end of php-ftp code
break;
case "http":
default:
// following uses curl as php code
$ch = curl_init($url_to_file);
$fp = fopen($local_file, "w");

curl_setopt($ch, CURLOPT_FILE, $fp);
curl_setopt($ch, CURLOPT_HEADER, 0);

curl_exec($ch);
curl_close($ch);
fclose($fp);
// end of php-curl code
}



// let's get the database open now
// include the database functions
 require('includes/functions/database.php');

// make a connection to the database... now
 tep_db_connect() or die('Unable to connect to database server!');

// open the xml file and build structure
$handle = @fopen($local_file,"r");
// open tmp file for writing
$tmp_handle = @fopen($temp_file,"w");

if ($handle) {
  while (!feof($handle)){
     $buffer = fgets($handle,4096);

// ************************** Possible edit needed here **************************************      
// sanitize garbage delivered by supplier into valid XML file, if necessary. Populate and uncomment as necessary
   $buffer = str_replace('<','<',$buffer);
   $buffer = str_replace('>','>',$buffer);
//    $buffer = str_replace('','',$buffer);
//    $buffer = str_replace('','',$buffer);
//    $buffer = str_replace('','',$buffer);
//    $buffer = str_replace('','',$buffer);
// *******************************************************************************
   if($buffer) {
     fwrite($tmp_handle,$buffer);
     }
   }
 }
// complete repairing xml file

// initialize some variables
   $name = '';
   $value = '';

// create new reader and open file
   $xml = new XMLReader();
   $xml->open($temp_file);

// read each line of the file
while ($xml->read()){
// echo $xml->nodeType . "<BR>\n";   // debug

// assign element node name to $name
  if ($xml->nodeType == XMLreader::ELEMENT) {
     $name = $xml->name;
   }

// assign element value to $value    
   if (in_array($xml->nodeType, array(XMLReader::TEXT, XMLReader::CDATA, XMLReader::WHITESPACE, XMLReader::SIGNIFICANT_WHITESPACE)) && $name!=''){
       $value= $xml->value;
   }

// ************************************* This block needs to be edited to match your xml file. ONLY THE NAMES IN CAPS NEED TO BE CHANGED *************
// assign each value to specific variables
    if ($name == "prodid") $itemno = trim($value);     // this may match your products_id or it may match your products_model or a custome field.
    if ($name == "price")   $cost = trim($value);
    if ($name == "quantity")  $qty = trim($value);
//     if ($name == "MFGINO") $model = trim($value);   // this may or may not be necessary. The others are necessary.
// ****************************************************************************************************************************************************


// ends getting data and populating local variables

//reset name/value values
 if ($xml->nodeType == XMLReader::END_ELEMENT){
   $name = '';
   $value = '';
 }

// **************************** This (name == 'Table') needs to be changed to the nametag that surrounds each produt. ************************
// only continue if we reach the product end tag
   if ($xml->nodeType == XMLReader::END_ELEMENT && $xml->name == '/>'){


if ($use_markup == true) {       // requires a markup column in the categories table with markeup amount as decimal ( .20 as 20%)
// get and set markup
$sql = "select categories.markup from categories,products_to_categories,products where products.products_id = '" . $itemno . "' and products.products_id = products_to_categories.products_id and products_to_categories.categories_id = categories.categories_id";
$result = mysql_query($sql);
$markup = mysql_fetch_row($result);
$price = (int)($cost * (1+ $markup))+.99;    // go ahead and change the .99 to whatever you want
$cost = $price;
}

// are we going to use MAP pricing?       Requires a products_map column in the products table, with a value for map, NULL if no MAP
if ($use_map == true) {
$sql = "select products_map from products where products_id = '" . $itemno . "'";
$result = mysql_query($sql);
$map = mysql_fetch_row($result);
if (!(is_null($map[0]))) {
  $cost = $map[0];
  }
}


// OK, let's build and execute the sql statement for this item    
$sql = "update products set products_quantity = '" . $qty . "', products_price = '" . $cost . "' where " . PRODUCTS_ID . "= '" . $itemno . "'";



if ($test_mode == true) {
  echo "<table border=1>";
  echo " <tr>";
  echo "  <td>" . $itemno . "</td><td>" . $cost . "</td><td>" . $qty . "</td>";
  echo " </tr>";
  echo "</table>"; 
 } else {
  $result = mysql_query($sql);      // this executes the sql
 }
} 
}                                   // back up to process next item

// and finally, close the db and we are done.
tep_db_close();

?>

If anyone could help out a bit it would be amazing.

♥mdtaylorlrim · September 20, 2010

Ok, the reason it is not working is that the contribution is written to get from http and not from https. Give me a little while to alter the code to allow from https. Never done it before so it will take a while, with all the other stuff I have to do today as well.

♥mdtaylorlrim · September 20, 2010

And also because the data is in attributes instead of elements. Never seen anyone do that.

Check your email.

pconic · October 17, 2010

I am trying to do the same thing I will be priciest if you can send me the code too ami@pconic.com

pconic · October 22, 2010

I try to import all the item from ingram micro to oscommerce using Xml mustang (i have user name and pass)and FTp.

how can import the pictures to my product. i will be appreciated if you can help in this regard.

my email address is mram@pconic.com

Thank you

ntd · January 10, 2012

Has anyone figured out the solution for this. I am trying to do the same thing with the same supplier.

Please let me know.

Importing xml into mysql inventory

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

Archived