OpenCart SEO URLs in the database for categories and products

Practice

to do this you need to transliterate and run queries that will generate the corresponding keys for unique SEO-friendly links for each product and category

generating links for categories while taking the existing ones into account

DELETE FROM oc_seo_url WHERE is_auto=1;

INSERT INTO oc_seo_url ( store_id , language_id , query , keyword, is_auto)
SELECT 0, 2, CONCAT('category_id=',category_id) ,

replace( replace( LOWER(translit(name)),'---','-'),'--','-')

,1 FROM oc_category_description
LEFT JOIN oc_seo_url_copy osu ON osu.query=CONCAT('category_id=',category_id)
WHERE osu.query IS NULL

beforehand you need to create a copy of the table oc_seo_url_copy from oc_seo_url with the values, plus a special field for conveniently deleting is_auto

a transliteration function in MYSQL

CREATE DEFINER = 'yourusername'@'%'
FUNCTION translit(`_txt` VARCHAR(250))
RETURNS text CHARSET utf8
BEGIN
DECLARE _f varchar(5);
DECLARE _t varchar(15);
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT f,t from translit;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

open cur;
the_loop: LOOP

FETCH cur INTO _f,_t;
IF done THEN
LEAVE the_loop;
END IF;
set _txt=replace(_txt,_f,_t);
END LOOP the_loop;

CLOSE cur;
return _txt;
END

the character-mapping table for transliteration translit

CREATE TABLE IF NOT EXISTS `translit` (
`f` varchar(3) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`t` varchar(15) NOT NULL DEFAULT '',
PRIMARY KEY (`f`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO translit(t, f) VALUES
('-', '%');
INSERT INTO translit(t, f) VALUES
('-', '?');
INSERT INTO translit(t, f) VALUES
('-', '/');
INSERT INTO translit(t, f) VALUES
('-', '"');
INSERT INTO translit(t, f) VALUES
('a', 'а');
INSERT INTO translit(t, f) VALUES
('b', 'б');
INSERT INTO translit(t, f) VALUES
('v', 'в');
INSERT INTO translit(t, f) VALUES
('g', 'г');
INSERT INTO translit(t, f) VALUES
('d', 'д');
INSERT INTO translit(t, f) VALUES
('e', 'е');
INSERT INTO translit(t, f) VALUES
('e', 'ё');
INSERT INTO translit(t, f) VALUES
('zh', 'ж');
INSERT INTO translit(t, f) VALUES
('z', 'з');
INSERT INTO translit(t, f) VALUES
('i', 'и');
INSERT INTO translit(t, f) VALUES
('y', 'й');
INSERT INTO translit(t, f) VALUES
('k', 'к');
INSERT INTO translit(t, f) VALUES
('l', 'л');
INSERT INTO translit(t, f) VALUES
('m', 'м');
INSERT INTO translit(t, f) VALUES
('n', 'н');
INSERT INTO translit(t, f) VALUES
('o', 'о');
INSERT INTO translit(t, f) VALUES
('r', 'р');
INSERT INTO translit(t, f) VALUES
('s', 'с');
INSERT INTO translit(t, f) VALUES
('t', 'т');
INSERT INTO translit(t, f) VALUES
('u', 'у');
INSERT INTO translit(t, f) VALUES
('f', 'ф');
INSERT INTO translit(t, f) VALUES
('h', 'х');
INSERT INTO translit(t, f) VALUES
('c', 'ц');
INSERT INTO translit(t, f) VALUES
('ch', 'ч');
INSERT INTO translit(t, f) VALUES
('sh', 'ш');
INSERT INTO translit(t, f) VALUES
('shc', 'щ');
INSERT INTO translit(t, f) VALUES
('y', 'ы');
INSERT INTO translit(t, f) VALUES
('e', 'э');
INSERT INTO translit(t, f) VALUES
('yu', 'ю');
INSERT INTO translit(t, f) VALUES
('ya', 'я');
INSERT INTO translit(t, f) VALUES
('A', 'А');
INSERT INTO translit(t, f) VALUES
('B', 'Б');
INSERT INTO translit(t, f) VALUES
('V', 'В');
INSERT INTO translit(t, f) VALUES
('G', 'Г');
INSERT INTO translit(t, f) VALUES
('D', 'Д');
INSERT INTO translit(t, f) VALUES
('E', 'Е');
INSERT INTO translit(t, f) VALUES
('Yo', 'Ё');
INSERT INTO translit(t, f) VALUES
('Zh', 'Ж');
INSERT INTO translit(t, f) VALUES
('Z', 'З');
INSERT INTO translit(t, f) VALUES
('I', 'И');
INSERT INTO translit(t, f) VALUES
('y', 'Й');
INSERT INTO translit(t, f) VALUES
('K', 'К');
INSERT INTO translit(t, f) VALUES
('L', 'Л');
INSERT INTO translit(t, f) VALUES
('M', 'М');
INSERT INTO translit(t, f) VALUES
('N', 'Н');
INSERT INTO translit(t, f) VALUES
('O', 'О');
INSERT INTO translit(t, f) VALUES
('P', 'П');
INSERT INTO translit(t, f) VALUES
('R', 'Р');
INSERT INTO translit(t, f) VALUES
('S', 'С');
INSERT INTO translit(t, f) VALUES
('T', 'Т');
INSERT INTO translit(t, f) VALUES
('U', 'У');
INSERT INTO translit(t, f) VALUES
('F', 'Ф');
INSERT INTO translit(t, f) VALUES
('H', 'Х');
INSERT INTO translit(t, f) VALUES
('C', 'Ц');
INSERT INTO translit(t, f) VALUES
('Ch', 'Ч');
INSERT INTO translit(t, f) VALUES
('Sh', 'Ш');
INSERT INTO translit(t, f) VALUES
('Shc', 'Щ');
INSERT INTO translit(t, f) VALUES
('Y', 'Ы');
INSERT INTO translit(t, f) VALUES
('E', 'Э');
INSERT INTO translit(t, f) VALUES
('Yu', 'Ю');
INSERT INTO translit(t, f) VALUES
('Ya', 'Я');
INSERT INTO translit(t, f) VALUES
('-', 'Ь');
INSERT INTO translit(t, f) VALUES
('-', 'ь');
INSERT INTO translit(t, f) VALUES
('-', 'Ъ');
INSERT INTO translit(t, f) VALUES
('-', 'ъ');
INSERT INTO translit(t, f) VALUES
('-', ' ');
INSERT INTO translit(t, f) VALUES
('p', 'п');
INSERT INTO translit(t, f) VALUES
('-', '(');
INSERT INTO translit(t, f) VALUES
('-', ')');
INSERT INTO translit(t, f) VALUES
('-', ',');
INSERT INTO translit(t, f) VALUES
('-', '.');
INSERT INTO translit(t, f) VALUES
('-', ';');
INSERT INTO translit(t, f) VALUES
('-', ':');