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!

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!

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!

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!

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!

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!

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;

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';

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!

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