Assign Related Products and Upsells to Simple Products Based on Grouping

Last modified by fuzzybabybunny on Fri, June 25, 2010 09:18
Source|Old Revisions  

This is an old revision of the document!


Special thanks to user dd_gi for providing this invaluable script. He takes all credit for this work.

Original Thread: http://www.magentocommerce.com/boards/viewthread/62575/

If you already have grouped products that have been associated with multiple simple products, you can quickly assign the simple products to their related products and upsell products.

The following script will work for the following case:

Grouped Product 1 contains Simple Products A, B, C, and D

You want:

Simple Product A to be related to Simple Product B, C, and D, and to have Grouped Product 1 as an upsell.

Simple Product B to be related to Simple Product A, C, and D, and to have Grouped Product 1 as an upsell.

Simple Product C to be related to Simple Product A, B, and D, and to have Grouped Product 1 as an upsell.

Simple Product D to be related to Simple Product A, B, and C, and to have Grouped Product 1 as an upsell.

Instructions

Save the following code 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.

  1. <pre>
  2. <?php
  3. $hostname='127.0.0.1';
  4. $username='yourdbusername';
  5. $password='yourdbpassword';
  6. $database='yourdb';
  7. $start_time=microtime(true);
  8. $link=mysql_connect($hostname,$username,$password);
  9. if (!$link) {
  10.     die("nCould not connect to server $hostname: " . mysql_error());
  11. }
  12. echo "nConnected successfully to server $hostname.";
  13. $result=mysql_query("USE `$database`;");
  14. if (!$result) {
  15.     die("nCould not connect to database $database: " . mysql_error());
  16. }
  17. echo "nConnected successfully to database $database.";
  18. $result = mysql_query("SELECT link_type_id,code FROM catalog_product_link_type;");
  19. $link_type=array();
  20. while ($row = mysql_fetch_array($result)) {
  21.     $link_type[$row['code']]=$row['link_type_id'];
  22. }
  23. $grouped_result = mysql_query("SELECT entity_id,type_id FROM catalog_product_entity WHERE type_id='grouped';");
  24. while ($grouped = mysql_fetch_array($grouped_result)) {
  25.     $grouped_product_id=$grouped['entity_id'];
  26.     echo "nFound grouped product $grouped_product_id";
  27.     $super_result = mysql_query("SELECT linked_product_id FROM catalog_product_link WHERE (product_id='$grouped_product_id' && link_type_id='{$link_type['super']}');");
  28.     $linked_products=array();
  29.     while ($super = mysql_fetch_array($super_result)) {
  30.         $super_product_id=$super['linked_product_id'];
  31.         echo "nFound linked product $super_product_id";
  32.         $linked_products[]=$super_product_id;
  33.     }
  34.     foreach($linked_products as $linked_product){ //product_id     linked_product_id     link_type_id
  35.         echo "nChecking linked product $linked_product";
  36.         foreach($linked_products as $linked_target_product){
  37.             if($linked_target_product==$linked_product){
  38.                 continue;
  39.             }
  40.             $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']}');");
  41.             if(mysql_num_rows($linked_target_product_result) > 0){
  42.                 echo "nFound cross-sell link for product $linked_target_product";
  43.             } else {
  44.                 mysql_query("INSERT INTO catalog_product_link VALUES (NULL,'$linked_product','$linked_target_product','{$link_type['cross_sell']}');");
  45.                 echo "nAdded cross-sell link for product $linked_target_product";
  46.             }
  47.             $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']}');");
  48.             if(mysql_num_rows($linked_target_product_result) > 0){
  49.                 echo "nFound relation link for product $linked_target_product";
  50.             } else {
  51.                 mysql_query("INSERT INTO catalog_product_link VALUES (NULL,'$linked_product','$linked_target_product','{$link_type['relation']}');");
  52.                 echo "nAdded relation link for product $linked_target_product";
  53.             }
  54.         }
  55.         $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']}');");
  56.         if(mysql_num_rows($upsell_product_result) > 0){
  57.             echo "nFound up-sell link for product $grouped_product_id";
  58.         } else {
  59.             mysql_query("INSERT INTO catalog_product_link VALUES (NULL,'$linked_product','$grouped_product_id','{$link_type['up_sell']}');");
  60.             echo "nAdded up-sell link for product $grouped_product_id";
  61.         }
  62.         $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']}');");
  63.         if(mysql_num_rows($cross_sell_product_result) > 0){
  64.             echo "nFound cross-sell link for product $grouped_product_id";
  65.         } else {
  66.             mysql_query("INSERT INTO catalog_product_link VALUES (NULL,'$linked_product','$grouped_product_id','{$link_type['cross_sell']}');");
  67.             echo "nAdded cross-sell link for product $grouped_product_id";
  68.         }
  69.     }
  70. }
  71. mysql_close($link);
  72. $end_time=microtime(true);
  73. $elapsed_time = sprintf("%0.6f", ($end_time - $start_time));
  74. echo "nCompleted in $elapsed_time seconds.";
  75. ?>



 

Magento 2 GitHub Repository

Magento Job Board - Some sort of tag line goes here

Latest Posts| View all Jobs