Magento Forum

2 SQL Tricks to Auto-Organize your Images In Magento
 
transio
Jr. Member
 
Total Posts:  1
Joined:  2009-10-24
 

If you imported a lot of data into your Magento installation, and your images got disorganized in the process, here are a couple SQL scripts that may help you!

Trick 1 - Auto-set default base, thumb, small image to first image.
If you have multiple images per product from your import, Magento might have set the LAST one as the default base, small, and thumb image for all of your products!  This can be an issue if you want the FIRST image to be the default!

This script updates your images and makes the FIRST image (image order 1) your default BASE, SMALL, and THUMB image for ALL products in your database.  Can save you lots of hours if you were planning on doing this task manually!

Careful if you run this!  Getting your image order set back to how it was won’t be so easy!

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 (707172)
    AND 
mgv.position 1;

Trick 2 - Auto-enable thumbs for multi-image and auto-disable for single-image products

Ok, so you imported 5,000 new products into your Magento store, and some have 1 product image, and the rest have 2+ images.  If you’re like me, you want the multi-image products to have thumbnails enabled on the product page, and if you only have 1 image, you want no thumbnails (kind of silly to switch between an image and itself LOL). 

So this script runs 2 updates.  The first enables all images for thumbnails, then the second disables any thumbnails for images associated to products having only a total count of 1image.

UPDATE catalog_product_entity_media_gallery_value SET disabled 0
UPDATE catalog_product_entity_media_gallery_value AS mgv,
    (
SELECT entity_idCOUNT(*) as image_countMAX(value_id) AS value_id
    FROM catalog_product_entity_media_gallery 
AS mg
    GROUP BY entity_id
    HAVING image_count 
1) AS mg
SET mgv
.disabled 1
WHERE mgv
.value_id mg.value_id

Enjoy!

Steve - President, Transio Web Development

 
Magento Community Magento Community
Magento Community
Magento Community
 
Discovery
Enthusiast
 
Total Posts:  767
Joined:  2007-12-29
 

That is awesome! Probably the best tip I have came across on here in ages, particularly part #2!

Thanking-you so much for sharing, and welcome to the Magento community (posts=1...)

 
Magento Community Magento Community
Magento Community
Magento Community
 
astanovitsia
Jr. Member
 
Total Posts:  15
Joined:  2009-12-14
 

What i must write, if i want enable thumbs only for second and other images. First image must be disable in thumbs for products with two and more images.  Thank you!

 
Magento Community Magento Community
Magento Community
Magento Community
 
cjagasken
Jr. Member
 
Total Posts:  1
Joined:  2009-11-15
 

great tips there - thanks!

I’ve got a similar problem after importing images, which i need a fix for…

I’ve imported about 1000 images/products from an oscommerce store, and the image filenames include capital letters and spaces. This means that when i try and add the products to google base they fail, because of the image url link will not accept spaces or capital letters. I can manually fix the problem, by manually changing every filename, but NEED ANOTHER WAY.

Please help!

 
Magento Community Magento Community
Magento Community
Magento Community
 
Bing Dicklepuss
Sr. Member
 
Total Posts:  91
Joined:  2010-07-26
 

cjagasken

Try this:
http://download.cnet.com/FastStone-Photo-Resizer/3000-2192_4-10319476.html?tag=mncol

I haven’t had your particular problem but I have used the above program for resizing images and concurrently renaming the image files to indicate their size (i.e. appending a “_200px” to the end of each base filename).

in the lower-right of my version (2.9) there is a radio button selection for “use filename template” and “search and replace”.
It doesn’t seem like “search and replace” recognizes spaces, but maybe the “use filename template” function will work for you.

@transio
I ran your queries but they are not removing the “exclude” option or activating “small image” and “thumbnail” for my files.

 
Magento Community Magento Community
Magento Community
Magento Community
 
gggggggg
Member
 
Total Posts:  69
Joined:  2010-05-07
 

This is close to what I need.  Does anyone know how to exclude the First image from showing a thumb, but all subsequent images show a thumb?

thanks
Greg

 
Magento Community Magento Community
Magento Community
Magento Community
 
gggggggg
Member
 
Total Posts:  69
Joined:  2010-05-07
 

gnore that, I got it.

mysql_query("UPDATE catalog_product_entity_media_gallery_value SET disabled = ‘1’ WHERE position = ‘1’") or die(mysql_error());

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top