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;

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