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

Faster way to assign related products and cross sells to a simple product? 
 
fuzzybabybunny
Member
 
Total Posts:  46
Joined:  2009-08-08
 

I’ve got 2000+ simple products and many of them are contained within 300 grouped products.

I’ve already taken the 300 grouped products and assigned the simple products that comprise them, but now I want to quickly assign the simple products to other related products.

Example:

Grouped Product 1 contains Simple Products A, B, C, and D (this has already been assigned)

Simple Product A should then logically be assigned Simple Product B, C, D and Grouped Product 1 as related or cross sells.
Simple Product B should then logically be assigned Simple Product A, C, D and Grouped Product 1 as related or cross sells.
Simple Product C should then logically be assigned Simple Product A, B, D and Grouped Product 1 as related or cross sells.
etc.

Obviously I don’t want to manually configure 2000+ simple products with their related/cross sell products via the admin panel. There should be a faster way to do this, no? I’m a complete noob at SQL and PHP though.

 
Magento Community Magento Community
Magento Community
Magento Community
 
fuzzybabybunny
Member
 
Total Posts:  46
Joined:  2009-08-08
 
dd_gi - 04 November 2009 03:58 PM

That’s easily enough done with code, to start you would need to populate a csv with the id numbers and type of associations.

For example, say that you have a simple product id 1 and you want to assign simple products 2 and 3 as cross sells and simple product 4, grouped product 5 as related products, you would have a csv line that reads:

1,c2,c3,r4,r5

The general format the script will read is the first column would be the target product and any other columns in that row will be considered for association, either cross sell or related.  Just add a new line for each target product with the appropriate association columns.

Is that sounds acceptable let me know and I will work up the required code.

1. If this is the case then I would first need to export a csv from Magento DB that has columns:

Grouped Product ID, Associated Simple Product IDs that belong in this Grouped Product

So: [GroupedProduct1] , [A, B, C, D]

2. Then I would take this file and make with formulas a new CSV:

SimpleProduct,CrossSells/Related

A, GroupedProduct1,B,C,D
B, GroupedProduct1,A,C,D
C, GroupedProduct1,A,B,D
D, GroupedProduct1,A,B,C

And then run the script on it.

But I don’t know how to do #1.

 
Magento Community Magento Community
Magento Community
Magento Community
 
fuzzybabybunny
Member
 
Total Posts:  46
Joined:  2009-08-08
 
dd_gi - 04 November 2009 05:36 PM

fuzzybabybunny - 04 November 2009 05:00 PM
dd_gi - 04 November 2009 03:58 PM
That’s easily enough done with code, to start you would need to populate a csv with the id numbers and type of associations.

For example, say that you have a simple product id 1 and you want to assign simple products 2 and 3 as cross sells and simple product 4, grouped product 5 as related products, you would have a csv line that reads:

1,c2,c3,r4,r5

The general format the script will read is the first column would be the target product and any other columns in that row will be considered for association, either cross sell or related.  Just add a new line for each target product with the appropriate association columns.

Is that sounds acceptable let me know and I will work up the required code.

1. If this is the case then I would first need to export a csv from Magento DB that has columns:

Grouped Product ID, Associated Simple Product IDs that belong in this Grouped Product

So: [GroupedProduct1] , [A, B, C, D]

2. Then I would take this file and make with formulas a new CSV:

SimpleProduct,CrossSells/Related

A, GroupedProduct1,B,C,D
B, GroupedProduct1,A,C,D
C, GroupedProduct1,A,B,D
D, GroupedProduct1,A,B,C

And then run the script on it.

But I don’t know how to do #1.

Can I assume from your example that you want to find all grouped products in your db and create cross sell/related (two different associations BTW) of its associated products as shown in your example?

Yup, export all grouped products with the associated simple products. With that I can assign related products to each simple product and then import.

 
Magento Community Magento Community
Magento Community
Magento Community
 
fuzzybabybunny
Member
 
Total Posts:  46
Joined:  2009-08-08
 
dd_gi - 04 November 2009 06:17 PM

My plan was to do all of that with one script, if it can be assumed that any assciated products found with grouped products should be related to the grouped products and other associated products as shown.  No import/export should be required if this can be applied to all grouped products.

I mean, yeah, if you could do that with just one script that’d be amazing!

 
Magento Community Magento Community
Magento Community
Magento Community
 
fuzzybabybunny
Member
 
Total Posts:  46
Joined:  2009-08-08
 

Your way makes more sense. I don’t even know how to run scripts though. If it’s a php script, do I just upload it to, say, the root directly and browse to http://www.mystore.com/nameofscript.php?

 
Magento Community Magento Community
Magento Community
Magento Community
 
fuzzybabybunny
Member
 
Total Posts:  46
Joined:  2009-08-08
 
dd_gi - 05 November 2009 04:38 PM

Here’s the code, save as linkprods.php at the root of your site and run it from a browser.  If you want to run it from an SSH prompt you can use php linkprods.php

Put in your hostname, db username, db password, db name.

<pre>
<?php
$hostname
='127.0.0.1';
$username='yourdbusername';
$password='yourdbpassword';
$database='yourdb';
$start_time=microtime(true);
$link=mysql_connect($hostname,$username,$password);
if (!
$link{
    
die("\nCould not connect to server $hostname: " mysql_error());
}
echo "\nConnected successfully to server $hostname.";
$result=mysql_query("USE `$database`;");
if (!
$result{
    
die("\nCould not connect to database $database: " mysql_error());
}
echo "\nConnected successfully to database $database.";
$result mysql_query("SELECT link_type_id,code FROM catalog_product_link_type;");
$link_type=array();
while (
$row mysql_fetch_array($result)) {
    $link_type[$row[
'code']]=$row['link_type_id'];
}
$grouped_result 
mysql_query("SELECT entity_id,type_id FROM catalog_product_entity WHERE type_id='grouped';"); 
while (
$grouped mysql_fetch_array($grouped_result)) {
    $grouped_product_id
=$grouped['entity_id'];
    echo 
"\nFound grouped product $grouped_product_id";
    
$super_result mysql_query("SELECT linked_product_id FROM catalog_product_link WHERE (product_id='$grouped_product_id' && link_type_id='{$link_type['super']}');");
    
$linked_products=array();
    while (
$super mysql_fetch_array($super_result)) {
        $super_product_id
=$super['linked_product_id'];
        echo 
"\nFound linked product $super_product_id";
        
$linked_products[]=$super_product_id;
    
}
    
foreach($linked_products as $linked_product)//product_id     linked_product_id     link_type_id
        
echo "\nChecking linked product $linked_product";
        foreach(
$linked_products as $linked_target_product){
            
if($linked_target_product==$linked_product){
                
continue;
            
}
            $linked_target_product_result 
mysql_query("SELECT linked_product_id FROM catalog_product_link WHERE (product_id='$linked_product' && linked_product_id='$linked_target_product' && link_type_id='{$link_type['cross_sell']}');");
            if(
mysql_num_rows($linked_target_product_result) > 0){
                
echo "\nFound cross-sell link for product $linked_target_product";
            
else {
                mysql_query
("INSERT INTO catalog_product_link VALUES (NULL,'$linked_product','$linked_target_product','{$link_type['cross_sell']}');");
                echo 
"\nAdded cross-sell link for product $linked_target_product";
            
}
            $linked_target_product_result 
mysql_query("SELECT linked_product_id FROM catalog_product_link WHERE (product_id='$linked_product' && linked_product_id='$linked_target_product' && link_type_id='{$link_type['relation']}');");
            if(
mysql_num_rows($linked_target_product_result) > 0){
                
echo "\nFound relation link for product $linked_target_product";
            
else {
                mysql_query
("INSERT INTO catalog_product_link VALUES (NULL,'$linked_product','$linked_target_product','{$link_type['relation']}');");
                echo 
"\nAdded relation link for product $linked_target_product";
            
}
        }
        $upsell_product_result 
mysql_query("SELECT linked_product_id FROM catalog_product_link WHERE (product_id='$linked_product' && linked_product_id='$grouped_product_id' && link_type_id='{$link_type['up_sell']}');");
        if(
mysql_num_rows($upsell_product_result) > 0){
            
echo "\nFound up-sell link for product $grouped_product_id";
        
else {
            mysql_query
("INSERT INTO catalog_product_link VALUES (NULL,'$linked_product','$grouped_product_id','{$link_type['up_sell']}');");
            echo 
"\nAdded up-sell link for product $grouped_product_id";
        
}
        $cross_sell_product_result 
mysql_query("SELECT linked_product_id FROM catalog_product_link WHERE (product_id='$linked_product' && linked_product_id='$grouped_product_id' && link_type_id='{$link_type['cross_sell']}');");
        if(
mysql_num_rows($cross_sell_product_result) > 0){
            
echo "\nFound cross-sell link for product $grouped_product_id";
        
else {
            mysql_query
("INSERT INTO catalog_product_link VALUES (NULL,'$linked_product','$grouped_product_id','{$link_type['cross_sell']}');");
            echo 
"\nAdded cross-sell link for product $grouped_product_id";
        
}
    }
}
mysql_close
($link);
$end_time=microtime(true);
$elapsed_time sprintf("%0.6f", ($end_time $start_time));
echo 
"\nCompleted in $elapsed_time seconds.";
?>

How the.... do you just sit down and type out this mass of code by hand? This is incredible!

I’ll try it later tonight. And yes, I know how to backup the DB, both via the Admin Panel and PHPMyAdmin.

 
Magento Community Magento Community
Magento Community
Magento Community
 
fuzzybabybunny
Member
 
Total Posts:  46
Joined:  2009-08-08
 

It worked! Woot! Thanks so much! Are you going to post this on the wiki? I can do it if you want and give you credits smile

 
Magento Community Magento Community
Magento Community
Magento Community
 
fuzzybabybunny
Member
 
Total Posts:  46
Joined:  2009-08-08
 

Here it is smile

http://www.magentocommerce.com/wiki/how-to/assign_related_products_and_upsells_to_simple_products_based_on_grouping

 
Magento Community Magento Community
Magento Community
Magento Community
 
walltoolsllc
Sr. Member
 
Total Posts:  146
Joined:  2008-08-21
 
fuzzybabybunny - 08 November 2009 08:40 PM

Here it is smile

http://www.magentocommerce.com/wiki/how-to/assign_related_products_and_upsells_to_simple_products_based_on_grouping

What are the Visibility of your Simple Products set to in Magento?  Catalog, Search; Search; or Nowhere?  We are trying to set the visibility of our Simple products that are part of a Group to Search only, so that we can simply show all sizes of a product from one screen and not clutter the site with a different product for every size.  However, we cannot seem to add Grouped products as Upsell items?  Therefore, since most of our products are Grouped, we cannot add any Upsell items?

If we set the Visibility to Catalog, Search then we can add the Simple products as Upsells.  This does defeat the purpose of having Groups though. Any advice?

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