Products Grid: Adding a column displaying a product custom feature (editable)

Target

We would like to add a column displaying custom values of a specific feature in the products grid.

We will then be able to view and edit custom features for each product

Setup

To add the field to the list of available fields for your products grids, click on the in the 'Add a field' panel and enter the following information:

What is the field ID?: myfeature

SC creates the field, you now need to populate the grid with:

Field name: Author
Table: Another table
Type: Editable
Refresh combinations: No

From the Advanced Properties panel on the right handside:

- select the menu SQLSelect and enter:

return ' , (SELECT fvl_cus.value FROM `' . _DB_PREFIX_ . 'feature_product` fp1_cus 
                INNER JOIN `' . _DB_PREFIX_ . 'feature_value_lang` fvl_cus ON (fp1_cus.id_feature_value=fvl_cus.id_feature_value AND fvl_cus.id_lang='.(int)$id_lang.')
                WHERE fp1_cus.id_feature = "36" 
                AND fp1_cus.id_product = p.id_product LIMIT 1) as myfeature';

- select the menu PHP onAfterUpdateSQL and enter:

if (isset($_POST['myfeature'])) {

    $feature_value = Tools::getValue('myfeature');
    $sql = 'SELECT * FROM ' . _DB_PREFIX_ . 'feature_product WHERE id_product=' . (int)$idproduct . ' AND id_feature=36';
    $res = Db::getInstance()->executeS($sql);

    if ($res) {
        // Le produit a déjà une valeur pour cette caractéristique
        if ($feature_value) {
            // ====== MODIFICATION : on supprime l'ancienne et on crée une nouvelle ======

            // Vérifier si la valeur actuelle est custom
            $sql = 'SELECT custom FROM ' . _DB_PREFIX_ . 'feature_value WHERE id_feature_value=' . (int)$res[0]['id_feature_value'];
            $isCustom = Db::getInstance()->getValue($sql);

            // Supprimer l'association produit <-> valeur
            $sql = 'DELETE FROM ' . _DB_PREFIX_ . 'feature_product WHERE id_product=' . (int)$idproduct . ' AND id_feature=36';
            Db::getInstance()->execute($sql);

            // Si c'était une valeur custom, vérifier si elle est encore utilisée ailleurs
            if ($isCustom) {
                $sql = 'SELECT COUNT(*) FROM ' . _DB_PREFIX_ . 'feature_product WHERE id_feature_value=' . (int)$res[0]['id_feature_value'];
                $usageCount = Db::getInstance()->getValue($sql);

                // Si elle n'est plus utilisée, la supprimer
                if ($usageCount == 0) {
                    $sql = 'DELETE FROM ' . _DB_PREFIX_ . 'feature_value WHERE id_feature_value=' . (int)$res[0]['id_feature_value'];
                    Db::getInstance()->execute($sql);

                    $sql = 'DELETE FROM ' . _DB_PREFIX_ . 'feature_value_lang WHERE id_feature_value=' . (int)$res[0]['id_feature_value'];
                    Db::getInstance()->execute($sql);
                }
            }

            // Créer une NOUVELLE valeur custom
            $sql = 'INSERT INTO ' . _DB_PREFIX_ . 'feature_value (id_feature,custom) VALUES (36,1)';
            if (Db::getInstance()->execute($sql)) {
                $id_value = Db::getInstance()->Insert_ID();

                // Insérer pour toutes les langues actives
                $languages = Language::getLanguages(false);
                foreach ($languages as $lang) {
                    $sql = 'INSERT INTO ' . _DB_PREFIX_ . 'feature_value_lang (id_feature_value,id_lang,value)
                            VALUES (' . (int)$id_value . ',' . (int)$lang['id_lang'] . ',"' . pSQL($feature_value) . '")';
                    Db::getInstance()->execute($sql);
                }

                // Associer au produit
                $sql = 'INSERT INTO ' . _DB_PREFIX_ . 'feature_product (id_feature,id_product,id_feature_value)
                        VALUES (36,' . (int)$idproduct . ',' . (int)$id_value . ')';
                Db::getInstance()->execute($sql);
            }
        } else {
            // ====== SUPPRESSION : vérifier si la valeur custom est encore utilisée ======
            $sql = 'SELECT custom FROM ' . _DB_PREFIX_ . 'feature_value WHERE id_feature_value=' . (int)$res[0]['id_feature_value'];
            $isCustom = Db::getInstance()->getValue($sql);

            $sql = 'DELETE FROM ' . _DB_PREFIX_ . 'feature_product WHERE id_product=' . (int)$idproduct . ' AND id_feature=36';
            if (Db::getInstance()->execute($sql) && $isCustom) {
                // Vérifier si la valeur custom est encore utilisée ailleurs
                $sql = 'SELECT COUNT(*) FROM ' . _DB_PREFIX_ . 'feature_product WHERE id_feature_value=' . (int)$res[0]['id_feature_value'];
                $usageCount = Db::getInstance()->getValue($sql);

                if ($usageCount == 0) {
                    $sql = 'DELETE FROM ' . _DB_PREFIX_ . 'feature_value WHERE id_feature_value=' . (int)$res[0]['id_feature_value'];
                    if (Db::getInstance()->execute($sql)) {
                        $sql = 'DELETE FROM ' . _DB_PREFIX_ . 'feature_value_lang WHERE id_feature_value=' . (int)$res[0]['id_feature_value'];
                        Db::getInstance()->execute($sql);
                    }
                }
            }
        }
    } elseif ($feature_value) {
        // ====== CRÉATION : créer une nouvelle valeur custom ======
        $sql = 'INSERT INTO ' . _DB_PREFIX_ . 'feature_value (id_feature,custom) VALUES (36,1)';
        if (Db::getInstance()->execute($sql)) {
            $id_value = Db::getInstance()->Insert_ID();

            // Insérer pour toutes les langues actives
            $languages = Language::getLanguages(false);
            foreach ($languages as $lang) {
                $sql = 'INSERT INTO ' . _DB_PREFIX_ . 'feature_value_lang (id_feature_value,id_lang,value)
                        VALUES (' . (int)$id_value . ',' . (int)$lang['id_lang'] . ',"' . pSQL($feature_value) . '")';
                Db::getInstance()->execute($sql);
            }

            $sql = 'INSERT INTO ' . _DB_PREFIX_ . 'feature_product (id_feature,id_product,id_feature_value)
                    VALUES (36,' . (int)$idproduct . ',' . (int)$id_value . ')';
            Db::getInstance()->execute($sql);
        }
    }
}

In this instance, we use feature ID 36. You can change the feature by looking for its ID in the Features panel (SC Catalog>Features)

Exit the editing window.

The new field is now present in the list of available fields and you can add it to your product grids.