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 :) )

Data Scraping Part-2

Here is second article on data scrapping. As I have mentioned in my previous blog that I was working on data scraping from Asp.Net MVC website. In that I have faced one more problem.

There was a field on the page which is visible to only logged in user. So first of all I have to create Asp.Net session by PHP cURL then I can request for the particular page. So how to do that. Following is the procedure for that.

First of all create a session through PHP cURL and then use that cURL request to get the restricted page. Following is the code for that.

$url = $this->login_url;

$ch = curl_init();

curl_setopt($ch, CURLOPT_URL, $url);

curl_setopt ($ch, CURLOPT_POST, 1);

curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);

curl_setopt($ch, CURLOPT_USERAGENT ,'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.8) Gecko/20100722 Firefox/3.6.8');

curl_setopt($ch, CURLOPT_AUTOREFERER,1);

curl_setopt ($ch, CURLOPT_POSTFIELDS, 'userName='.urlencode($this->user).'&password='.urlencode($this->pass).'&rememberMe=true&rememberMe=false');

curl_setopt ($ch, CURLOPT_COOKIEJAR, 'cookie.txt');

curl_setopt ($ch, CURLOPT_COOKIEFILE, 'cookie.txt');

curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1);

curl_setopt ($ch, CURLOPT_FOLLOWLOCATION , 1);

In above code two things are new CURLOPT_USERAGENT ,CURLOPT_POSTFIELDS.

If you carefully see CURLOPT_POSTFIELDS there you can see some different data then normal post fields. Generally every login page has remember me options.

Another different thing is CURLOPT_USERAGENT. This is the information of browser and system from where the request is being sent.

How you can properly build these two fields? The option I have used is Live HTTP headers extension in Firefox. You can find it from this website http://livehttpheaders.mozdev.org/

Install it in your Firefox and then simply browse that the website for which you want to get the information. This extension will capture all the data of your browsed page. You can use it in your cURL request. Use it in above code and then use following code to get restricted information on the page.

curl_setopt($ch, CURLOPT_URL, $page_url);

curl_setopt ($ch, CURLOPT_POST, false);

$store = curl_exec ($ch);

curl_close ($ch);

Hope this helps.

Scrap data of Asp.Net MVC website using PHP cURL

Hello,

Last week I was working with data scraping project. I have to extract data of Asp.Net MVC website using PHP Curl. I guess everyone know about PHP cURL. Its library created by Daniel Stenberg, that allows you to connect and communicate to many different types of servers with many different types of protocols. More information you can get it from this link.

So basically following is the code snippet of how to use cURL with PHP for data scraping.

$ch = curl_init("http://www.example.com");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$pageData = curl_exec($ch);
curl_close($ch);

You will get complete page HTML+Data in $pageData variable. Now you can extract data from this variable using various PHP functions.

When you use cURL it is very easy to extract data of static page. What if we have dynamic page like Asp.Net page? For example you have a combo box on Asp.Net page so when you select some other option in combo box your Asp.Net page gets refreshed. The exact word is 'POST BACK'. Some code gets executed in code behind file of Asp.Net page and again page is constructed.

So how we can use PHP cURL for this. cURL supports various options for this like

curl_setopt($ch, CURLOPT_POSTFIELDS, $post_string);

Here you can give all the post fields like option selected in combo box. It will be & separated string. For Example

postfield1=value1&postfield2=value2&postfield3=value3

So you have to identify all the fields in Asp.Net page that gets posted. But what if you don't know anything about Asp.Net page. How you can identify all the necessary fields that gets posted on post back of Asp.Net page?

Well, I have used a tool called Fiddler web debugger. When you install Fiddler it will track all of your web sessions and it will give you all the data like headers, textview , WebForms , XML etc.
After installing Fiddler just browse through your Asp.Net website. Select various fields from combo box and do analysis of Fiddler data. You will get all the information about posted fields. Using this you can make your post string for cURL request and you can have all the data of Asp.Net website.