Author Topic: XML export  (Read 2151 times)

daninet

  • 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]
<channel>
<item>
<product_sku>00001</product_sku>
<product_name><![CDATA[Product 1 name]]></product_name>
<product_desc><![CDATA[<p>any html</p>]]></product_desc>
<price_with_tax>35,64</price_with_tax>
<picture_url>http://www.xy.com/components/com_virtuemart/shop_image/product/xy.jpg</picture_url>
<product_url><![CDATA[http://www.xy.com/index.php?option=com_virtuemart&Itemid=......]]></product_url>
<product_availability></product_availability>
<manufacturer_name><![CDATA[Manufacturer's name]]></manufacturer_name>
</item>
</channel>

The PHP to export looks like this:

Code: [Select]
<?php


$host 
"****";
$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
            jos_vm_product.product_sku,
            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('http://www.xy.com/components/com_virtuemart/shop_image/product/',jos_vm_product.product_full_image) AS picture_url,
            CONCAT('<![CDATA[http://www.xy.com/index.php?option=com_virtuemart&Itemid=28&page=shop.product_details&flypage=flypage.tpl&product_id=',jos_vm_product.product_id,'&category_id=',jos_vm_product_category_xref.category_id,']]>') AS product_url,
            jos_vm_product.product_availability,
            CONCAT('<![CDATA[',jos_vm_manufacturer.mf_name,']]>') AS manufacturer_name
         FROM
            jos_vm_product
            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
         WHERE
            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\"?>
\r\n";

$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);
fclose($file);

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!