To create a support ticket, start a search

Specific Prices (window): Adding a field from a table outside the specific prices table


Prerequisites

The field needs to exist in your database

 

Target

We would like to add the field "My field" which is located in the table ps_ma_table (this table does not exist in PrestaShop by default).

We will then be able to:

  • use filters to optimize the selection of specific prices
  • mass-edit this field using Store Commander's copy/paste option
  • rapidly export the specific prices panel with this field

 

Setup

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

What is the field ID? : my_field (should be exactly the name as it is in the database).
 

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

Field name: My field
Table: special
Type: multiple choice (should corresponds to the type of your field, here with preset values)

 

In this example, the fields are in table ps_my_table:

id_of_my_table, id_product, mon_champ

 

From the Advanced Properties panel on the right handside:

- select the menu List of choices (if field type is 'multiple choices, otherwise leave blank) and enter:

return array(0=>_l('Aucune'), 1=>_l('Valeur 1'), 2=>_l('Valeur 2'));

 

- select the menu SQL Select and enter:

return ' ,mt.mon_champ';

 

- select the menu SQL Left Join and enter:

return " LEFT JOIN "._DB_PREFIX_."ma_table  mt  ON (mt.id_product= p.id_product)";

 

- select the menu PHP onAfterUpdateSQL and enter: 

if (isset($_POST["mon_champ"])) {
    $sql = "SELECT * FROM " . _DB_PREFIX_ . "ma_table WHERE id_product=" . (int)$id_product;
    $res = Db::getInstance()->ExecuteS($sql);
    $mon_champ = (int)Tools::getValue('mon_champ', 0);
    if (count($res)) {
        if ($mon_champ) {
            $sql = "UPDATE " . _DB_PREFIX_ . "ma_table  SET mon_champ=" . (int)$mon_champ . "  WHERE id_product=" . (int)$id_product;
            Db::getInstance()->Execute($sql);
        } else {
            $sql = "DELETE FROM " . _DB_PREFIX_ . "ma_table  WHERE id_product=" . (int)$id_product;
            Db::getInstance()->Execute($sql);
        }
    } else {
        $sql = "INSERT INTO " . _DB_PREFIX_ . "ma_table  (id_product,mon_champ) VALUES (" . (int)$id_product . "," . pSQL($mon_champ) . ")";
        Db::getInstance()->Execute($sql);
    }
}

 

You will need to adapt this code to your needs.

Exit the editing window.

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