Author Topic: XML export  (Read 2092 times)


  • Beginner
  • *
  • Posts: 8
XML export
« on: May 29, 2012, 20:48:05 pm »
Hello everyone!

I've got a problem to solve. We have a couple of price comparing sites in my country. Few of them are free to use the others works in a money per click method to make your shop gain some profit.
There is a basic code to export all the products via cron into an xml table look like this. (Most of the price comparing sites accept it)

Code: [Select]
<product_name><![CDATA[Product 1 name]]></product_name>
<product_desc><![CDATA[<p>any html</p>]]></product_desc>
<manufacturer_name><![CDATA[Manufacturer's name]]></manufacturer_name>

The PHP to export looks like this:

Code: [Select]

$user "*****";
$pass "*****";
$database "****"

$linkID mysql_connect($host$user$pass) or die("Could not connect to host.");
mysql_select_db($database$linkID) or die("Could not find database.");

$query "SELECT
            CONCAT('<![CDATA[',jos_vm_product.product_name,']]>') AS product_name,
            CONCAT('<![CDATA[',jos_vm_product.product_desc,']]>') AS product_desc, 
            ROUND((jos_vm_product_price.product_price*jos_vm_tax_rate.tax_rate)+jos_vm_product_price.product_price) AS price_with_tax,
            CONCAT('',jos_vm_product.product_full_image) AS picture_url,
            CONCAT('<![CDATA[',jos_vm_product.product_id,'&category_id=',jos_vm_product_category_xref.category_id,']]>') AS product_url,
            CONCAT('<![CDATA[',jos_vm_manufacturer.mf_name,']]>') AS manufacturer_name
            Inner Join jos_vm_product_price ON jos_vm_product.product_id = jos_vm_product_price.product_id
            Inner Join jos_vm_tax_rate ON jos_vm_product.product_tax_id = jos_vm_tax_rate.tax_rate_id
            Inner Join jos_vm_product_mf_xref ON jos_vm_product.product_id = jos_vm_product_mf_xref.product_id
            Inner Join jos_vm_manufacturer ON jos_vm_product_mf_xref.manufacturer_id = jos_vm_manufacturer.manufacturer_id
            Inner Join jos_vm_product_category_xref ON jos_vm_product.product_id = jos_vm_product_category_xref.product_id
            Inner Join jos_vm_category ON jos_vm_product_category_xref.category_id = jos_vm_category.category_id
            jos_vm_product.product_publish =  'Y'
mysql_query('SET NAMES utf8');
$resultID  =  mysql_query($query$linkID) or die("Data not found.");

$xml_output "<?xml version=\"1.0\"?>

$xml_output .= "<channel>\n";

for($x = 0 ; $x < mysql_num_rows($resultID) ; $x++){
    $row = mysql_fetch_assoc($resultID);
    $xml_output .= "<item>\n";
    $xml_output .= "<product_sku>" . $row['product_sku'] . "</product_sku>\n";
    $xml_output .= "<product_name>" . $row['product_name'] . "</product_name>\n";
    $xml_output .= "<product_desc>" . $row['product_desc'] . "</product_desc>\n";
    $xml_output .= "<price_with_tax>" . $row['price_with_tax'] . "</price_with_tax>\n";
    $xml_output .= "<picture_url>" . $row['picture_url'] . "</picture_url>\n";
    $xml_output .= "<product_url>" . $row['product_url'] . "</product_url>\n";
    $xml_output .= "<product_availability>" . $row['product_availability'] . "</product_availability>\n";
    $xml_output .= "<manufacturer_name>" . $row['manufacturer_name'] . "</manufacturer_name>\n";
    $xml_output .= "</item>\n\n";


$xml_output .= "</channel>";

$file= fopen("output.xml", "w");
fwrite($file, $xml_output);

echo "Ready";


My problem is that one of the sites need hierarchic category to show in the xml so they want a line like this:
Code: [Select]
<product_category>Parent category > Category1 > Caregory2</product_category>
I'm not an expert, but I know that VM uses category ID's to list products into categories. Is there any solution for that case? How I should modify the PHP code? I would be glad with the last category's name in the XML which the ID refers to.
Thanks for any answer!