Useful MySQL queries for working with the OpenCart database

If you have an OpenCart store, sooner or later you will need to change or retrieve something from the database en masse. We offer a selection of useful MySQL queries for OpenCart.

1. Search and replace a specific phrase in product names:

UPDATE `oc_product_description` SET name = REPLACE(name, 'old text', 'new text');

2. Search and replace a specific phrase in product descriptions:

UPDATE `oc_product_description` SET description = REPLACE(description, 'old text', 'new text');

3. Replace an attribute in products (to combine identical attributes into one and remove duplicates):

UPDATE `oc_product_attribute` SET `attribute_id`='1' WHERE `attribute_id`='2';

4. Find all duplicate SEO URLs

SELECT keyword, COUNT(*) counter FROM `oc_seo_url` GROUP BY keyword HAVING counter > 1;

5. Find products with the same name

SELECT name, COUNT(*) counter FROM `oc_product_description` GROUP BY name HAVING counter > 1;

6. Get the ID of product categories

SELECT category_id FROM `oc_product_to_category` WHERE product_id = '99'

7. Get the ID of the main product category (for ocStore or if SeoPro is installed)

SELECT category_id FROM `oc_product_to_category` WHERE product_id = '99' AND main_category = '1'

8. Update all prices, multiply by a factor of 1.2

UPDATE `oc_product` SET price = (price * 1.2)

9. Restore access to the OpenCart admin - create a new administrator with login "opencart" and password "opencart"

INSERT INTO `oc_user` (`user_group_id`, `username`, `password`, `alt`, `firstname`, `lastname`, `email`, `image`, `code`, `ip`, `status`, `date_added`) VALUES ( '1', 'opencart', '4e8e674a6e2060d38cdda9e614949901e0ff4073', 'wxhCo24Fv', 'Firstname', 'Lastname', 'opencart@opencart.com', '', '', '', '', '1', '2020-01-01 10:00:00');

10. Reset the password of the administrator with the login "admin" - set the password to "password"

UPDATE `oc_user` SET `password` = '2b8c0d3013806177d1b28964cc38f8f0c3116b37', `alt` = '3ff8W98lI' WHERE username = 'admin'

11. Add the store name to the end of the product Meta Title

UPDATE `oc_product_description` SET meta_title = CONCAT(meta_title, " - My store")

12. Delete categories without name & description

DELETE c FROM `oc_category` AS c LEFT JOIN `oc_category_description` AS cd USING (category_id) WHERE cd.category_id IS NULL;

13. Bulk deletion of the attribute in all products and deletion of the attribute itself from the system

SET @attribute_id = 123; 
DELETE FROM `oc_product_attribute` WHERE `attribute_id` = @attribute_id; 
DELETE FROM `oc_attribute_description` WHERE `attribute_id` = @attribute_id; 
DELETE FROM `oc_attribute` WHERE `attribute_id` = @attribute_id;

14. Copy data from one table to another table.

INSERT INTO oc_manufacturer_description (manufacturer_id, meta_h1) SELECT manufacturer_id, name AS meta_h1 FROM oc_manufacturer;

This query will copy from oc_manufacturer to the oc_manufacturer_description table.

15. Capitalize all attribute values in products:

UPDATE `oc_product_attribute` SET text = CONCAT(UCASE(LEFT(text, 1)), SUBSTRING(text, 2));

16. Bulk replace attribute values in products:

UPDATE `oc_product_attribute` SET text = 'New value' WHERE text = 'Old value';

The article will be supplemented with useful MySQL queries for working with OpenCart database tables