Create friendly URL based on product name in Prestashop using SQL query

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:


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();

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();

I ask you to review this article

Rating 5 Stars from 2 reviews

19 thoughts on “Create friendly URL based on product name in Prestashop using SQL query”

Bruce says:

11/10/2017 at 09:52

Hello,

Thanks for your post 🙂

It’s possible to create a cron for this sql query ?

Regards

Reply
1. evox95 says:
  
  11/10/2017 at 21:35
  
  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: [email protected]
  
  Regards
  
  Reply
Bruce says:

13/11/2017 at 10:03

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

Reply
1. evox95 says:
  
  13/11/2017 at 12:59
  
  Hello,
  You should use this script https://blog.bestcoding.net/en/copy-product-translations-from-one-lang-to-others/
  
  Reply
Bruce says:

15/11/2017 at 15:50

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.

Reply
Bruce says:

15/11/2017 at 16:43

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?

Reply
1. evox95 says:
  
  15/11/2017 at 17:14
  
  Have you tried using the above query instead of the module?
  
  Reply
Bruce says:

15/11/2017 at 17:20

That’s not it yet …
The problem is accentuated uppercase characters

Reply
Bruce says:

15/11/2017 at 17:33

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

Reply
Bruce says:

16/11/2017 at 11:03

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

Reply
Bruce says:

17/11/2017 at 13:14

Hello,
How can I transform accented uppercase characters in accented lowercase characters?
Thanks a lot for your help.
Bruce

Reply
Bruce says:

21/11/2017 at 13:00

Hello,
You don’t have any ideas for transform accented uppercase characters in accented lowercase characters ?
Thanks
Bruce

Reply
1. evox95 says:
  
  21/11/2017 at 13:13
  
  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).
  
  Reply
Bruce says:

21/11/2017 at 13:19

“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. 🙂

Reply
Bruce says:

21/11/2017 at 14:31

At worst, we turn uppercase accented characters letters into lowercase characters not accented

Reply
Bruce says:

22/11/2017 at 10:31

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

Reply
1. evox95 says:
  
  23/11/2017 at 14:43
  
  Try this: https://pastebin.com/jbnXjWgX
  
  Reply
Bruce says:

23/11/2017 at 16:45

Thanks but this query so long…
And if we transform accuented characters uppercase to characters lowercase(a-z) what’s the query ?

Reply
Bruce says:

28/11/2017 at 08:46

Hello,
Please, could you help me to transform accented characters uppercase to characters lowercase (a-z) ?

Reply

Example of action

Queries

19 thoughts on “Create friendly URL based on product name in Prestashop using SQL query”

Leave a Reply Cancel reply