Insert Custom Option values via SQL?

Question

I have a product with 8 custom options. Each custom option is a range value in increments, meaning there are ~180 values per option (8 * 180 values to input manually!). I'd really like to insert these in via an SQL query.

Is this possible without messing up Magento, and does anyone have some example SQL to hand? I don't need to create the custom option itself, only add additional values.

Answer 1

Avoid SQL queries. Instead, you could use the model that Magento uses in the SOAP API.

The SOAP calls to product_custom_option.* do call the model Mage_Catalog_Model_Product_Option_Api.

You can have a look at the Mage_Catalog_Model_Product_Option_Api methods

/**
 * Add custom option to product
 *
 * @param string $productId
 * @param array $data
 * @param int|string|null $store
 * @return bool $isAdded
 */
public function add($productId, $data, $store = null)
{ /* ... */ }

and

/**
 * Update product custom option data
 *
 * @param string $optionId
 * @param array $data
 * @param int|string|null $store
 * @return bool
 */
public function update($optionId, $data, $store = null)
{ /* ... */ }

and use the documentation linked above to see how the data array has to be constructed for your options.

So you should be able to do something like this (sorry, can't test this at the moment):

$model = Mage::getModel('catalog/product_option_api');

$selectOptionId = 123; // You need to get the options ids before e.g. using Mage_Catalog_Model_Product_Option_Api::items()
$selectOptionValueId = 1234; // If you had option values before, you have to get the value ids for the option before e.g. using Mage_Catalog_Model_Product_Option_Api::info()

$customDropdownOption = array(
    "title" => "Custom Dropdown Option Title",
    "type" => "drop_down",
    "additional_fields" => array(
        array(
            "value_id" => $selectOptionValueId,
            "price" => 14.00,
            "price_type" => 'percent',
            "sku" => "custom_select_option_sku_1",
            "sort_order" => 26
        )
    )
);

$model->update($selectOptionId, $customDropdownOption);

Please let me know if this works.