Useful database queries
Move products to an another category
|
1 |
UPDATE db220385_2.catalog_category_product SET category_id = 18 WHERE category_id = 19; |
Get all attribute option values The following example shows, how to get all available values for the attribute filter_stores
|
1 2 3 4 5 6 7 8 9 10 |
SELECT ea.attribute_code, eao.option_id, eaov.store_id, eaov.value_id, eaov.value FROM eav_attribute AS ea INNER JOIN eav_attribute_option AS eao ON ea.attribute_id = eao.attribute_id INNER JOIN eav_attribute_option_value AS eaov ON eao.option_id = eaov.option_id WHERE ea.attribute_code = 'filter_stores'; |
Customer – get DOB (Mage 1.10.1.1)
|
1 2 3 |
SELECT ce.*, ced.value AS dob FROM `customer_entity` AS ce LEFT JOIN `customer_entity_datetime` AS ced ON(ce.entity_id = ced.entity_id AND `attribute_id` = 11) ORDER BY ce.entity_id DESC |
Customer – get DOB from orders (Mage 1.10.1.1)
|
1 2 3 |
SELECT customer_is_guest, `customer_dob`, `customer_email`, store_name FROM `db176735`.`sales_flat_order` Order BY `entity_id` DESC; |
Change order totals
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Update sales_flat_order SET base_grand_total = 36.0000, base_subtotal = 30.2500, base_tax_amount = 5.75, grand_total = 36.0000, subtotal = 30.2500, tax_amount = 5.7500, total_qty_ordered = 3, base_subtotal_incl_tax = 36.0000, subtotal_incl_tax = 36.0000, weight = 30.000 where entity_id = 424 |
Get configurable products directly from the database Mage […]