Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Page 1 of 2
XML Product Export - Very Fast - Bypasses Magento Code - RSS Feed - MySQL Query and PHP File
 
VonCali
Jr. Member
 
Total Posts:  3
Joined:  2010-05-20
 

Hey guys and gals -
I was trying to export all of the products from the Magento cart to an external site using the RSS feeds but I have nearly 5,000 products and it was taking WAY to long to generate the file. Then I started having issues with it returning a 500 error. I decided to skip all of the overhead that Magento seems to include with any of its operations and wrote a PHP script to handle this straight out of the database. Feel free to use and customize as much as you’d like. Some of the data being exported are my site’s custom attributes.

To add any more attributes to this just find them in the eav_attribute table and take a look at the frontend_label column. You can match these up to the catalog_product_entity_DATATYPE table using the attribute_id and the backend_type columns.

I am not a PHP/MySQL expert and this file was written rather quickly so if you know of ways to improve on it please comment and help out!

Let me know if you have any questions and I will do what I can to help.

Thanks,
Rich

<?php
    
/****************************************************
    Author: Rich Calzada
    Date:   2010.05.20    
    ****************************************************/
    
header ("content-type: text/xml");
    
    
//Setup Connection information
    
$dbhost 'DB_HOST';
    
$dbuser 'DB_USER';
    
$dbpass 'DB_PASSWORD';
    
    
//Connect to the database
    
$conn mysql_connect($dbhost$dbuser$dbpass) or die('Error connecting to mysql');
    
    
//Point to specific DB
    
$dbname 'DB_NAME';
    
mysql_select_db($dbname);

    
//Create the Query to get the products
    
$sql "SELECT DISTINCT P.entity_id, P.sku, V.value AS Name, T1.value AS ProdDesc, T2.value AS ShortDesc,
    T5.value AS ModelMachine, D.value AS Price, CONCAT('http://www.yourdomain.com/', V1.value) AS Link, 
    CASE
        WHEN V2.Value IS NULL
            THEN NULL
        ELSE CONCAT('http://www.yourdomain.com/media/catalog/product', V2.value)
    END AS Image
    FROM  catalog_product_entity AS P INNER JOIN
    catalog_product_entity_varchar AS V  ON P.entity_id = V.entity_id  AND V.attribute_id  = 60  LEFT JOIN
    catalog_product_entity_varchar AS V1 ON P.entity_id = V1.entity_id AND V1.attribute_id = 87  LEFT JOIN
    catalog_product_entity_varchar AS V2 ON P.entity_id = V2.entity_id AND V2.attribute_id = 74  LEFT JOIN
    catalog_product_entity_text    AS T1 ON P.entity_id = T1.entity_id AND T1.attribute_id = 61  LEFT JOIN
    catalog_product_entity_text    AS T2 ON P.entity_id = T2.entity_id AND T2.attribute_id = 62  LEFT JOIN
    catalog_product_entity_text    AS T5 ON P.entity_id = T5.entity_id AND T5.attribute_id = 542 LEFT JOIN
    catalog_product_entity_decimal AS D  ON P.entity_id = D.entity_id  AND D.attribute_id  = 64"
;

    
//Run the query
    
$query mysql_query($sql);
    
    
//Begin the output
    
echo '<?xml version="1.0" encoding="ISO-8859-1"?>';
    echo 
'<catalog>';
    
    
//Loop through and print each products info
    
while($row mysql_fetch_array($query))
    

        
echo '<item>';
        echo 
'<entity_id>'.$row['entity_id'].'</entity_id>';
        echo 
'<sku>'.$row['sku'].'</sku>';
        echo 
'<name>'.$row['Name'].'</name>';
        echo 
'<description>'.$row['ProdDesc'].'</description>';
        echo 
'<shortdesc>'.$row['ShortDesc'].'</shortdesc>';
        echo 
'<modelmachine>'.$row['ModelMachine'].'</modelmachine>';
        echo 
'<price>'.$row['Price'].'</price>';
        echo 
'<link>'.$row['Link'].'</link>';
        echo 
'<image>'.$row['Image'].'</image>';
        echo 
'</item>';
    
}
    
    
//Finish output
    
echo '</catalog>';
?>
 
Magento Community Magento Community
Magento Community
Magento Community
 
FrankTheDog
Jr. Member
 
Avatar
Total Posts:  25
Joined:  2009-09-23
 

VonCall, Thank you for sharing!

 
Magento Community Magento Community
Magento Community
Magento Community
 
mrtech
Sr. Member
 
Total Posts:  87
Joined:  2008-06-30
 

hi
i just ran this query

SELECT DISTINCT P.entity_id, P.sku, V.value AS Name, T1.value AS ProdDesc, T2.value AS ShortDesc,
T5.value AS ModelMachine, D.value AS Price, CONCAT(’http://www.yourdomain.com/’, V1.value) AS Link,
CASE
WHEN V2.Value IS NULL
THEN NULL
ELSE CONCAT(’http://www.yourdomain.com/media/catalog/product’, V2.value)
END AS Image
FROM catalog_product_entity AS P INNER JOIN
catalog_product_entity_varchar AS V ON P.entity_id = V.entity_id AND V.attribute_id = 60 LEFT JOIN
catalog_product_entity_varchar AS V1 ON P.entity_id = V1.entity_id AND V1.attribute_id = 87 LEFT JOIN
catalog_product_entity_varchar AS V2 ON P.entity_id = V2.entity_id AND V2.attribute_id = 74 LEFT JOIN
catalog_product_entity_text AS T1 ON P.entity_id = T1.entity_id AND T1.attribute_id = 61 LEFT JOIN
catalog_product_entity_text AS T2 ON P.entity_id = T2.entity_id AND T2.attribute_id = 62 LEFT JOIN
catalog_product_entity_text AS T5 ON P.entity_id = T5.entity_id AND T5.attribute_id = 542 LEFT JOIN
catalog_product_entity_decimal AS D ON P.entity_id = D.entity_id AND D.attribute_id = 64;

on mysql DB and didn’t return any results
whats wrong?

 
Magento Community Magento Community
Magento Community
Magento Community
 
VonCali
Jr. Member
 
Total Posts:  3
Joined:  2010-05-20
 
mrtech - 13 June 2010 05:31 PM

on mysql DB and didn’t return any results
whats wrong?

@MrTech -
In my example I included the selection of some of my custom attributes. If you don’t have the same attributes in your install (Model/Machine, etc.) you will get zero results returned. You’ll need to modify the SQL to look up only the product attributes that are in the standard Magento install. Let me know if you still need help and I can try to take a second to post some code that should work for you.

Thanks!
Rich

 
Magento Community Magento Community
Magento Community
Magento Community
 
Maxpatat
Jr. Member
 
Total Posts:  16
Joined:  2009-10-21
 

Great Job,

I had to modify it to my needs but it’s lightning fast!

Cheers,
mark

 
Magento Community Magento Community
Magento Community
Magento Community
 
VonCali
Jr. Member
 
Total Posts:  3
Joined:  2010-05-20
 

@Maxpatat -
grin Awesome! Glad I could help!

 
Magento Community Magento Community
Magento Community
Magento Community
 
Liviu Jianu
Jr. Member
 
Total Posts:  7
Joined:  2010-05-11
 

great post, i managed to add some more code with the help of a skilled friend and now it exports to a .xml and a .csv

 
Magento Community Magento Community
Magento Community
Magento Community
 
webscot
Sr. Member
 
Total Posts:  192
Joined:  2009-05-12
 

Would you please be willing to share your upgraded SQL/PHP file(s)? I tried the file posted above in 1.4.0.1 and it gave me only a message

This XML file does not appear to have any style information associated with it.
The document tree is shown below.
with output of
<catalog/>
and nothing else. I matched IDs and removed VonCali’s custom tags to no avail. I’m currently stuck at exporting 4500 items.
 
Magento Community Magento Community
Magento Community
Magento Community
 
Sander
Sr. Member
 
Avatar
Total Posts:  232
Joined:  2008-01-08
Netherlands
 

Yeah, I wouldn’t go with direct DB queries for that… There’s some import/export extensions around I think do this pretty well.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Yellow Satsuma
Member
 
Total Posts:  41
Joined:  2010-02-13
 

Ive been using this without any issue for some time now, however after putting it on a friends store i notice as he uses tax and i dont the database is pulling the exc tax price, is there a way this code can be modified to add the tax?

 
Magento Community Magento Community
Magento Community
Magento Community
 
markushauker
Jr. Member
 
Avatar
Total Posts:  3
Joined:  2011-01-15
Indianapolis
 

Nice. Thanks for the share! Something new to try in our dev environment. wink

 
Magento Community Magento Community
Magento Community
Magento Community
 
Dakshika
Jr. Member
 
Total Posts:  13
Joined:  2009-04-21
Maharagama
 

hi is that possible to add category name with each product as wel…

 
Magento Community Magento Community
Magento Community
Magento Community
 
r.vthoff
Member
 
Total Posts:  31
Joined:  2011-02-03
 
Liviu Jianu - 30 August 2010 10:30 AM

great post, i managed to add some more code with the help of a skilled friend and now it exports to a .xml and a .csv

Can you post your added code please? It will be very useful for me and others. Thanks in advance!

 
Magento Community Magento Community
Magento Community
Magento Community
 
jmayo05
Jr. Member
 
Total Posts:  12
Joined:  2010-04-06
 

Has anyone per chance figured out how to modify the code above to export ALL product attributes?

Running the profile/data flow option built into Magento is a joke, especially when you have thousands of products to export.

I have over 100 attributes that need exported, and coding them one by one really isn’t a viable option. I CAN do it, but that sort of defeats the point of code poetry, right?  tongue rolleye

 
Magento Community Magento Community
Magento Community
Magento Community
 
dwuethrich
Jr. Member
 
Total Posts:  20
Joined:  2009-05-29
 

Thanks.... I just use the sql part and modifed the attributes.

This works great and a lot faster than the export feature within the Magento admin.

 
Magento Community Magento Community
Magento Community
Magento Community
 
lbeetles
Member
 
Total Posts:  66
Joined:  2009-02-04
 
dwuethrich - 09 March 2011 04:09 PM

Thanks.... I just use the sql part and modifed the attributes.

This works great and a lot faster than the export feature within the Magento admin.

Hi dwuethrich,

Can you post your code please as i cannot seem to get this to work.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 2