Magento get option id for specified attribute set and value

Magento get option id for specified attribute set and value

July 10, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento get option id for specified attribute set and value

Retrieving Magento Option ID by Attribute Set and Value

As a senior developer at Laravel Company, I understand how challenging it can be to navigate the complex database structure of Magento. In this post, we’ll walk through a practical solution to retrieve an option ID using the SKU, attribute set ID, and option label.

Understanding the Magento Database Structure

Magento stores its product options in several tables:

  1. catalog_product_entity: Contains basic product information.
  2. eav_attribute_option_value (as you mentioned): Stores the actual option values.
  3. catalog_product_super_link: Connects simple products to configurable ones.

The key table for our use case is eav_attribute_option_value, which maps an option value ID (value_id) to its corresponding option ID (option_id).

Retrieving the Option ID

To fetch the option ID, we’ll need to query these tables. Here’s a step-by-step guide:

Step 1: Get the Product ID from the SKU

First, let’s find the product ID using the provided SKU.

$sku = 'Your-SKU-Here';
$productId = $connection->fetchOne("SELECT entity_id FROM catalog_product_entity WHERE sku = ?", [$sku]);

Step 2: Fetch the Configurable Product Options

Next, we need to retrieve all options for our configurable product.

$options = [];
$sql = "SELECT
            op.value as option_label,
            ov.value_id,
            ov.option_id
         FROM
            catalog_product_super_link pls
         JOIN
            eav_attribute_option_value ov ON pls.product_id = ov.option_id
         JOIN
            eav_attribute_option o ON ov.option_id = o.option_id
         WHERE
            pls.parent_id = ?";

$results = $connection->fetchAll($sql, [$productId]);

foreach ($results as $result) {
    $options[$result['option_id']] = $result['option_label'];
}

Step 3: Find the Option ID by Matching the Attribute Set and Label

Finally, we match the provided attribute set ID and option label to find the desired option_id.

$attributeSetId = 'Your-Attribute-Set-ID-Here';
$optionLabel = 'Rose';

foreach ($options as $optionId => $label) {
    if (strpos($label, $optionLabel) !== false && in_array($attributeSetId, array_column($results, 'store_id'))) {
        echo "Option ID: {$optionId}";
        break;
    }
}

Handling Multilingual Options

As you experienced with the locale switch affecting the option ID, Magento handles multilingual options by store view. When switching locales, it’s important to ensure your query uses the correct store_id for the desired language.

You can fetch the current store ID using:

$currentStoreId = Mage::app()->getStore()->getId();

Then use this in your SQL query:

WHERE
    pls.parent_id = ?
AND
    ov.store_id = ?;

Conclusion

Retrieving an option ID from Magento requires a solid understanding of its database structure and relationships. By following the steps outlined above, you should be able to accurately fetch the desired option_id using the SKU, attribute set ID, and option label.

At Laravel Company, we’ve found that understanding these core concepts is key to building efficient e-commerce solutions with Magento. For more in-depth guides on Magento development and best practices, feel free to explore our developers’ blog: Laravel Company Blog.

Happy coding, and remember—when dealing with Magento, there’s always a logical solution hidden among the many tables!

Catalog Database Configuration

Our network