Below you will find simple SQL queries which creates product friendly url from product’s name and saves them in the database SEO section.
You can run this i.e. in phpmyadmin or MySQL Workbench.
Example of action
From product name $ My super product 1 !!!
it will create friendly url my-super-product-1
Queries
This queries will create friendly URLs for all products in the shop:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
DELIMITER // # drop procedure to prevent errors DROP PROCEDURE IF EXISTS removeSpecialChars// # create new procedure to remove special chars CREATE PROCEDURE removeSpecialChars ( ) BEGIN DECLARE i INT; DECLARE replaceChar CHAR; SET i = 0; label: WHILE (i <= 255) DO SET i = i + 1; # skip numbers IF i >= 48 AND i <= 57 THEN ITERATE label; END IF; # replace big letters into small letters IF i >= 65 AND i <= 90 THEN UPDATE `ps_product_lang` SET `link_rewrite` = SUBSTRING(replace(`link_rewrite`, char(i), char(i+32)), 1, 128); ITERATE label; END IF; # skip small letters IF i >= 97 AND i <= 122 THEN ITERATE label; END IF; # replace char UPDATE `ps_product_lang` SET `link_rewrite` = SUBSTRING(replace(`link_rewrite`, char(i), '-'), 1, 128); END WHILE label; # remove '-' from the end and from the start of string SET i = 1; label: WHILE (i > 0) DO UPDATE `ps_product_lang` SET `link_rewrite` = SUBSTRING(`link_rewrite`, 1, CHAR_LENGTH(`link_rewrite`)-1) WHERE `link_rewrite` LIKE '%-'; UPDATE `ps_product_lang` SET `link_rewrite` = SUBSTRING(`link_rewrite`, 2, CHAR_LENGTH(`link_rewrite`)-1) WHERE `link_rewrite` LIKE '-%'; SELECT IF(EXISTS(SELECT `id_product` FROM `ps_product_lang` WHERE `link_rewrite` LIKE '%-' OR `link_rewrite` LIKE '-%' LIMIT 1), 1, 0) INTO i; END WHILE label; END; // DELIMITER ; # copy name into link_rewrite field UPDATE `ps_product_lang` SET `link_rewrite` = SUBSTRING(`name`, 1, 128); # remove special chars from all link_rewrite fields CALL removeSpecialChars(); |
Hello,
Thanks for your post 🙂
It’s possible to create a cron for this sql query ?
Regards
Hello,
If you want to create cron you need to create a script with query from post above, eg. create_friendly_url.php
Next add executing of this file in cron like this: php /path/to/file/create_friendly_url.php
Maybe my module will help you: https://github.com/evox95/mbgplr
If you want me to write such a cron task for you, write to me: contact@bestcoding.net
Regards
Hello,
Thank you for your article.
Would you have the same query to update all descriptions produced from one language (FR id 1) to all other languages (X6)?
Thanks
Hello,
You should use this script https://blog.bestcoding.net/en/copy-product-translations-from-one-lang-to-others/
Hello,
Thanks again for your scripts!
I have a problem with your module that generates the product URLs.
When a product has a character accented in its name (French name), the rewritten url stops literally before that accent.
Do you have a solution to avoid this problem?
Thank you in advance.
In fact, it’s the apostrophes that are problematic …
Ex: name of product is VEILLEUSE PROJECTEUR D’ÉTOILES
and the url rewrite is truncated before ‘ÉTOILES
Would you have a solution?
Have you tried using the above query instead of the module?
That’s not it yet …
The problem is accentuated uppercase characters
yes it’s the same result
My product name is : VEILLEUSE PROJECTEUR D’ÉTOILES
And the URL friendly is (with your module or your sql code) : veilleuse-projecteur-d
I would like URL friendly : veilleuse-projecteur-d-étoiles
Thanks
Hello,
Sorry for my english I’m french 🙂
How can I have the right URLs for my products by keeping accented characters?
I’m going around in circles since yesterday …
Thank you in advance for your help.
Bruce
Hello,
How can I transform accented uppercase characters in accented lowercase characters?
Thanks a lot for your help.
Bruce
Hello,
You don’t have any ideas for transform accented uppercase characters in accented lowercase characters ?
Thanks
Bruce
Hello,
My script removes everything which is not not number (0-9) or small letter (a-z) and replaces uppercase letters (A-Z) into lowercase (a-z).
I think it’s not a good idea to have accented characters in URL.
If you want I can add replacing accented characters into standard ASCII characters (a-z).
“If you want I can add replacing accented characters into standard ASCII characters (a-z).”
Yes, I would like it if you do not mind. 🙂
At worst, we turn uppercase accented characters letters into lowercase characters not accented
Hello,
I try this :
IF i >= 192 AND i <= 223 THEN
ITERATE label;
END IF;
But is not good 🙁
Product name : POKÉBOX NOËL 2017
Url rewrite : pokÉbox-noËl-2017
I would like : pokébox-noël-2017 or pokebox-noel-2017
Thanks
Bruce
Try this: https://pastebin.com/jbnXjWgX
Thanks but this query so long…
And if we transform accuented characters uppercase to characters lowercase(a-z) what’s the query ?
Hello,
Please, could you help me to transform accented characters uppercase to characters lowercase (a-z) ?