Some code recipes and useful scripts.

Later Ctrl + ↑

Add Admin User Via MySQL

If you don’t have access to store’s admin panel, but you have access to a database, you can easily add new Magento admin panel user via MySQL script.

Before you launch the next script, please, be sure your password is 8 symbols length and has at least 1 digit.

LOCK TABLES `admin_role` WRITE , `admin_user` WRITE;
SET @SALT = "mr";
SET @PASS = CONCAT(MD5(CONCAT( @SALT , "password") ), CONCAT(":", @SALT ));
SELECT @EXTRA := MAX(extra) FROM admin_user WHERE extra IS NOT NULL;
INSERT INTO `admin_user` (firstname,lastname,email,username,password,created,lognum,reload_acl_flag,is_active,extra,rp_token_created_at) 
VALUES ('First name','Last name','','username',@PASS,NOW(),0,0,1,@EXTRA,NOW());
INSERT INTO `admin_role` (parent_id,tree_level,sort_order,role_type,user_id,role_name) 
VALUES (1,2,0,'U',(SELECT user_id FROM admin_user WHERE username = 'username'),'First name');

Make Simple Products of Configurable “Not Visible Individually”

If you wan’t automatically make all simple products which are configurable options of configurable products to be hidden in catalog, you have to change their visibility to “Not Visible Individually”.

Next SQL-query will help you with that task.

`value` = 1
`attribute_id`=(SELECT `attribute_id` FROM `eav_attribute` WHERE `attribute_code` = 'visibility')
`entity_id` IN (SELECT `product_id` FROM `catalog_product_super_link`);
2015   configurable   product   simple

Change Customers Email Domains

Sometimes you need to work with a production’s DB dump on a staging or on a dev server. To avoid emails be sent to real customers you should replace their email addresses with safe values. We will use Mailinator service which receives all emails and allows to access them if you know full email address.

Just execute this SQL-query in your DB

UPDATE `customer_entity`
SET `email` = CONCAT(
	SUBSTRING(`email`, 1, locate('@', `email`)),

If you need to left your organization domain email addresses unchanged, just add such condition:

UPDATE `customer_entity`
SET `email` = CONCAT(
	SUBSTRING(`email`, 1, locate('@', `email`)),
WHERE  SUBSTRING(`email`, locate('@', `email`) + 1) != '';
2015   customer   email

Change Magento Product Images File Type

For instance, we need to convert all .gif to .jpg

Execute next commands in terminal

> cd /vaw/www/media/catalog/product/
> for i in $(find $(pwd) -name \*.gif -print); do convert $i $(echo $i.jpg | sed s/.gif//g); done
> rm -r cache/

Then, run these SQL-statements in DB

UPDATE `catalog_product_entity_varchar`
SET `value` = REPLACE(`value`, '.gif', '.jpg') 
WHERE `attribute_id` IN 
    (SELECT `attribute_id` FROM `eav_attribute`
        WHERE `attribute_code` IN ('image', 'small_image', 'thumbnail') AND `entity_type_id` = 4
UPDATE `catalog_product_entity_media_gallery`
SET `value` = REPLACE(`value`, '.gif', '.jpg');
2015   images   media   product