Magento:change product image from gallery image

This is some what different blog. While working on a Magento project I faced a little problem. I have uploaded products with image gallery and set product image,small image and thumbnail to last image of gallery images.

Now client told that I want first image to be displayed as main image. It was around 10,000+ products. So it was not possible to manually change all the products. So after wasting around two to three hours I got a solution.

If you carefully observe Magento database , it stores all the product related information in catalog_product_entity_varchar table. There you will fine rows with product images data. So what you have to do is you have to extract first image name from product images and update it to rest of the fields. Following is the query to get first image name from product gallery images.

SELECT entity_id,SUBSTRING( value, LOCATE( "/", value, 3 ) +1, IF( LOCATE( ";", SUBSTRING( value, LOCATE( "/", value, 3 ) +1 ) , 2 ) -1 <0, CHAR_LENGTH( value ) -15, LOCATE( ";", SUBSTRING( value, LOCATE( "/", value, 3 ) +1 ) , 2 ) -1 ) ) FROM catalog_product_entity_varchar WHERE `attribute_id` =83 and `entity_id` in (select entity_id from catalog_product_entity)

While using this query for your project you have to change this query because in my case following is the typical data of image gallery.

/productimages/67808_101_Y_3_8CT_A.jpg;/productimages/67808_101_Y_3_8CT_B.jpg;

/productimages/67808_101_Y_3_8CT_D.jpg;/productimages/67808_101_Y_3_8CT_E.jpg;

/productimages/67808_101_Y_3_8CT_Z.jpg;/productimages/67808_102P_W_3_8CT_A.jpg

So in query 15 is the character count of /productimages if you have some other path then use proper character count.

Also you don't need that If condition in query if your image gallery contain more than one image. In my case there were image galleries with only one image so I have to use that if condition.

Now lets move further. Using above query you will get first image name but still you have to update it. I found two ways to do it. First is using cursor and stored procedure to run this query and second is to use PHP script to write update query.

Hope this post helps you if you face same problem like me.

(Although it certainly worked for me, please take a backup of your table before trying this :) )