How to add a custom option to all products

Last modified by dd_gi on Fri, June 25, 2010 09:29
Source|Old Revisions  

It is simple to add custom options to multiple products, as long as you abandon the Magento Admin GUI and use a stand-alone .php file populated with the custom options details you want to add.

Consider this example where I had a candy store web site with 1 option/11 rows, 4 stores with 3 language translations each, and 1 category with 14 products. For the example I wanted to add the same custom option/rows to all products (in the various language translations) which would have required many hours of tedious entries.

Please take the time to understand what the script below does before running it; it DELETES all custom options you may already have assigned and assigns a single custom option with 11 rows to ALL products in your database. It does this because that’s what I needed, if you need something different please modify the script accordingly.

The example code includes custom price additions for the different options and sets a custom sort order as well.

Backup your database and use the code at your own risk. I am providing it only as an example of what can be done to programatically add custom items in bulk, hopefully if you have use for the script it will be simple enough to modify it for your own needs.

  1. <!DOCTYPE html PUBLIC "-W3CDTD XHTML 1.0 TransitionalEN" "http:www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  2. <html xmlns="http:www.w3.org/1999/xhtml">
  3. <head>
  4. <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  5. </head>
  6. <body>
  7. <pre>
  8. <?php
  9. $res = mysql_pconnect('localhost', 'username', 'password');
  10. mysql_select_db('magento');
  11. mysql_query("SET NAMES 'utf8';", $res);
  12. mysql_query("SET CHARACTER SET 'utf8';", $res);
  13. $query = 'select entity_id from catalog_product_entity';
  14. $res = mysql_query($query);
  15. $products=array();
  16. while ($ret = mysql_fetch_array($res)) {
  17.     $products[]=$ret[0];
  18.     $query = "UPDATE catalog_product_entity set has_options=1 where entity_id=$ret[0]";
  19.     echo "$query<br>";
  20.     $res1 = mysql_query($query);
  21. }
  22. echo "Set all products for has_options in catalog_product_entity.<br>";
  23. $res = mysql_query('DELETE from catalog_product_option');
  24. $res = mysql_query('DELETE from catalog_product_option_title');
  25. $res = mysql_query('DELETE from catalog_product_option_type_price');
  26. $res = mysql_query('DELETE from catalog_product_option_type_title');
  27. $res = mysql_query('DELETE from catalog_product_option_type_value');
  28. echo "Deleted all rows from catalog_product_option* tables.<br>";
  29. foreach ($products as $product){
  30.     $query = "insert into catalog_product_option (product_id,type,is_require,image_size_x,image_size_y,sort_order) values ($product,'drop_down',0,0,0,0)";
  31.     echo "$query<br>";
  32.     $res1 = mysql_query($query);
  33. }
  34. echo "Populated catalog_product_option.<br>";
  35. $option_titles=array('en_US'=>'Optional Coating',
  36.                      'pt_BR'=>'Revestimento Opcional',
  37.                      'es_ES'=>'Recubrimiento Opcional');
  38. $res = mysql_query('SELECT * FROM `core_config_data` WHERE path="general/locale/code"');
  39. while ($ret = mysql_fetch_array($res)) {
  40.     $stores[$ret['value']][]=$ret['scope_id'];
  41. }
  42. $res = mysql_query('select * from catalog_product_option');
  43. $sort_orders=array(0,
  44.                    1,
  45.                    1,
  46.                    1,
  47.                    1,
  48.                    1,
  49.                    1,
  50.                    1,
  51.                    1,
  52.                    1,
  53.                    1);
  54. while ($ret = mysql_fetch_array($res)) {
  55.     foreach($stores as $locale=>$scopes){
  56.         foreach($scopes as $scope){
  57.             $query = "insert into catalog_product_option_title (option_id,store_id,title) values ($ret[0],$scope,'$option_titles[$locale]')";
  58.             echo "$query<br>";
  59.             $res1 = mysql_query($query);
  60.         }
  61.     }
  62.     foreach($sort_orders as $order){
  63.         $query = "insert into catalog_product_option_type_value (option_id,sort_order) values ($ret[0],$order)";
  64.         echo "$query<br>";
  65.         $res1 = mysql_query($query);
  66.     }
  67. }
  68. echo "Populated catalog_product_option_title.<br>";
  69. echo "Populated catalog_product_option_type_value.<br>";
  70. $prices=array(0,
  71.               1.00,
  72.               0.50,
  73.               1.00,
  74.               1.00,
  75.               1.00,
  76.               1.00,
  77.               1.00,
  78.               2.00,
  79.               3.00,
  80.               0.50);
  81. $option_type_titles=array(
  82.               'en_US'=>array(
  83.                           'White sugar, fine (included in price)',
  84.                           'White sugar, coarse',
  85.                           'White sugar, powdered',
  86.                           'Red sugar, medium',
  87.                           'Blue sugar, medium',
  88.                           'Green sugar, medium',
  89.                           'Yellow sugar, medium',
  90.                           'Pink sugar, medium',
  91.                           'Chocolate sprinkles',
  92.                           'Rainbow nonpareils',
  93.                           'Shredded coconut'),
  94.               'pt_BR'=>array(
  95.                           'açúcar branco, multa (incluído no preço)',
  96.                           'açúcar branco, grosseiros',
  97.                           'açúcar branco, em pó',
  98.                           'açúcar vermelho, médio',
  99.                           'açúcar azul, médio',
  100.                           'açúcar verde, médio',
  101.                           'açúcar amarelo, médio',
  102.                           'açúcar rosa, médio',
  103.                           'chocolate sprinkles',
  104.                           'arco-íris nonpareils',
  105.                           'flocos de coco'),
  106.               'es_ES'=>array(
  107.                           'Azúcar blanco, fino (incluido en el precio)',
  108.                           'Azúcar blanco, grueso',
  109.                           'Azúcar blanco, en polvo',
  110.                           'Azúcar rojo, medio',
  111.                           'Azúcar azul, medio',
  112.                           'Azúcar verde, medio',
  113.                           'Azúcar amarillo, medio',
  114.                           'Azúcar rosa, medio',
  115.                           'Chocolate rocía',
  116.                           'Arco iris nonpareils',
  117.                           'Coco rallado'
  118.                           )
  119.               );
  120. $res = mysql_query('select * from catalog_product_option_type_value');
  121. $i = 0;
  122. $j = count($prices)-1;
  123. while ($ret = mysql_fetch_array($res)) {
  124.     foreach($stores as $locale=>$scopes){
  125.         foreach($scopes as $scope){
  126.             $query = "insert into catalog_product_option_type_price (option_type_id,store_id,price,price_type) values ($ret[0],$scope,$prices[$i],'fixed')";
  127.             echo "$query<br>";
  128.             $res1 = mysql_query($query);
  129.             $query = "insert into catalog_product_option_type_title (option_type_id,store_id,title) values ($ret[0],$scope,'{$option_type_titles[$locale][$i]}')";
  130.             echo "$query<br>";
  131.             $res1 = mysql_query($query);
  132.         }
  133.     }
  134.     $i = ($j==$i) ? 0 : $i+1 ;
  135. }
  136. echo "<br>Populated catalog_product_option_type_price.<br>";
  137. echo "<br>Populated catalog_product_option_type_title.<br>";
  138. ?>
  139. </pre>
  140. </body>
  141. </html>



 

Magento 2 GitHub Repository

Magento Job Board - Some sort of tag line goes here

Latest Posts| View all Jobs