Solved

PHP import script ignoring xml tags containing html

Posted on 2014-12-01
26
160 Views
Last Modified: 2016-03-02
Hi everyone,

In order to import products into our website that was built using Magento, we export an xml file from our point of sale program and this is uploaded to our web server. A php script is then run and a csv file is created. We then use the Magmi php plugin to import the products. The problem I'm having is that if any html is included in the xml file then that xml tag is not imported into Magento. The following is an example of the code in the xml file containing some html:

<LongDescription><![CDATA[ Some description goes here.<br /> <br /> <span style="color: #bc1a31;">SPECIAL PROMOTION: Some promotional description goes here.</span> ]]></LongDescription>

When the import script is run, then the above tag gets ignored. You'll notice in the above snippet that the following tags have been inserted into the xml tag:

<![CDATA[

 ]]>

These tags were automatically inserted into the xml file when it was exported from our Point of Sale program (they weren't intentionally added) so I'm wondering if anyone could tell me whether it is in fact those tags that are preventing the php import script from accepting the xml tag and importing the information into Magento?

I'd really appreciate any advice.
0
Comment
Question by:gwh2
  • 13
  • 13
26 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
Yes, it is 'those tags'.  There are 5 characters that can not be used directly in XML because they have special meanings in XML:  &, ", ', <, >  
This page shows how to 'escape' them: http://support.esri.com/cn/knowledgebase/techarticles/detail/23973

In PHP, htmlentities() can do the conversion for you and can be limited to XML1 requirements.  
http://php.net/manual/en/function.htmlentities.php  The decoding function and related functions are listed on that page.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
Although... this page says what you have should be working: http://www.w3schools.com/xml/xml_cdata.asp
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
Looking at the http://wiki.magmi.org/index.php?title=Import_new_products page, it shows standard CSV format... but your text above has double quotes in it and that will break normal CSV parsing.
0
 
LVL 1

Author Comment

by:gwh2
Comment Utility
Thanks for the reply,

So from what you're saying it seems that the only thing breaking the code is the double quotes in the inline css - is that right? If yes, if I escaped the doubled quotes as follows would that work?


<LongDescription><![CDATA[ Some description goes here.<br /> <br /> <span style=\"color: #bc1a31;\">SPECIAL PROMOTION: Some promotional description goes here.</span> ]]></LongDescription>
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
Probably not.  CSV parsers are usually pretty simple minded.  But you can change them to single quotes instead of double quotes.  HTML doesn't care.  Then the question is whether the Magento database import is written properly.  It should be doing a 'mysql_real_escape' of some version which would take care of the quotes.
0
 
LVL 1

Author Comment

by:gwh2
Comment Utility
The Magento database import script probably needs tweaking to take care of the quotes but I need a workaround in the meantime so I'll just try the single quotes and will come back to accept the solution. It may not be until tomorrow morning though as the import script runs overnight.

Thanks so much for your advice. I really appreciate it.
0
 
LVL 1

Author Comment

by:gwh2
Comment Utility
Hi, I tested this out by only including the single quotes in the inline css however it's still not being processed. Do you think that it could be the import script that needs tweaking to allow html in the .csv file?
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
Can you post a copy of the CSV file that is supposed to be imported that has the problem?  Just a small sample.
0
 
LVL 1

Author Comment

by:gwh2
Comment Utility
Hi, I have the php script that contains the code to create the csv file but I can't find the actual csv file. I think that maybe after it's been created it gets deleted until the next time the import script is run. I can run the script if you need the csv file but would it help if I posted the php script itself?
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
No, I think we need to see some data that doesn't work.  The script may work fine with correct data.
0
 
LVL 1

Author Comment

by:gwh2
Comment Utility
Hi, I've found the csv file and I've included a small portion of it in an excel file. Could you take a look to see what might be causing the issue?

Thanks for your support.
0
 
LVL 1

Author Comment

by:gwh2
Comment Utility
Sorry I've now uploaded the file …
products.xlsx
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
What you uploaded appears to be ok... but Excel will filter the import.  CSV is a plain text format and that's what I need to see.  The plain text that is generated and fed to the database.  And I specifically need to see lines that are Not currently importing properly.
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.

 
LVL 1

Author Comment

by:gwh2
Comment Utility
Ok I've uploaded a csv file. It contains 1 product in 3 colours. The first 2 colours are outputting to the csv file because there is no html but the third colour, ie. khaki didn't output the description as I added html.

Thanks again for your help.
0
 
LVL 1

Author Comment

by:gwh2
Comment Utility
See attached for the csv file.
products2.csv
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
Ok, you've identified the stage at which it fails, the CSV export.  Now is the time to post the PHP script that does that to see what's going on that causes it to fail.  It would be helpful if you could also post the XML that created the CSV above.
0
 
LVL 1

Author Comment

by:gwh2
Comment Utility
Ok great. Below is the code from the php script. Note that I've removed the values from inside the quote marks in the first 4 lines of code for privacy.

Thanks again

<?php
$con = mysql_connect("123.0.0.0”, "", "");
mysql_select_db("");
	
	$project_location = "/home/site.com/html";
	$csv_file = $project_location."/var/import/products.csv";
	$fp = fopen($csv_file,"w");

/******************************************************************************
 * Generate CSV from products table to magento import format
 ******************************************************************************/	
	
	if(!$fp){
		echo "Can not create file ";
		exit;
	} else {
		echo "File created";
	}
	$head = array("store","websites","attribute_set","type","categories","sku","name","supplier_name","style_code","spec_chart","alt_code","status","visibility","enable_googlecheckout","tax_class_id","colour","size","style","gender","price","special_price","description","composition","special_from_date","qty","manage_stock","product_name","store_id","colour_groups","configurable_attributes","simples_skus","swatch_path","is_in_stock","image","small_image","thumbnail","re_skus","media_gallery","us_skus","colour_clone");	
	fputcsv($fp, $head);
	
	//simple products
	$sql = "SELECT * FROM products p left join products_sizes ps on ps.Sku = p.Sku WHERE p.status = 0";
	$result = mysql_query($sql);

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $sql;
    die($message);
}
	
	while($rs = mysql_fetch_assoc($result) ){

		$price = $rs['Price'];
		$specialprice = $rs['SpecialPrice'];
		if( intval($rs['Price']) <= 0 ){
			$price = $rs['SpecialPrice'];
			$specialprice = '';
			$rs['SpecialPriceStart'] = '';
			$rs['SpecialPriceEnd'] = '';
		} 

		if( $specialprice <= 0 ){
			$specialprice = '';
			$rs['SpecialPriceStart'] = '';
			$rs['SpecialPriceEnd'] = '';
		}
		
		$ColourGroup = ucwords(strtolower(str_replace("/","/ ",$rs['ColourGroup'])));
		$rs['ColourGroup'] = str_replace('/ ','/',$ColourGroup);
		
		$swatch_image = " ";
		
		if($rs['SizeName']!='N/A')
			$SizeName="-".$rs['SizeName'];
		elseif($rs['SizeName']=='N/A')
			$rs['SizeName']='';
		
		$name = $rs['ProductTitle'].'-'.ucwords(strtolower($rs['ColourName'])).$SizeName;
		$store = 0;
		
		$rs['ColourName'] = ucwords(strtolower($rs['ColourName']));
		
		if($rs['WebDepartment']=='Bow Ties' || $rs['WebDepartment']=='Formal Ties' || $rs['WebDepartment']=='Vests' || $rs['WebDepartment']=='Belts' || $rs['WebDepartment']=='Handkerchiefs' || $rs['WebDepartment']=="Big Men's Vests"){
			$rs['QtyOnHand']=10000;
		}
		
		$swatch_image = " ";
		if( $rs['SwatchImage'] != '' ){
			$swatch_image = $project_location. "/new_media/".$rs['SupplierName'].'/swatches/'.$rs['SwatchImage'];
		}
		
		$product_image = " ";
		if( $rs['ProductImage'] != '' ){
			$product_image = $project_location."/new_media".$rs['ProductImage'];
		} 
		
		$alternate_image = " ";
		if( $rs['AlternativeImage'] != "" ){
			$al_prs = explode(";", $rs['AlternativeImage']);
			$alternate_image = $product_image.";";
			for($al_pr=0; $al_pr < count($al_prs); $al_pr++){
				if (strpos(trim($al_prs[$al_pr]),'.jpg') !== false) {
					$alternate_image .= $project_location."/new_media/".$rs['SupplierName'].'/'.str_replace(" ", "-", trim($al_prs[$al_pr])).";";
				}
				else
				{
					$alternate_image .= $project_location."/new_media/".$rs['SupplierName'].'/'.str_replace(" ", "-", trim($al_prs[$al_pr])).".jpg;";
				}
			}
			$alternate_image = substr($alternate_image, 0, -1);
			
		}
		if( $rs['AlternativeImage2'] != "" ){
			
			$al_prs2 = explode(";", $rs['AlternativeImage2']);
			
			$alternate_image = $alternate_image.";";
			
			for($al_pr2=0; $al_pr2 < count($al_prs2); $al_pr2++){
				if (strpos(trim($al_prs2[$al_pr2]),'.jpg') !== false) {
					$alternate_image .= $project_location."/new_media/".$rs['SupplierName'].'/'.str_replace(" ", "-", trim($al_prs2[$al_pr2])).";";
				}
				else
				{
					$alternate_image .= $project_location."/new_media/".$rs['SupplierName'].'/'.str_replace(" ", "-", trim($al_prs2[$al_pr2])).".jpg;";
				}
					
			}
			
			$alternate_image = substr($alternate_image, 0, -1);
		}
		

		$instock = ($rs['QtyOnHand'] < 3)?0:1;
		
		if($rs['Code']=='')
			$rs['Code']=$rs['Sku'];
		
		$store = 0;
			$ar = array('admin','base','Default','simple', '',$rs['Code'],$name,$rs['SupplierName'],'',$rs['SpecChart'],
			'','1','1','1','New Tax Rule',$rs['ColourName'],$rs['SizeName'],
			'', '',$price,$specialprice,$rs['LongDescription'],$rs['Composition'],$rs['SpecialPriceStart'],
			$rs['QtyOnHand'],0,$name,$store, $rs['ColourGroup'],'','',$swatch_image,$instock,$product_image,$product_image,$product_image,'',$alternate_image,'','');			
		
		
		fputcsv($fp, $ar);
	}
	
	//Configurable products
	$sql = "SELECT p.* FROM products p group by p.Style having p.status = 0";
	$results = mysql_query($sql) or die(mysql_error());
	$rs = array(); 
	$main_sku = '';
	while($rs = mysql_fetch_assoc($results) ){
		$sql_simple = "SELECT group_concat(BarCode) as simpleskus, group_concat(SizeName) as colorsizes FROM `products_sizes`where Style=".$rs['Style'];
		$res_simple = mysql_query($sql_simple);
		$simple_sku='';
		if(mysql_num_rows($res_simple)>0){
			$res_arr = mysql_fetch_array($res_simple);
			$simple_sku = $res_arr['simpleskus'];

			if(str_replace('N/A,','',$res_arr['colorsizes'])!='N/A')
				$config_attr="colour,size";
			else 
				$config_attr="colour";
		}
		
		$sql_colors = "SELECT group_concat(ColourGroup) as colornames FROM `products`where Style=".$rs['Style'];
		$res_colors = mysql_query($sql_colors);
		$color_names='';
		if(mysql_num_rows($res_colors)>0){
			$res_arr = mysql_fetch_array($res_colors);
			
			$color_names = ucwords(strtolower(str_replace(",",", ",$res_arr['colornames'])));
			$color_names = ucwords(strtolower(str_replace("/","/ ",$color_names)));
			$color_names = str_replace('/ ','/',$color_names);
			$color_names = str_replace(', ',',',$color_names);
			
			//$color_names = $res_arr['colornames'];
		}
			
			$price = $rs['Price'];
			$specialprice = $rs['SpecialPrice'];

			if( intval($rs['Price']) <= 0 ){
				$price = $rs['SpecialPrice'];
				$specialprice = '';
				$rs['SpecialPriceStart'] = '';
				$rs['SpecialPriceEnd'] = '';
			} 
			$ColourGroup = ucwords(strtolower(str_replace("/","/ ",$rs['ColourGroup'])));
			$rs['ColourGroup'] = str_replace('/ ','/',$ColourGroup);
		
			if( $specialprice <= 0 ){
				$specialprice = '';
				$rs['SpecialPriceStart'] = '';
				$rs['SpecialPriceEnd'] = '';
			}

			$swatch_image = " ";
			if( $rs['SwatchImage'] != '' ){
				$swatch_image = $project_location. "/new_media/".$rs['SupplierName'].'/swatches/'.$rs['SwatchImage'];
			} 
			$product_image = " ";
			if( $rs['ProductImage'] != '' ){
				$product_image = $project_location."/new_media".$rs['ProductImage'];
			} 
			$rel_items = " ";
			if( trim($rs['SuggestedItems']) != ''){
				$rel_items = str_replace(';',',',substr($rs['SuggestedItems'],0,-1));
				
			} 
			$alternate_image = " ";
			if( trim($rs['AlternativeImage']) != "" ){
				$al_prs = explode(";", $rs['AlternativeImage']);
				$alternate_image = "";
				for($al_pr=0; $al_pr < count($al_prs); $al_pr++){
					if (strpos(trim($al_prs[$al_pr]),'.jpg') !== false) {
						$alternate_image .= $project_location."/new_media/".$rs['SupplierName'].'/'.str_replace(" ", "-", trim($al_prs[$al_pr])).";";
					}
					else
					{
						$alternate_image .= $project_location."/new_media/".$rs['SupplierName'].'/'.str_replace(" ", "-", trim($al_prs[$al_pr])).".jpg;";
					}
				}
				$alternate_image = substr($alternate_image, 0, -1);
			}
			if( trim($rs['AlternativeImage2']) != "" ){
			
			$al_prs2 = explode(";", $rs['AlternativeImage2']);
			
			$alternate_image = $alternate_image.";";
			
			for($al_pr2=0; $al_pr2 < count($al_prs2); $al_pr2++){
				if (strpos(trim($al_prs2[$al_pr2]),'.jpg') !== false) {
					$alternate_image .= $project_location."/new_media/".$rs['SupplierName'].'/'.str_replace(" ", "-", trim($al_prs2[$al_pr2])).";";
				}
				else
				{
					$alternate_image .= $project_location."/new_media/".$rs['SupplierName'].'/'.str_replace(" ", "-", trim($al_prs2[$al_pr2])).".jpg;";
				}
			}
			
			$alternate_image = substr($alternate_image, 0, -1);
		}

			$category_id = '';
			$name = $rs['ProductTitle'];
			$store = 0;
			$gender="";
			if($rs['Ranges'] == "corp" || $rs['Ranges'] == 'Healthcare' || $rs['Ranges'] == 'Beauty' || $rs['Ranges'] == 'Hospitality' || $rs['Ranges'] == 'Workwear'){
				$category_id = 'corp Brochure#'.ucwords(strtolower($rs['Category'])).'#'.ucwords(strtolower($rs['WebDepartment']));
				$gender = str_replace('corp ','',$rs['GroupName']);
			}elseif($rs['Ranges'] == "Online"){
				$category_id = 'Online Store#'.ucwords(strtolower($rs['Category'])).'#'.ucwords(strtolower($rs['WebDepartment']));
				$gender = '';
			}else{
				$ranges = explode('; ',$rs['Ranges']);
				foreach($ranges as $range){
					if( $range == "Online" ){
						$category_id .= 'Online Store#'.ucwords(strtolower($rs['Category'])).'#'.ucwords(strtolower($rs['WebDepartment'])).';;';
					}else if( $range == "corp" ){
						$category_id .= 'corp Brochure#corp#'.ucwords(strtolower($rs['WebDepartment'])).';;';
					}else if( $range == “Range1” || $range == "Range2” || $range == "Range3” || $range  == "Range4”){
						$category_id .= 'corp Brochure#'.ucwords(strtolower($range)).'#'.ucwords(strtolower($rs['WebDepartment'])).';;';
					}
				}
				$category_id = substr($category_id, 0, -2);
				#$category_id = 'Online Store#'.ucwords(strtolower($rs['Category'])).'#'.ucwords(strtolower($rs['WebDepartment'])).';;corp Brochure#corp#'.ucwords(strtolower($rs['WebDepartment']));
				$gender = str_replace('corp ','',$rs['MajorName']);
			}
			$upsell_rs = "-re::1";	
			if( trim($rs['upsells']) != "" ){
				$upsell_rs = "-re::1,".$rs['upsells'];
			}				

			$ar = array('admin','base','Default','configurable',$category_id,$rs['Style'],$name,$rs['SupplierName'],$rs['Style'],
					$rs['SpecChart'],
				$rs['AltCode'],'1','4','1','New Tax Rule','','',
				'', $gender,$price,$specialprice,$rs['LongDescription'],$rs['Composition'],$rs['SpecialPriceStart'],
				0,1,$name,0, $rs['ColourGroup'],$config_attr,$simple_sku,$swatch_image,1,$product_image,$product_image,
			$product_image,$rel_items,$alternate_image,$upsell_rs,$color_names);
			fputcsv($fp, $ar);
	}	
	fclose($fp);
?>

Open in new window

0
 
LVL 1

Author Comment

by:gwh2
Comment Utility
The xml tag that holds the description is called 'LongDescription' and it can be found in the following lines of code:

			'', '',$price,$specialprice,$rs['LongDescription'],$rs['Composition'],$rs['SpecialPriceStart'],

Open in new window


and

				'', $gender,$price,$specialprice,$rs['LongDescription'],$rs['Composition'],$rs['SpecialPriceStart'],

Open in new window

0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
I don't see an XML file or a PHP function to convert the XML in any of that.  ??
0
 
LVL 1

Author Comment

by:gwh2
Comment Utility
The code below is a sample of the xml file relating to the 3 products that were in the csv file. Is that ok?

<?xml version="1.0" standalone="yes"?>
<ItemsDataSet>
<TemplateItem>
<Style>5721</Style>
<Sku>5721002</Sku>
<AltCode />
<Name>MENS COTTON CHINOS BLACK</Name>
<LongDescription>A flat front trouser with two slant pockets and a fob pocket at the front and two button-through hip pockets at the rear. </LongDescription>
<SupplierCode>10</SupplierCode>
<SupplierName>NEW SUPPLIER</SupplierName>
<Major>1</Major>
<MajorName>MENS</MajorName>
<GroupCode>5</GroupCode>
<GroupName>TROUSERS</GroupName>
<ClassCode>502</ClassCode>
<ClassName>PURE COTTON TROUSERS</ClassName>
<WebDepartment>Trousers</WebDepartment>
<BrandCode>999</BrandCode>
<BrandName>OPEN BRAND</BrandName>
<SeasonCode>999</SeasonCode>
<SeasonName>OPEN SEASON</SeasonName>
<FirstSaleDate>27/06/2012</FirstSaleDate>
<LastChangeDate>02/12/2014</LastChangeDate>
<Catalog>1</Catalog>
<HotList />
<ColourCode>002</ColourCode>
<ColourName>BLACK</ColourName>
<ColourGroup>BLACK/CHARCOAL</ColourGroup>
<Price>   89.00</Price>
<SpecialPrice>   69.00</SpecialPrice>
<SpecialPriceStart>26/11/2014</SpecialPriceStart>
<SpecialPriceEnd>23/11/2024</SpecialPriceEnd>
<MultiSellPrice />
<OtherSellPrice />
<SuggestedItems>1078;9795;1126;1164;1158;</SuggestedItems>
<AlternativeItems>1126;</AlternativeItems>
<AddOnItems>1126;1078;9795;2237;1720;2282;1171;1164;5116;4744;</AddOnItems>
<RecommendedItems/>
<ProductTitle> Men's Cotton Chinos</ProductTitle>
<ProductImage>5721002_Black.jpg</ProductImage>
<Category>Men</Category>
<AlternativeImage2 />
<Composition>100% Cotton</Composition>
<Ranges>Online</Ranges>
<CorporateRelated />
<AlternativeImage>5721002_Black_Side;5721002_Black_Back</AlternativeImage>
<SwatchImage />
<SpecChart>31</SpecChart>
<Item>
<Code>572100202</Code>
<BarCode>572100202</BarCode>
<SizeCode>2</SizeCode>
<SizeName>30</SizeName>
<QtyOnHand>1</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100204</Code>
<BarCode>572100204</BarCode>
<SizeCode>4</SizeCode>
<SizeName>32</SizeName>
<QtyOnHand>3</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100206</Code>
<BarCode>572100206</BarCode>
<SizeCode>6</SizeCode>
<SizeName>34</SizeName>
<QtyOnHand>3</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100207</Code>
<BarCode>572100207</BarCode>
<SizeCode>7</SizeCode>
<SizeName>36</SizeName>
<QtyOnHand>3</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100208</Code>
<BarCode>572100208</BarCode>
<SizeCode>8</SizeCode>
<SizeName>38</SizeName>
<QtyOnHand>2</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100209</Code>
<BarCode>572100209</BarCode>
<SizeCode>9</SizeCode>
<SizeName>40</SizeName>
<QtyOnHand>1</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100210</Code>
<BarCode>572100210</BarCode>
<SizeCode>10</SizeCode>
<SizeName>42</SizeName>
<QtyOnHand>1</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100211</Code>
<BarCode>572100211</BarCode>
<SizeCode>11</SizeCode>
<SizeName>44</SizeName>
<QtyOnHand>1</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100212</Code>
<BarCode>572100212</BarCode>
<SizeCode>12</SizeCode>
<SizeName>46</SizeName>
<QtyOnHand>2</QtyOnHand>
<QtyOnOrder/>
</Item>
</TemplateItem>
<TemplateItem>
<Style>5721</Style>
<Sku>5721003</Sku>
<AltCode />
<Name>MENS COTTON CHINOS NAVY</Name>
<LongDescription>A flat front trouser with two slant pockets and a fob pocket at the front and two button-through hip pockets at the rear. </LongDescription>
<SupplierCode>10</SupplierCode>
<SupplierName>NEW SUPPLIER</SupplierName>
<Major>1</Major>
<MajorName>MENS</MajorName>
<GroupCode>5</GroupCode>
<GroupName>TROUSERS</GroupName>
<ClassCode>502</ClassCode>
<ClassName>PURE COTTON TROUSERS</ClassName>
<WebDepartment>Trousers</WebDepartment>
<BrandCode>999</BrandCode>
<BrandName>OPEN BRAND</BrandName>
<SeasonCode>999</SeasonCode>
<SeasonName>OPEN SEASON</SeasonName>
<FirstSaleDate>07/05/2012</FirstSaleDate>
<LastChangeDate>02/12/2014</LastChangeDate>
<Catalog>1</Catalog>
<HotList />
<ColourCode>003</ColourCode>
<ColourName>NAVY</ColourName>
<ColourGroup>BLUE</ColourGroup>
<Price>   89.00</Price>
<SpecialPrice>   69.00</SpecialPrice>
<SpecialPriceStart>26/11/2014</SpecialPriceStart>
<SpecialPriceEnd>23/11/2024</SpecialPriceEnd>
<MultiSellPrice />
<OtherSellPrice />
<SuggestedItems>1078;9795;1126;1164;1158;</SuggestedItems>
<AlternativeItems>1126;</AlternativeItems>
<AddOnItems>1126;1078;9795;2237;1720;2282;1171;1164;5116;4744;</AddOnItems>
<RecommendedItems/>
<ProductTitle> Men's Cotton Chinos</ProductTitle>
<ProductImage>5721003_Navy.jpg</ProductImage>
<Category>Men</Category>
<AlternativeImage2 />
<Composition>100% Cotton</Composition>
<Ranges>Online</Ranges>
<CorporateRelated />
<AlternativeImage>5721003_Navy_Side;5721003_Navy_Back;5721003_Navy_GM</AlternativeImage>
<SwatchImage />
<SpecChart>31</SpecChart>
<Item>
<Code>572100302</Code>
<BarCode>572100302</BarCode>
<SizeCode>2</SizeCode>
<SizeName>30</SizeName>
<QtyOnHand>1</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100304</Code>
<BarCode>572100304</BarCode>
<SizeCode>4</SizeCode>
<SizeName>32</SizeName>
<QtyOnHand/>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100306</Code>
<BarCode>572100306</BarCode>
<SizeCode>6</SizeCode>
<SizeName>34</SizeName>
<QtyOnHand>2</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100307</Code>
<BarCode>572100307</BarCode>
<SizeCode>7</SizeCode>
<SizeName>36</SizeName>
<QtyOnHand/>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100309</Code>
<BarCode>572100309</BarCode>
<SizeCode>9</SizeCode>
<SizeName>40</SizeName>
<QtyOnHand>1</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572100312</Code>
<BarCode>572100312</BarCode>
<SizeCode>12</SizeCode>
<SizeName>46</SizeName>
<QtyOnHand>2</QtyOnHand>
<QtyOnOrder/>
</Item>
</TemplateItem>
<TemplateItem>
<Style>5721</Style>
<Sku>5721064</Sku>
<AltCode />
<Name>MENS COTTON CHINOS KHAKI</Name>
<LongDescription><![CDATA[<p>A flat front trouser with two slant pockets and a fob pocket at the front and two button-through hip pockets at the rear.</p> <p><strong>SPECIAL PROMOTION:</strong> Enter Promo Code 12345 at checkout.</p> ]]></LongDescription>
<SupplierCode>10</SupplierCode>
<SupplierName>NEW SUPPLIER</SupplierName>
<Major>1</Major>
<MajorName>MENS</MajorName>
<GroupCode>5</GroupCode>
<GroupName>TROUSERS</GroupName>
<ClassCode>502</ClassCode>
<ClassName>PURE COTTON TROUSERS</ClassName>
<WebDepartment>Trousers</WebDepartment>
<BrandCode>999</BrandCode>
<BrandName>OPEN BRAND</BrandName>
<SeasonCode>999</SeasonCode>
<SeasonName>OPEN SEASON</SeasonName>
<FirstSaleDate>07/05/2012</FirstSaleDate>
<LastChangeDate>02/12/2014</LastChangeDate>
<Catalog>1</Catalog>
<HotList />
<ColourCode>064</ColourCode>
<ColourName>KHAKI</ColourName>
<ColourGroup>GREEN</ColourGroup>
<Price>   89.00</Price>
<SpecialPrice>   69.00</SpecialPrice>
<SpecialPriceStart>26/11/2014</SpecialPriceStart>
<SpecialPriceEnd>23/11/2024</SpecialPriceEnd>
<MultiSellPrice />
<OtherSellPrice />
<SuggestedItems>1078;9795;1126;1164;1158;</SuggestedItems>
<AlternativeItems>1126;</AlternativeItems>
<AddOnItems>1126;1078;9795;2237;1720;2282;1171;1164;5116;4744;</AddOnItems>
<RecommendedItems/>
<ProductTitle> Men's Cotton Chinos</ProductTitle>
<ProductImage>5721064_Khaki.jpg</ProductImage>
<Category>Men</Category>
<AlternativeImage2 />
<Composition>100% Cotton</Composition>
<Ranges>Online</Ranges>
<CorporateRelated />
<AlternativeImage>5721064_Khaki_Side;5721064_Khaki_Back;5721064_Khaki_GM</AlternativeImage>
<SwatchImage />
<SpecChart>31</SpecChart>
<Item>
<Code>572106404</Code>
<BarCode>572106404</BarCode>
<SizeCode>4</SizeCode>
<SizeName>32</SizeName>
<QtyOnHand>3</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572106406</Code>
<BarCode>572106406</BarCode>
<SizeCode>6</SizeCode>
<SizeName>34</SizeName>
<QtyOnHand>3</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572106407</Code>
<BarCode>572106407</BarCode>
<SizeCode>7</SizeCode>
<SizeName>36</SizeName>
<QtyOnHand>1</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572106408</Code>
<BarCode>572106408</BarCode>
<SizeCode>8</SizeCode>
<SizeName>38</SizeName>
<QtyOnHand>1</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572106409</Code>
<BarCode>572106409</BarCode>
<SizeCode>9</SizeCode>
<SizeName>40</SizeName>
<QtyOnHand>6</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572106410</Code>
<BarCode>572106410</BarCode>
<SizeCode>10</SizeCode>
<SizeName>42</SizeName>
<QtyOnHand>1</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572106411</Code>
<BarCode>572106411</BarCode>
<SizeCode>11</SizeCode>
<SizeName>44</SizeName>
<QtyOnHand>2</QtyOnHand>
<QtyOnOrder/>
</Item>
<Item>
<Code>572106412</Code>
<BarCode>572106412</BarCode>
<SizeCode>12</SizeCode>
<SizeName>46</SizeName>
<QtyOnHand>1</QtyOnHand>
<QtyOnOrder/>
</Item>
</TemplateItem>
</ItemsDataSet>

Open in new window

0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
That's what I was looking for.  I opened it in Microsoft's XML Notepad and it was fine.  No errors.  When I saved it, it had the same exact content except that it was indented by sections.

I'm going to have get some sleep but the next I want to know is which program converts this into CSV.  I'll check back tomorrow but I'm done for tonight.
0
 
LVL 1

Author Comment

by:gwh2
Comment Utility
Hi, I think the following script imports the products from the xml:

<?php
ini_set("display_errors", 1);
$con = mysql_connect("", "", "");
mysql_select_db("");
$location = "/home/site.com/html";
$project_location = $location."/media";

function XML2Array($XmlFilePath) {
	if (!file_exists($XmlFilePath))
		return FALSE;
	$Class = Array ();
	$Class['UsersFileXml'] = file_get_contents($XmlFilePath);
	$Class['FileXml'] = simplexml_load_string($Class['UsersFileXml']);
	$Class['FileJson'] = json_encode($Class['FileXml']);
	$Array = json_decode($Class['FileJson'], TRUE);
	unset ($Class);
	return $Array;
}

function dateformat($dateval) {
	list ($day, $month, $year) = explode("/", $dateval);
	return $year . '-' . $month . '-' . $day;
}

/***********************************************************
 * Move data into backup tables 
 * *********************************************************/
mysql_query("truncate table `products_old`");
mysql_query("truncate table `products_sizes_old`");
mysql_query("insert into products_old SELECT * FROM `products`");
mysql_query("insert into products_sizes_old SELECT * FROM `products_sizes`");

mysql_query("truncate table `products`");
mysql_query("truncate table `products_sizes`");
/************** Move data into backup tables *********************/

$array = XML2Array($location.'/folder/products.xml');
foreach ($array['TemplateItem'] as $product) {
	$index_key = '';
	$val_str = '';
	$sku = $style = $ProductImage = $SupplierName = '';
	foreach ($product as $key => $value) {
		if (!is_array($value)) {
			if ($key == 'Sku') {
				$sku = $value;
			}
			if ($key == 'Style') {
				$style = $value;
			}
			if($key == 'ProductImage')
				$ProductImage = $value;
				
			if($key == 'SupplierName')
				$SupplierName = $value;

			switch ($key) {
				case 'FirstSaleDate' :
				case 'LastChangeDate' :
				case 'SpecialPriceEnd' :
				case 'SpecialPriceEnd' :
				case 'SpecialPriceStart' :
					$value = dateformat($value);
					break;
				case 'AltCode': 
					$v = $value[0];
					if( strtolower($v) == 's' ){
						$value = substr($value, 1);
					} 	
				break;	
					
			}

			$index_key = $index_key . $key . ', ';
			if(trim($value)!='')
				$val_str = $val_str . "'" . mysql_real_escape_string(trim($value)) . "', ";
			else
				$val_str = $val_str . "'', ";

		} else {
			if ($key == 'Item') {
				if($value['Code']!=''){
					if(is_array($value['QtyOnHand'])){
						$value['QtyOnHand']=0;
					}
					if(is_array($value['SizeName']))
						$value['SizeName']="N/A";
						
					$item_index_key = 'Style, Sku, Code, BarCode, SizeCode, SizeName, QtyOnHand';
					$item_val_str = $style. "," . $sku . ",".$value['Code'].",".$value['BarCode'].",".$value['SizeCode'].",'".$value['SizeName']."',".$value['QtyOnHand'];
					//echo"<br />"."INSERT INTO products_sizes (" . $item_index_key . ") VALUES (" . $item_val_str . ")";
					mysql_query("INSERT INTO products_sizes (" . $item_index_key . ") VALUES (" . $item_val_str . ")");
				}else{
					foreach ($value as $itemkey => $items) {
						if(is_array($items['QtyOnHand'])){
							$items['QtyOnHand']=0;
						}
						$item_index_key = 'Style, Sku, Code, BarCode, SizeCode, SizeName, QtyOnHand';
						$item_val_str = $style. "," . $sku . ",".$items['Code'].",".$items['BarCode'].",".$items['SizeCode'].",'".$items['SizeName']."',".$items['QtyOnHand'];
						//echo"<br />"."INSERT INTO products_sizes (" . $item_index_key . ") VALUES (" . $item_val_str . ")";
						mysql_query("INSERT INTO products_sizes (" . $item_index_key . ") VALUES (" . $item_val_str . ")");

					}
				}

			}

		}
		//}
	}
	
	if($ProductImage!='')
		$SupplierProductImage = '/'.$SupplierName.'/'.$ProductImage;
	else
		$SupplierProductImage='';
	
	$find_array = array('_','-',' ');
	$replace_array = array('','','');
	$SupplierProductImage = strtolower(str_replace($find_array,$replace_array,$SupplierProductImage));
	
	//$index_key = substr(trim($index_key), 0, -1);
	//$val_str = substr(trim($val_str), 0, -1);
	$index_key = $index_key .'SupplierProductImage';
	$val_str = $val_str ."'".$SupplierProductImage."'";
	mysql_query("INSERT INTO products (" . $index_key . ") VALUES (" . $val_str . ")");
}

/************************************************************************************** 
 * Import Image Names from folders to database
 **************************************************************************************/

mysql_query("truncate table products_images");

function listFolderFiles($dir,$project_location) {
	$ffs = scandir($dir);
	foreach ($ffs as $ff) {
		if ($ff != '.' && $ff != '..' && $ff!='.DS_Store') {
			
			if (is_dir($dir . '/' . $ff)){
				listFolderFiles($dir . '/' . $ff,$project_location);
			}else{
			
			$convertimage = strtolower(str_replace($project_location,'',$dir.'/'.$ff));
			$find_array = array('_','-',' ');
			$search_array = array('','','');
			$convertimage = str_replace($find_array,$search_array,$convertimage);
			
			$sql="insert into products_images(origimage,convertimage) values('".$dir."/".$ff."','".$convertimage."')";
			mysql_query($sql);
				
			}
		}
	}
}
listFolderFiles($project_location,$project_location);
mysql_query("UPDATE products_images SET origimage = REPLACE (origimage,'".$project_location."','')");
mysql_query("update products_images pi, products p set p.ProductImage=pi.origimage WHERE pi.convertimage=p.SupplierProductImage");

/*********************************************************************
 * Up Cell products
 *********************************************************************/

$sql_upcell = "select * from products where CorpRelated!=''";
$res_upcell = mysql_query($sql_upcell);

while($row_upcell = mysql_fetch_assoc($res_upcell)){
	$sql_altcode="SELECT group_concat(Style) as upcell_products FROM `products` WHERE `AltCode` in ('".str_replace("; ","','",$row_upcell['CorpRelated'])."')";
	$res_altcode = mysql_query($sql_altcode);
	$rows_altcode = mysql_num_rows($res_altcode);
	if($rows_altcode){
		$row_altcode = mysql_fetch_assoc($res_altcode);
		mysql_query("update products set upsells='".$row_altcode['upcell_products']."' where Style=".$row_upcell['Style']);
	}	
}



/**********************************************************************
 * Update category IDs into import data 
 * ********************************************************************/
mysql_query("update `products` set Category=Ranges WHERE Ranges='Range1'");
mysql_query("update `products` set Category=Ranges WHERE Ranges='Range2'");
mysql_query("update `products` set Category=Ranges WHERE Ranges='Range3'");
mysql_query("update `products` set Category=Ranges WHERE Ranges='Range4'");
mysql_query("update `products` set Category='Corp' WHERE Ranges='Corp'");

mysql_query("update `catalog_category_entity_varchar` cpev, `catalog_category_entity` cpe, products p  set p.category_id=cpe.entity_id WHERE cpe.entity_id=cpev.entity_id and cpev.attribute_id=41 and cpev.`value`=p.Category and cpe.level=3");
mysql_query("update `catalog_category_entity_varchar` cpev, `catalog_category_entity` cpe, products p  set p.sub_category_id=cpe.entity_id WHERE cpe.entity_id=cpev.entity_id and cpev.attribute_id=41 and cpev.`value`=p.WebDepartment and cpe.level=4");

mysql_query("update products set status=0, active='0' where 1");

mysql_query("update products_old set status=0, active='0' where 1");

/******************************************************************
 * ********Disabled the not found products in xml file ************
 ******************************************************************/
 
mysql_query("update products p, products_old po set po.active='1' where p.Sku=po.Sku");

mysql_query("update products_sizes ps, products_sizes_old pso set pso.active='1',ps.active='1' where ps.BarCode=pso.BarCode");

mysql_query("update products_old po, catalog_product_entity cpe, catalog_product_entity_int cpei set cpei.value=2 where po.Sku=cpe.sku and cpe.entity_id=cpei.entity_id and cpei.attribute_id =96 and po.active='0'");


mysql_query("update products_sizes_old pso, catalog_product_entity cpe, catalog_product_entity_int cpei set cpei.value=2 where pso.BarCode=cpe.sku and cpe.entity_id=cpei.entity_id and cpei.attribute_id =96 and pso.active='0'");

//Update the Modified products in products table
//mysql_query("update products p, products_old po set p.status=1, po.status=1 where p.LastChangeDate=po.LastChangeDate and p.Sku=po.Sku");

mysql_query("update products p, products_old po set p.status=0 where p.Style=po.Style and po.status=0");

mysql_query("update products p, products_sizes ps set p.status=0 where p.Style=ps.Style and ps.active='0'");
/**************** Update category IDs into import data **************************/
?>

Open in new window

0
 
LVL 82

Accepted Solution

by:
Dave Baldwin earned 500 total points
Comment Utility
I don't know if this is fixable.  This line...
$Class['FileXml'] = simplexml_load_string($Class['UsersFileXml']);

Open in new window

gives this result for the problem.
["LongDescription"]=>
      object(SimpleXMLElement)#85 (0) {
      }

Open in new window

There is no content there.  I read this page http://php.net/manual/en/function.simplexml-load-string.php but couldn't get anything to work on your code and XML.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
This is my test program without any of the MySQL code.
<?php
ini_set("display_errors", 1);

// XML2Array
function XML2Array($XmlFilePath) {
global $jsonarray;
global $zimplexml;
	if (!file_exists($XmlFilePath))
		return FALSE;
	$Class = Array ();
	$Class['UsersFileXml'] = file_get_contents($XmlFilePath);
	$Class['FileXml'] = simplexml_load_string($Class['UsersFileXml']);
	$zimplexml = $Class['FileXml'];
	$Class['FileJson'] = json_encode($Class['FileXml']);
	$jsonarray = $Class['FileJson'];
	$Array = json_decode($Class['FileJson'], TRUE);
	unset ($Class);
	return $Array;
}

function dateformat($dateval) {
	list ($day, $month, $year) = explode("/", $dateval);
	return $year . '-' . $month . '-' . $day;
}

// get XML into an array
$array = XML2Array('products2.xml');
echo "<pre>";
//var_dump($array);
//var_dump($jsonarray);
var_dump($zimplexml);

echo "</pre>";

?>

Open in new window

0
 
LVL 1

Author Comment

by:gwh2
Comment Utility
Ok I may need to have a php developer look into this as my knowledge is very limited. Thanks for your help.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
You're welcome, glad to help.
0

Featured Post

What Is Threat Intelligence?

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

Join & Write a Comment

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
The viewer will learn how to count occurrences of each item in an array.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 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

10 Experts available now in Live!

Get 1:1 Help Now