The Poetics Of Coding

If you want to update custom option in bulk easy way can be using SQL

If you have a custom option with Title “Extended Warranty
with price options
1 year – $20
2 years – $30

you can do this with three magento tables
1. Want to update custom option title make a query on table “catalog_product_option_title”
2. Want to update price title make a query on table “catalog_product_option_type_title”
3. Want to update price make a query on table “catalog_product_option_type_price”

The simplest query is

UPDATE Tbl_Name set field_name = TRIM(Replace(Replace(Replace(field_name,'\t',''),'\n',''),'\r',''));

Replace category_id_here with you your category id.

SELECT p.entity_id AS prod,
p.sku AS sku,
cp.category_id AS cp_category_id,
cp.`position` AS cp_position,
c.`entity_id` AS c_category_id,
cat_varchar.value AS CatName,
c.`entity_type_id` AS c_entity_type_id,
c.`attribute_set_id` AS c_attribute_set_id,
c.`parent_id` AS c_parent_id,
c.`path` AS c_path,
c.`position` AS c_position,
c.`level` AS c_level,
c.`children_count` AS c_children_count
FROM catalog_product_entity AS p
LEFT JOIN catalog_category_product AS cp
ON p.entity_id = cp.product_id
LEFT JOIN catalog_category_entity AS c
ON cp.category_id = c.entity_id
LEFT JOIN catalog_category_entity_varchar AS cat_varchar
ON     c.entity_id = cat_varchar.entity_id
AND cat_varchar.attribute_id = 111
LEFT JOIN catalog_product_entity_varchar AS prod_varchar
ON     p.entity_id = prod_varchar.entity_id
AND prod_varchar.attribute_id = 96
AND c.entity_id = category_id_here

I was working to a project and we import more than 90k products, suddenly client asked to update SKU format for 21k products.

I search for the solution on internet and find many but duplicate solutions, infact it was a single solution and everyone was copying. When i check this solution i realised that this script is not for me, as it first check the SKU in database and after that update and save, so 21k product means 52k calls to DB ooooooooooooohhhhhhh

Now check my solution.

1. Create CSV with 2 column one for old sku and other for new sku

2. Create a temp table in your magento DB, lets say it’s name is sku_tmp

3. Import your CSV to this table, using your favorit DB tool (phpmyadmin…. i used sqlyog)

4. Now run this query “UPDATE catalog_product_entity AS cpe, sku_temp AS tmp SET cpe.sku = tmp.sku2  WHERE  cpe.sku = tmp.sku ”

Hurrah…………. 21k rows updated in just seconds……..


On my local it need to update in windows so i just reinstall it, it removes the wamp server too. I just download the new version and install it.

With this new installation phpmyadmin version and mysql version updated too. So like i did in past i just copy the database directory and paste in new version but problem was

1. Database with all table and table with all columns was in left side

2. Onclick in left side it shows error #1146 table doesn’t exist, strange.

after a lot of search and reading mysql documentation i come to realise that there are three other files out of database directory

1. ibdata1

2. ib_logfile0, ib_logfile01

so need to replace these 3 files too in your new version of database and problem will be solved.

No thanks as i am great while helping others……….. :)

In last days i was working on dreamhost dedicated server, upload a zip file now realize that server panel not support unzip functionality,

So option was to use shell command to unzip, but again problem was with permission and many other.

So i come across a solution, a real easy solution, a php exec command.

<?php exec("unzip"); ?>

you can change myfile to name of your zip file. that’s it

It is really difficult to transfer a site from one host to other, reason can be you are not satisfied with current host or whatever.

Let discuss the steps.

1. You hv to zip the current file

if you are on linex cpanel it is very easy, just select all files by clicking on “select all” and than click on “compress” and give it a proper name like “”, this process will not take more than few seconds if your site is less than 100MB

how to zip whole site on cpanel

how to zip whole site on cpanel

If you are not on cpanel than it will be little difficult, but let me make easy for you

Please click here

Now you should be able to access this zip file like this  “; in browser, if browser start download this zip file everything is OK, just cancel it

2. Goto destination host now

now to open cpanel or ftp of destination host, where you want to upload your current site. But first make a file with name “copysite.php” with this script

$errors= error_get_last();
echo "COPY ERROR: ".$errors['type'];
echo "<br />\n".$errors['message'];
} else {
echo "File copied from remote!";

run this script file in your browser like this “; and script will generate a zip file and will start coping you old site data. After getting message “File copied from remote!” extract this file from your cpanel.
Enjoy, no thanks as i am great while helping others.


Get every new post delivered to your Inbox.

%d bloggers like this: