goorin-color-filter-mapping.php

<?php

$data = [
[112, 'BLA'],
[112, 'BLK'],
[113, 'blu'],
[113, 'BLU'],
[113, 'DNVY'],
[113, 'MAU'],
[113, 'NAV'],
[113, 'nvy'],
[113, 'NVY'],
[113, 'ROY'],
[113, 'TEA'],
[114, 'bro'],
[114, 'BRO'],
[114, 'BRW'],
[114, 'CML'],
[114, 'COG'],
[114, 'DBRO'],
[114, 'RUS'],
[114, 'WKY'],
[117, 'FOR'],
[117, 'GRE'],
[117, 'GRN'],
[117, 'MIN'],
[117, 'MOS'],
[117, 'MOSS'],
[117, 'OLI'],
[117, 'OLV'],
[117, 'SAG'],
[117, 'SAGE'],
[117, 'SEA'],
[117, 'TUR'],
[118, 'CHA'],
[118, 'DGRY'],
[118, 'GRR'],
[118, 'GRY'],
[118, 'gry'],
[118, 'LGRY'],
[118, 'SIL'],
[118, 'SLA'],
[119, 'AST'],
[119, 'CAM'],
[119, 'MLTI'],
[119, 'MULTI'],
[119, 'NON'],
[120, 'CAR'],
[120, 'CML'],
[120, 'KHA'],
[120, 'KHI'],
[120, 'NAT'],
[120, 'OAT'],
[120, 'PUT'],
[120, 'TAN'],
[120, 'TAU'],
[121, 'ORA'],
[122, 'COR'],
[122, 'PEA'],
[122, 'PIN'],
[122, 'PNK'],
[123, 'BLK'],
[123, 'CRA'],
[123, 'EGG'],
[123, 'LIL'],
[123, 'PLU'],
[123, 'PUR'],
[124, 'BUR'],
[124, 'CRE'],
[124, 'MAR'],
[124, 'RED'],
[124, 'ROS'],
[124, 'WIN'],
[125, 'IVO'],
[125, 'STO'],
[125, 'WHI'],
[125, 'WHT'],
[126, 'GLD'],
[126, 'GOLD'],
[126, 'HNY'],
[126, 'MUS'],
[126, 'MUST'],
[126, 'YEL'],
[225, 'ANT'],
];

$result = [];

foreach ($data as $a) {
    if (!isset($result)) {
        $result[$a[0]] = [$a[1]];
    } else {
        $result[$a[0]][] = $a[1];
    }
}

var_export($result);

goorin-color-filter-mapping.sql

select  /* cpei.entity_id, cpe.sku, */ cpei.value, cpemg.value
from mage_catalog_product_entity_int cpei
inner join mage_catalog_product_entity cpe on cpei.entity_id = cpe.entity_id
inner join mage_eav_attribute ea on cpei.attribute_id = ea.attribute_id
inner join mage_catalog_product_entity_media_gallery cpemg on cpei.entity_id = cpemg.entity_id
where ea.attribute_code = 'color_filter'
and cpei.value > 0
-- and cpei.value = 117
-- and cpemg.value like "%BRO%"
and cpe.sku like "%-%-%"
group by cpei.value, cpemg.value;