1

I have a Magento Enterprise 1.14.2.1 multi-site and what I want is lets say I have two store views setup store "store_US" and "store_AU"

I want to run a php file that would get each sku, price and special price from the "store_US" and then multiple the price used in the store_us and save the new value to the price in the "store_au" for that sku. and then the same for the special price value of each sku.

I believe the table I would need to do the query on is the catalog_product_entity_decimal table. However not sure how to write the sql.

So far I have the below query which I believe will get the price of all products within a specific store id. However I am not sure how to add to this query to also get the sku of the item it is getting the price for.

$storeId = 4;
$sql = "select value from catalog_product_entity_decimal val 
        where val.attribute_id = 
            (
               SELECT attribute_id FROM eav_attribute eav
               WHERE eav.entity_type_id = 4 
               AND eav.attribute_code = 'price'
            )
        AND val.store_id = $storeId
        ";

I also tried the below query which I thought might give me the products sku and price

 select value from catalog_product_entity_decimal val 
        where val.attribute_id = 
            (
                SELECT attribute_id FROM eav_attribute eav
                WHERE eav.entity_type_id = 4 
                AND eav.attribute_code = 'price'
            )
        AND val.attribute_id = 
            (
                SELECT attribute_id FROM eav_attribute eav
                WHERE eav.entity_type_id = 4 
                AND eav.attribute_code = 'sku'
            )
        AND val.store_id = 4

however the second query also does not return anything

1 Answer 1

0

Ok so I figured out the answer to my own question :)

For anyone else needing to do this the way is the following: 1. enable "Use Flat Catalog Category" at admin > System > Configuration > CATALOG > Catalog > Frontend 2. run a full re-index via ssh 3. set indexers to Update when scheduled at admin > System > Configuration > ADVANCED > Index Management > Index Management Options 4. in ssh check that crontab is set like below:

          • sh /YOUR_MAGENTO_ROOT_DIRECTORY/cron.sh

Now I could just query the table catalog_product_flat_1 where the #1 represents the store id I want to edit the pricing of a sku for. The catalog_product_flat_1 table will have a sku, price special_price column

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.