13

I have a Magento store that has around 3,000 products. Almost all of these products have a single image attached to it.

For some reason, even though I set the small image and thumbnail image as the same as the base image in the import CSV file, only the base image is set for each product. This means that when you search for a product you get a placeholder - but once you go into the product page you get the correct image. This can be easily remedied by going into the product admin page and selecting the boxes for small image and thumbnail.

The problem is, with 3,000 images this would take quite a long time to do manually. I have found a SQL command that should make all base, small and thumbnail images map the the first image for each product. As I only have one image for each product this should be perfect. However, it doesn't do anything. It says 0 rows changed.

UPDATE catalog_product_entity_media_gallery AS mg,
catalog_product_entity_media_gallery_value AS mgv,
catalog_product_entity_varchar AS ev
SET ev.value = mg.value
WHERE  mg.value_id = mgv.value_id
AND mg.entity_id = ev.entity_id
AND ev.attribute_id IN (70, 71, 72)
AND mgv.position = 1

Does anyone know why this isn't working?

Thanks,

Danny

dannymcc
  • 3,744
  • 12
  • 52
  • 85

6 Answers6

24

Just as a warning to anyone (like myself!) who wants to try out this script. I ran this without thinking and it changed all the product names!

  1. Go into your attributes panel, find the image/small image/thumbnail attributes.
  2. Note down the ids (mine in this case were 85,86 and 87)
  3. Change the query to reflect those id's.

So my query looks like:

UPDATE catalog_product_entity_media_gallery AS mg,
       catalog_product_entity_media_gallery_value AS mgv,
       catalog_product_entity_varchar AS ev
SET ev.value = mg.value
WHERE  mg.value_id = mgv.value_id
AND mg.entity_id = ev.entity_id
AND ev.attribute_id IN (85,86,87)
AND mgv.position = 1;
nhahtdh
  • 55,989
  • 15
  • 126
  • 162
Thomas Harding
  • 394
  • 3
  • 9
  • Yeah I just did this. Good save for anyone who finds this post FIRST :). – Jared Eitnier Sep 19 '12 at 14:53
  • This is the real answer and should me marked as such. Thank you! – KPheasey Mar 20 '14 at 15:22
  • This solution is working but now it is showing same image as thumbnail in cart, mini cart and checkout page. In product detail page it is showing correct image. Please help me to resolve this issue – Krupal Oct 02 '17 at 13:27
19

After making a change like that to the database, even if successful, you would need to rebuild the images cache in Cache Management.

You might be able to do it with a script like this and not worry about caching or indexing.

<?php

require 'app/Mage.php';
Mage::app();

$products = Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect('*');
foreach ($products as $product) {
    if (!$product->hasImage()) continue;
    if (!$product->hasSmallImage()) $product->setSmallImage($product->getImage());
    if (!$product->hasThumbnail()) $product->setThumbnail($product->getImage());
    $product->save();
}

?>Done!

Save this in your Magento directory and access it by typing the URL into your browser's address bar. I haven't tested this script yet.

clockworkgeek
  • 37,650
  • 9
  • 89
  • 127
  • i am sorry, when i test your code,it can't work. why. i add echo 'down!'; after your code. it does't output it. – user1188320 May 31 '12 at 05:37
  • 1
    @user1188320 Make sure you have [display_errors on](http://www.php.net/manual/en/errorfunc.configuration.php#ini.display-errors) so you can see what has gone wrong. If it says out of memory then increase the [memory_limit](http://www.php.net/manual/en/ini.core.php#ini.memory-limit). – clockworkgeek Jun 01 '12 at 11:42
  • same issue. i did it same thing but i am getting empty page?@clockworkgeek – papa.ramu Nov 24 '14 at 07:09
  • @papa.ramu If you've increased `memory_limit` and `max_execution_time` and there is still a problem then perhaps you can open a new question based on your different experience. – clockworkgeek Nov 24 '14 at 11:20
  • 2
    Incase you can't change 'memory_limit' simplest solution is limit your collection by adding "->setPageSize(2000)->setCurPage(1);" at last of product query. And by changing the setcurpage to 2 and execute the code again and so on. You can adjust page size till the time you get rid of error. – Abhishek Sachan Mar 09 '16 at 15:08
2

i know this is an old post, however in case anyone has the same issue then the problem was with the ev.attribute_id. Updated code below:

UPDATE 
    catalog_product_entity_media_gallery AS mg,
    catalog_product_entity_media_gallery_value AS mgv,
    catalog_product_entity_varchar AS ev
SET 
    ev.value = mg.value
WHERE  
    mg.value_id = mgv.value_id
      AND mg.entity_id = ev.entity_id
      AND ev.attribute_id IN (74, 75, 76)
      AND mgv.position = 1

Thanks.

SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
Joel
  • 21
  • 1
1

I used stereo_world's method on magento 1.7.0.2 and it worked great. (I can't upvote his answer because I'm new to stackoverflow)

I don't know where he is seeing the attribute id in the attribute panel, because I don't see a numerical id there at all. I found the ids (85,86,87) by opening up phpMyAdmin and looking in eav_attribute table.

As clockworkgeek pointed out - reindex/cache flush are necessary.

user2321249
  • 11
  • 1
  • I found the attribute ID in the URL of the manage attribute page. So for example `index.php/admin/catalog_product_attribute/edit/attribute_id/85/key/c2069ce458b67f8598a3660bf2c8edf6/` (the id coming after attribute_id) – Thomas Harding Jul 22 '16 at 10:28
0

@user2321249 To find the attribute id in CE 1.9.1, go to the attribute information page and look at the URL. For example, from the Admin backend, select Catalog->Manage Attributes. Find the thumbnail attribute and select it. With my system the URL is:

www.example.com/magento/index.php/admin/catalog_product_attribute/edit/attribute_id/87/key/f759b57c21a7c75f33095a243f44b2a5/

You can easily tell the thumbnail attribute_id in my system is 87. Do the same for the Base Image and Small Image.

tzvi
  • 471
  • 3
  • 5
  • 19
0

I got it working using the following trick for Magento 1.9.2.2 version:

INSERT INTO catalog_product_entity_varchar
      (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT
      entity_type_id, 75, store_id, entity_id, value
FROM
      catalog_product_entity_varchar
WHERE 
      attribute_id = 74

Then replace value 75 with 76 and import the query again. Be sure to replace the attribute id values to your own

Ray
  • 11
  • 2