HOWTO Add Custom Product Options En-Masse

Last modified by Discovery on Fri, June 25, 2010 09:42
Source|Old Revisions  

This is an old revision of the document!


Introduction

Stock 1.1.7 Magento has very useful Custom Products Options. These can be used to collect ‘custom options’ such as text, e.g. to be engraved on a trophy, a drop-down, e.g. a colour preference or a check-box, e.g. for something-else. Custom Product Options are really for products that don’t need lots and lots of extra sku’s. To take the example of a T-shirt, the sizes and colours all need sku’s but the custom text does not...

The only slight down-side to stock Custom Product Options is that you cannot duplicate them all across a gazillion products, for instance all the T-shirts. Going into every product is tedious and can result in errors. With this worked example a script is used to do the business. You can modify this script to suit your needs.

Example Script Overview

In this T-shirt example the catalog was conveniently built with lengthy sku codes containing a little bit of category information. The sku’s are searched for anything with T-shirt in the sku code. This can be modified to be a brand, or you can write your own select to retrieve products. With a bit more coding all products from a category could be pulled up.

Since a lot of custom options were already setup on various products, this script checks to see if a product has options already. If it has then it does not do anything except print to the screen that the product already has them.

In this example the options were needed for an embroidered badge or a print. Since the print can be on the front or the back, and since the print can be in a variety of colours, mono, two colour and multi colour a total of seven tick boxes are added. An additional text box is added for generic text about size, location and other stuff. This gets brought through onto the invoice/cart.

This particular example is designed to work with another piece of code that hangs onto the the custom product options template looking for the word ‘Customise!’. If it spots that word then it shows an upload box so images can be uploaded. Allegedly this feature is to be done properly and made part of the product soon, hence this hack is not mentioned here.

This code can also be chopped down, so the polo-shirts that need to be done next can be much the same but with just the embroidered badge option. More elegant code would be nice, however, this script should make sense to those that have problems with php squiggles on the page.

It is highly recommended that you backup your store before altering the products en-masse, you can also run a cut down script that just shows you what products are to be modified.

Save the example script to a file, e.g. in a directory of your own such as /hack and then comment out the code that does stuff.

Example Script

/hack/cpo.php - point browser at domain.com/hack/cpo.php to run

  1. <html>
  2. <head>
  3. <title>Custom Product Options - T-shirt</title>
  4. </head>
  5. <body>
  6. <?php {
  7. // Database connection - hope you remembered your user, pass and db
  8.   $res = mysql_pconnect('localhost', 'USER', 'PASS);
  9.   mysql_select_db('MAGENTODB');
  10. // Product selector - here all skus with T-shirt are selected you may want to use one sku to test
  11.   $res = mysql_query('select entity_id from catalog_product_entity where type_id = 'configurable' and sku like '%T-shirt%'');
  12. // Flag to test selection, set to 1 for cpo's to be added, set to 0 if the products to be modified just need to be shown with no harm done...
  13.   $go_for_it=0;
  14.  
  15. // Go through all the products that match the selection
  16.   while ($o = mysql_fetch_array($res)) {
  17.     $res_o = mysql_query('select option_id from catalog_product_option where product_id='.$o['entity_id']);
  18.     $result = mysql_query('select sku from catalog_product_entity where entity_id='.$o['entity_id']);
  19. // Get the product images too - keep it visual
  20.     $image_omysql_query('select value from catalog_product_entity_media_gallery where entity_id='.$o['entity_id']);
  21.     $imagemysql_fetch_array($image_o);
  22.  
  23.     $row = mysql_fetch_assoc($result);
  24.     if (mysql_num_rows($res_o)) {
  25. // if there are already custom product options then do nothing and move on
  26.       echo '<img src="/media/catalog/product'.$image['value'].'"> '.$row['sku'].' ('.$o['entity_id'].') has options already<br />';
  27.       $catalog_product_optionmysql_query('select option_id, product_id, type, is_require, sku, max_characters, file_extension, sort_order from catalog_product_option where product_id='.$o['entity_id']);
  28.       $catalog_product_option_array = mysql_fetch_array($catalog_product_option);
  29.       echo 'table catalog_product_option values='.$catalog_product_option_array['option_id'].' '.$catalog_product_option_array['product_id'].'<br />';
  30.     }
  31.     else {
  32. // if there are no custom product options then do stuff
  33.       echo '<img src="/media/catalog/product'.$image['value'].'"> '.$row['sku'].' ('.$o['entity_id'].') is getting new custom product options<br />';
  34.  
  35. //Stuff happens - comment out to test selection then modify to suit needs
  36. if($go_for_it==1) {
  37. //Add text box and checkbox list
  38.  
  39.         mysql_query('insert into catalog_product_option (product_id, type, is_require, sort_order) values ('.$o['entity_id'].', "area", 0, "10")');
  40.         $area_id=mysql_insert_id();
  41.         mysql_query('insert into catalog_product_option (product_id, type, is_require, sort_order) values ('.$o['entity_id'].', "checkbox", 0, "0")');
  42.         $checkbox_id=mysql_insert_id();
  43.  
  44. //No price for the text box
  45.  
  46.         mysql_query('insert into catalog_product_option_price (option_id, store_id, price, price_type) values ('.$area_id.', "0", "0.00", "fixed")');
  47.  
  48. //Text for the text box and check box list
  49.  
  50.         mysql_query('insert into catalog_product_option_title (option_id, store_id, title) values ('.$area_id.', "0", "Size, location and other notes")');
  51.         mysql_query('insert into catalog_product_option_title (option_id, store_id, title) values ('.$checkbox_id.', "0", "Customise!")');
  52.  
  53. //Add check-box 1
  54.  
  55.         mysql_query('insert into catalog_product_option_type_value (option_id, sort_order) values ('.$checkbox_id.', "0")');
  56.         $option_type_id=mysql_insert_id();
  57.         mysql_query('insert into catalog_product_option_type_title (option_type_id, store_id, title) values ('.$option_type_id.', "0", "Embroidered badge")');
  58.         mysql_query('insert into catalog_product_option_type_price (option_type_id, store_id, price, price_type) values ('.$option_type_id.', "0", "3.0000", "fixed")');
  59.  
  60. //Add check-box 2
  61.  
  62.         mysql_query('insert into catalog_product_option_type_value (option_id, sort_order) values ('.$checkbox_id.', "1")');
  63.         $option_type_id=mysql_insert_id();
  64.         mysql_query('insert into catalog_product_option_type_title (option_type_id, store_id, title) values ('.$option_type_id.', "0", "Single colour print (front)")');
  65.         mysql_query('insert into catalog_product_option_type_price (option_type_id, store_id, price, price_type) values ('.$option_type_id.', "0", "3.0000", "fixed")');
  66.  
  67. //Add check-box 3
  68.  
  69.         mysql_query('insert into catalog_product_option_type_value (option_id, sort_order) values ('.$checkbox_id.', "2")');
  70.         $option_type_id=mysql_insert_id();
  71.         mysql_query('insert into catalog_product_option_type_title (option_type_id, store_id, title) values ('.$option_type_id.', "0", "Single colour print (back)")');
  72.         mysql_query('insert into catalog_product_option_type_price (option_type_id, store_id, price, price_type) values ('.$option_type_id.', "0", "3.0000", "fixed")');
  73.  
  74. //Add check-box 4
  75.  
  76.         mysql_query('insert into catalog_product_option_type_value (option_id, sort_order) values ('.$checkbox_id.', "3")');
  77.         $option_type_id=mysql_insert_id();
  78.         mysql_query('insert into catalog_product_option_type_title (option_type_id, store_id, title) values ('.$option_type_id.', "0", "Two colour print (front)")');
  79.         mysql_query('insert into catalog_product_option_type_price (option_type_id, store_id, price, price_type) values ('.$option_type_id.', "0", "4.0000", "fixed")');
  80.  
  81. //Add check-box 5
  82.  
  83.         mysql_query('insert into catalog_product_option_type_value (option_id, sort_order) values ('.$checkbox_id.', "4")');
  84.         $option_type_id=mysql_insert_id();
  85.         mysql_query('insert into catalog_product_option_type_title (option_type_id, store_id, title) values ('.$option_type_id.', "0", "Two colour print (back)")');
  86.         mysql_query('insert into catalog_product_option_type_price (option_type_id, store_id, price, price_type) values ('.$option_type_id.', "0", "4.0000", "fixed")');
  87.  
  88. //Add check-box 6
  89.  
  90.         mysql_query('insert into catalog_product_option_type_value (option_id, sort_order) values ('.$checkbox_id.', "5")');
  91.         $option_type_id=mysql_insert_id();
  92.         mysql_query('insert into catalog_product_option_type_title (option_type_id, store_id, title) values ('.$option_type_id.', "0", "Multi colour print (front)")');
  93.         mysql_query('insert into catalog_product_option_type_price (option_type_id, store_id, price, price_type) values ('.$option_type_id.', "0", "5.0000", "fixed")');
  94.  
  95. //Add check-box 7
  96.  
  97.         mysql_query('insert into catalog_product_option_type_value (option_id, sort_order) values ('.$checkbox_id.', "6")');
  98.         $option_type_id=mysql_insert_id();
  99.         mysql_query('insert into catalog_product_option_type_title (option_type_id, store_id, title) values ('.$option_type_id.', "0", "Multi colour print (back)")');
  100.         mysql_query('insert into catalog_product_option_type_price (option_type_id, store_id, price, price_type) values ('.$option_type_id.', "0", "5.0000", "fixed")');
  101.       }
  102. //End of does stuff
  103.     }
  104.     mysql_free_result($result);
  105.   }
  106. }
  107. ?>
  108. </body>
  109. </html>



 

Magento 2 GitHub Repository

Magento Job Board - Some sort of tag line goes here

Latest Posts| View all Jobs