SQL 将所有商店图像设为 Magento 中的基本图像、小图像和缩略图图像？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4349592/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Make all store images the base, small and thumbnail images in Magento?
提问by dannymcc
I have a Magento store that has around 3,000 products. Almost all of these products have a single image attached to it.
我有一家 Magento 商店，里面有大约 3,000 种产品。几乎所有这些产品都附有一张图片。
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.
出于某种原因，即使我将小图像和缩略图设置为与导入 CSV 文件中的基本图像相同，但仅为每个产品设置了基本图像。这意味着当您搜索产品时，您会得到一个占位符 - 但一旦您进入产品页面，您就会得到正确的图像。这可以通过进入产品管理页面并选择小图像和缩略图框来轻松解决。
The problem is, with 3,000 images this would take quite a long time to do manually. I have found a SQL command that shouldmake 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.
问题是，对于 3,000 张图像，手动操作需要很长时间。我发现了一个SQL命令应使所有的基地，小缩略图映射每个产品的第一张图像。因为我每个产品只有一张图片，所以这应该是完美的。但是，它没有任何作用。它说 0 行已更改。
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
丹尼
回答by Thomas Harding
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!
就像对想要尝试此脚本的任何人（如我自己！）的警告一样。我不假思索地运行了这个，它改变了所有的产品名称！
- Go into your attributes panel, find the image/small image/thumbnail attributes.
- Note down the ids (mine in this case were 85,86 and 87)
- Change the query to reflect those id's.
- 进入您的属性面板，找到图像/小图像/缩略图属性。
- 记下 ID（在这种情况下，我的是 85,86 和 87）
- 更改查询以反映这些 ID。
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;
回答by clockworkgeek
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.
将其保存在您的 Magento 目录中，然后通过在浏览器的地址栏中键入 URL 来访问它。我还没有测试过这个脚本。
回答by Joel
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:
我知道这是一篇旧帖子，但是如果有人遇到同样的问题，那么问题出在 ev.attribute_id 上。更新代码如下：
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.
谢谢。
回答by user2321249
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)
我在 magento 1.7.0.2 上使用了stereo_world 的方法，效果很好。（我不能赞成他的回答，因为我是 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.
我不知道他在哪里看到属性面板中的属性 id，因为我根本没有在那里看到数字 id。我通过打开 phpMyAdmin 并查看 eav_attribute 表找到了 ids (85,86,87)。
As clockworkgeek pointed out - reindex/cache flush are necessary.
正如clockworkgeek所指出的那样-重新索引/缓存刷新是必要的。
回答by tzvi
@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:
@user2321249 要在 CE 1.9.1 中查找属性 id，请转到属性信息页面并查看 URL。例如，从 Admin 后端，选择 Catalog->Manage Attributes。找到缩略图属性并选择它。在我的系统中，URL 是：
www.example.com/magento/index.php/admin/catalog_product_attribute/edit/attribute_id/87/key/f759b57c21a7c75f33095a243f44b2a5/
www.example.com/magento/index.php/admin/catalog_product_attribute/edit/ attribute_id / 87/键/ f759b57c21a7c75f33095a243f44b2a5 /
You can easily tell the thumbnail attribute_id in my system is 87. Do the same for the Base Image and Small Image.
你可以很容易地告诉我的系统中的缩略图attribute_id是87。对Base Image和Small Image做同样的事情。
回答by Ray
I got it working using the following trick for Magento 1.9.2.2 version:
我使用以下技巧让它在 Magento 1.9.2.2 版本中工作：
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
然后将值 75 替换为 76 并再次导入查询。请务必将属性 id 值替换为您自己的值
