Magento: Add admin user with MySQL statement in the database

4. April 2016 at 11:34

sql-statement-mysql

This script quickly adds a Magento Admin user directly into the database. It is possible to run this script from the command line or by copying and pasting into phpMyAdmin. Just make sure to edit the following fields with your personalized data and import. Most of these fields are trivial, I’m just listing them so you don’t miss anything.

Set the salt portion of your password. You’ll rarely need to change this. If you do, just use two lower case letters of your choice.
Set your password. At least 8 characters in length and at least one digit.
Firstname: Enter admin’s first name.
Lastname: Enter admin’s last name.
Enter email of admin user.
Enter username where ‘myuser’ is set. Notice ‘myuser’ shows up in two places.
Enter Firstname again. This is more symbolic to label the rule.

LOCK TABLES `admin_role` WRITE , `admin_user` WRITE;
SET @SALT = "rp";
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 ('Firstname','Lastname','email@example.com','myuser',@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 = 'myuser'),'Firstname');
UNLOCK TABLES;

Please share this article von facebook & google plus or where you want, thank you!

GD Star Rating
loading...
GD Star Rating
loading...

Magento: A foreign key constraint fails when reindex – Solution

30. März 2016 at 14:08

magento-sql-query-to-delete-the-categories

Just execute the query in the database.

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_link_type`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_enabled_index`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_category_product_index`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
TRUNCATE TABLE `cataloginventory_stock`;
INSERT INTO `catalog_product_link_type`(`link_type_id`,`code`) VALUES (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell');
INSERT INTO `catalog_product_link_attribute`(`product_link_attribute_id`,`link_type_id`,`product_link_attribute_code`,`data_type`) VALUES (1,2,'qty','decimal'),(2,1,'position','int'),(3,4,'position','int'),(4,5,'position','int'),(6,1,'qty','decimal'),(7,3,'position','int'),(8,3,'qty','decimal');
INSERT INTO `cataloginventory_stock`(`stock_id`,`stock_name`) VALUES (1,'Default');
TRUNCATE TABLE `catalog_product_entity`;
SET FOREIGN_KEY_CHECKS = 1;

Please share this article von facebook & google plus or where you want, thank you!

GD Star Rating
loading...
GD Star Rating
loading...

Magento: Delete all categories with sql

23. März 2016 at 10:44

magento-sql-query-to-delete-the-categories

To delete all categories in Magento about sql you can use the following query.

If you has more than 3 stores, you must extend the entry „catalog_product_flat_1;“ with your store-id, e.g.:

DELETE FROM catalog_product_flat_3;
DELETE FROM catalog_product_flat_4;

and so on …

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `catalog_category_entity`;
TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_link_type`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_enabled_index`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_product_entity`;
TRUNCATE TABLE `cataloginventory_stock`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_type`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_entity`;
TRUNCATE TABLE `cataloginventory_stock`;
TRUNCATE TABLE `catalog_category_product`;
DELETE FROM catalog_product_flat_1;
DELETE FROM catalog_product_flat_2;
DELETE FROM catalog_product_flat_3;
SET FOREIGN_KEY_CHECKS = 1;
insert  into `catalog_product_link_type`(`link_type_id`,`code`) values (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell');
insert  into `catalog_product_link_attribute`(`product_link_attribute_id`,`link_type_id`,`product_link_attribute_code`,`data_type`) values (1,2,'qty','decimal'),(2,1,'position','int'),(3,4,'position','int'),(4,5,'position','int'),(6,1,'qty','decimal'),(7,3,'position','int'),(8,3,'qty','decimal');
insert  into `cataloginventory_stock`(`stock_id`,`stock_name`) values (1,'Default');

Please share this article von facebook & google plus or where you want, thank you!

GD Star Rating
loading...
GD Star Rating
loading...

Anonymise Magento database

19. Mai 2015 at 09:39

sql-statement-mysql

to anonymise the magento database you can use the following sql:

--
-- Anonymise Magento database
--
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
UPDATE `admin_user` SET
  `firstname` = CONCAT('Firstname-', `user_id`),
  `lastname` = CONCAT('Lastname-', `user_id`),
  `email` = CONCAT('admin-', `user_id`, '@test.com'),
  `username` = CONCAT('username-', `user_id`);
UPDATE `customer_entity` SET
  `email` = CONCAT('email-', `entity_id`, '@test.com');
UPDATE `sales_flat_order` SET
  `customer_firstname` = CONCAT('Firstname-', `customer_id`),
  `customer_middlename` = CONCAT('Middlename-', `customer_id`),
  `customer_lastname` = CONCAT('Lastname-', `customer_id`),
  `customer_email` = CONCAT('email-', `customer_id`, '@test.com');
UPDATE `sales_flat_order_address` SET
  `firstname` = CONCAT('Firstname-', `customer_id`),
  `middlename` = CONCAT('Middlename-', `customer_id`),
  `lastname` = CONCAT('Lastname-', `customer_id`),
  `email` = CONCAT('admin-', `customer_id`, '@test.com');
UPDATE `sales_flat_quote` SET
  `customer_firstname` = CONCAT('Firstname-', `customer_id`),
  `customer_middlename` = CONCAT('Middlename-', `customer_id`),
  `customer_lastname` = CONCAT('Lastname-', `customer_id`),
  `customer_email` = CONCAT('email-', `customer_id`, '@test.com');
UPDATE `sales_flat_quote_address` SET
  `firstname` = CONCAT('Firstname-', `customer_id`),
  `middlename` = CONCAT('Middlename-', `customer_id`),
  `lastname` = CONCAT('Lastname-', `customer_id`),
  `email` = CONCAT('admin-', `customer_id`, '@test.com');
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
UNLOCK TABLES;

Please share this article von facebook & google plus or where you want, thank you!

GD Star Rating
loading...
GD Star Rating
loading...

MySQL Query Sammlung / MySQL Query Library

8. April 2015 at 17:34

sql-statement-mysql

Some MySQL Querys which you need by the programming with php / mysql or  magento:

MySQL Update with sub-select: Update a value of an option, e.g. „price“, where option-name is like „optionname“

UPDATE catalog_product_option_type_price t1
SET t1.price='30'
WHERE t1.option_type_id in (SELECT option_type_id FROM catalog_product_option_type_title WHERE `title` like '%optionname%');

Other example with an own table where are not all product_id’s imported and must be updated by sku

UPDATE mypharmacy_stock_statistical_data t2,
(   SELECT entity_id,sku
    FROM catalog_product_entity
) t1
SET t2.product_id = t1.entity_id
WHERE t1.sku=t2.sku

Please share this article von facebook & google plus or where you want, thank you!

GD Star Rating
loading...
GD Star Rating
loading...

MySQL: Doppelte Datensätze finden

6. Februar 2014 at 17:44

Ihr müsste doppelte Datensätze innerhalb einer MySQL-Tabelle finden?

Das folgende SQL-Query hilft euch dabei (in spalte 1 werden doppelte Datensätze gesucht):

<code>SELECT COUNT(*) AS COUNT, spalte1, spalte2 FROM tabelle
GROUP BY spalte1 HAVING COUNT(*)>1;
</code>

Wenn euch der Tipp geholfen hat, bitte diesen Artikel auch bei Google+ und Facebook teilen, danke für euren Support!

GD Star Rating
loading...
GD Star Rating
loading...

Magento: Kunden-ID bei Bestellungen ändern / Bestellungen anderen Kunden zuweisen

19. November 2013 at 12:27

Möchtet ihr Bestellungen einen anderen Kunden zuweisen, ggf. weil die Bestellungen dem falschen Kunden zugewiesen sind (z.b. nach einer Datenmigration) so könnt ihr dies über die folgenden SQL-Statements erledigen:

UPDATE sales_flat_order SET customer_id = 949791 WHERE customer_id = 616151;
 UPDATE sales_flat_order_address SET customer_id = 949791 WHERE customer_id = 616151;
 UPDATE sales_flat_shipment SET customer_id = 949791 WHERE customer_id = 616151;
 UPDATE sales_flat_order_grid SET customer_id = 949791 WHERE customer_id = 616151;
GD Star Rating
loading...
GD Star Rating
loading...

Magento: Admin_User Passwort per SQL in der Datenbank ändern

10. Januar 2013 at 11:19

Mit dem folgenden SQL-Update-Script ist es möglich das Passwort eines Admin_Users direkt in der Datenbank zu ändern.

Dabei ist zu beachten, dass der String „qX“ nicht geändert werden kann, den String „password“ mit dem neuen Passwort ersetzen.

UPDATE admin_user SET password=CONCAT(MD5('qXpassword'), ':qX') WHERE username='admin';

 

 

GD Star Rating
loading...
GD Star Rating
loading...

WordPress Plugins über Datenbank deaktivieren

10. September 2012 at 16:20

Wordpress logo

Nicht korrekt funktionierende WordPress Plugins oder nicht mehr funktionierende (z.B. nach einem Update), können dazu führen, dass ihr kein Zugriff mehr auf das WordPress Admin Backend habt.
In diesem Fall ist das deaktivieren selbiger nur noch über die Datenbank möglich.

Z.B. mit Hilfe der Weboberfläche phpMyAdmin könnt ihr ganz einfach den folgen SQL-Befehl an die WordPress Datenbank abschicken:

update wp_options set option_value = “ where option_name = ‚active_plugins‘;

Danach sind alle WordPress-Plugins deaktiviert, und der Zugriff auf das WordPress-Admin-Backend sollte wieder möglich sein bzw. Plugin-Unabhängig. Unter Plugins solltet ihr die weiterhin benötigten wieder aktivieren.

Wenn euch dieser Tipp geholfen hat, bitte liken und teilen, danke!

GD Star Rating
loading...
GD Star Rating
loading...

ZendFramework: Datenbankabfrage / SQL Abfrage mit ZendFramework & Magento

15. Juli 2011 at 14:38

Mit dem folgenden Code-Schnippsel könnt ihr einfach eine sql-abfrage über zend in magento starten.

More information: framework.zend.com/manual/de/zend.db.select.html

$table = array('table_name);
$selectCol = array('table_id' , 'col_two');
$where = 'col_two >= ?';  // ? wird ersetzt mit zweitem wert in der where bedingung
$order = '';
$connection = Mage::getSingleton('core/resource')->getConnection('core_read');
$select = $connection->select()
->from($table, $selectCol)
->where($where, $this->lastExportDate)
->order($order);
echo $select->__toString();  // die komplette sql-abfrage ausgeben lassen
$result = $connection->fetchAll($select);

 

 

GD Star Rating
loading...
GD Star Rating
loading...