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

Awesome MySQL table export tool. Great for backup and other operations! 
 
g17owner
Jr. Member
 
Avatar
Total Posts:  13
Joined:  2012-03-27
New Jersey
 

Hi All, I found this tutorial last night and by this morning I got it done and working flawlessly! I thought I would share with everyone because we all know there is no support with community edition.

http://www.ssdtutorials.com/tutorials/series/export-mysql-csv.html

Here is what it does:

Without having to directly enter your database, it will let you choose from a drop down menu which table from your DB you want to export. Then once you choose your table you can choose which columns you want to export.

Next, I am going to figure out how to password protect access to this page.

After that, I am going to figure out how to add my own modification to it that does the following:

I have tons of custom options. But we all know if you look in the db and find your custom options, its just a long list of options with little or no info that identifies which product sku each option belongs to. So what I am going to do is perfect a new SQL query that will compile all of the necessary data from multiple tables into one table.

Whats the purpose? Well, if I ever leave Magento, I dont want to have to figure out how to manually match up each of my 30,000+ custom options with the individual products they belong to. Its also a great way to do a backup before making other changes to your site.

Image Attachments
Capture.PNG
 
Magento Community Magento Community
Magento Community
Magento Community
 
g17owner
Jr. Member
 
Avatar
Total Posts:  13
Joined:  2012-03-27
New Jersey
 

A helpful hint for those that take this tutorial. I find that in magento, any time I use a require_once, magento doesnt like my file path and I cant seem to figure it out. So in index.php where it says “require_once(’classes/Helper.php’);” delete that and directly paste in its place the contents of helper.php and it will work with no problem.

 
Magento Community Magento Community
Magento Community
Magento Community
 
g17owner
Jr. Member
 
Avatar
Total Posts:  13
Joined:  2012-03-27
New Jersey
 

OK! Even though nobody has commented on this thread, I am going to update it because I KNOW there will be someone out there looking for this info. I looked for 2 months and nobody has it.

Here is what I am showing you. In magento, there export and download system sucks, always running out of memory, especially if you have a ton of products and even more so if you have a ton of custom options.

I currently have 30,090 custom options that are part of my product listings for 2300 products. Have you ever gone to your database and wanted to back up all those options but see that they are spread out accross many db tables and you are just overwhelmed? Well, I felt that way, but then used my brain and wrote an awesome query for the phpmyadmin sql database.

I am going to show everyone 2 queries. The first one is just a “SELECT” query which will match everything up and show you ALL columns accross those many tables that match your options to the parent products.

Query #1
Two things to note for either query #1 or #2: “mage_” is the prefix for all of my tables. You may not have a prefix or may have a different prefix. All you have to do is delete “mage_” or replace it with your own prefix. The rest of the table names should be the same.)

The other thing you should make note of in the query is the Attribute ID. Mine are 96 & 99. 96 represents the attribute that gives the name of the product that the option belongs too. 99 represents that products id number. You may need to adjust it to whatever your attribute numbers are. Its very easy.

SELECT *
FROM mage_catalog_product_option_type_title titletable
LEFT JOIN mage_catalog_product_option_type_value valuetable ON titletable.option_type_id = valuetable.option_type_id
LEFT JOIN mage_catalog_product_option_type_price pricetable ON valuetable.option_type_id = pricetable.option_type_id
LEFT JOIN mage_catalog_product_option_title optiontitle ON valuetable.option_id = optiontitle.option_id
LEFT JOIN mage_catalog_product_option optionconnect ON optiontitle.option_id = optionconnect.option_id
LEFT JOIN mage_catalog_product_entity parentsku ON optionconnect.product_id = parentsku.entity_id
LEFT JOIN mage_catalog_product_entity_varchar parentname ON parentsku.entity_id = parentname.entity_id
AND parentname.attribute_id = ‘96’
LEFT JOIN mage_catalog_product_entity_decimal parentprice ON parentsku.entity_id = parentprice.entity_id
AND parentprice.attribute_id = ‘99’

Query #2

This query will take all of this valuable info and create a new table in your database that contains all this info, but only the columns you need. Other wise you would have a ton of repetative or pointless columns. Dont worry, this does not do ANYTHING to ANY of your existing data. It just copies all the data you need into a separate table for your own use and does not affect anything in any way.

CREATE TABLE custom_options_backup (SELECT titletable.option_type_id AS option_id,titletable.title AS option_title,valuetable.sku AS option_sku,pricetable.price AS price_difference,pricetable.price_type AS price_diff_type,valuetable.sort_order AS sort_order,optionconnect.type AS option_type,optionconnect.max_characters AS max_characters,valuetable.option_id AS option_heading_id,optiontitle.title AS option_heading,optionconnect.sku AS option_heading_sku,optionconnect.is_enabled AS is_enabled,optionconnect.is_require AS is_required,optionconnect.product_id AS parent_prod_id,parentsku.sku AS parent_sku,parentname.value AS parent_name,parentprice.value AS parent_price
FROM mage_catalog_product_option_type_title titletable
LEFT JOIN mage_catalog_product_option_type_value valuetable ON titletable.option_type_id = valuetable.option_type_id
LEFT JOIN mage_catalog_product_option_type_price pricetable ON valuetable.option_type_id = pricetable.option_type_id
LEFT JOIN mage_catalog_product_option_title optiontitle ON valuetable.option_id = optiontitle.option_id
LEFT JOIN mage_catalog_product_option optionconnect ON optiontitle.option_id = optionconnect.option_id
LEFT JOIN mage_catalog_product_entity parentsku ON optionconnect.product_id = parentsku.entity_id
LEFT JOIN mage_catalog_product_entity_varchar parentname ON parentsku.entity_id = parentname.entity_id
AND parentname.attribute_id = ‘96’
LEFT JOIN mage_catalog_product_entity_decimal parentprice ON parentsku.entity_id = parentprice.entity_id
AND parentprice.attribute_id = ‘99’)

Anyways, I took the time to do the research and post this and I hope it helps someone. If you have any questions, I would be happy to try to point you in the right direction.

Enjoy!

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top