Post by hearyourself » Tue May 29, 2012 3:39 pm

Hello,

I am posting a simple sql script to help those with a new installation to automatically generate the product seo urls.

Its elementary and I would suggest only using this where you have no product seo url aliases configured.

I used this after I loaded my products.

Hope this helps someone.

Code: Select all

INSERT INTO `url_alias`
SELECT null as `url_alias_id`,
concat('product_id=',`product_id`) as query,
 Replace(Replace(lower(`name`),' ','-'),'''','') as keyword FROM `product_description` 

Newbie

Posts

Joined
Tue May 29, 2012 3:33 pm

Post by tjsystems » Mon Sep 17, 2012 11:45 pm

Thanks....
saved me some time...

Do you have also somting for the Categories?

Live with 2.2.0.0 fully SSL.


Active Member

Posts

Joined
Sun Aug 26, 2012 5:39 pm
Location - NL, Amsterdam

Post by terrabyte911 » Sun Oct 21, 2012 9:36 am

Would it be possible to also add a hyphen then the model to the end of the url?
TIA

Newbie

Posts

Joined
Thu May 31, 2012 6:56 am

Post by alaskabear » Fri Apr 26, 2013 11:59 am

Thank you, @hearyourself! It worked beautifully.

Newbie

Posts

Joined
Sun Apr 14, 2013 2:06 am

Post by imagina » Sun Jul 07, 2013 3:02 am

We created a simple SQL to update all urls replacing common characters and avoid duplicated (max 3) urls.

Code: Select all

DELETE FROM url_alias WHERE 1;

INSERT INTO `url_alias`
SELECT null as `url_alias_id`,
concat('product_id=',`product_id`) as query,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(lower(`name`),':',''),'~',''),'!',''),'@',''),'#',''),'$',''),'%',''),'*',''),'(',''),')',''),'+',''),'{',''),'}',''),'[',''),']',''),';',''),'''',''),':',''),'"',''),'<',''),'>',''),'?',''),' ','-'),'><',''),'<>','') as keyword FROM `product_description` WHERE language_id=1;

INSERT INTO `url_alias`
SELECT null as `url_alias_id`,
concat('category_id=',`category_id`) as query,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(lower(`name`),':',''),'~',''),'!',''),'@',''),'#',''),'$',''),'%',''),'*',''),'(',''),')',''),'+',''),'{',''),'}',''),'[',''),']',''),';',''),'''',''),':',''),'"',''),'<',''),'>',''),'?',''),' ','-'),'><',''),'<>','') as keyword FROM `category_description` WHERE language_id=1;

UPDATE url_alias SET keyword=concat(keyword,'2')
 WHERE url_alias_id NOT IN (SELECT * 
                    FROM (SELECT MIN(n.url_alias_id)
                            FROM url_alias n
                        GROUP BY n.keyword) x);
 
 
UPDATE url_alias SET keyword=concat(keyword,'3')
 WHERE url_alias_id NOT IN (SELECT * 
                    FROM (SELECT MIN(n.url_alias_id)
                            FROM url_alias n
                        GROUP BY n.keyword) x);


http://www.imaginacolombia.com

Newbie

Posts

Joined
Sun Jul 07, 2013 1:29 am

Post by rrrdv » Thu Aug 08, 2013 4:49 pm

Where I need to put that line ? In which file ? Thank you!

Newbie

Posts

Joined
Wed Apr 10, 2013 1:51 am

Post by pierre67 » Thu Sep 26, 2013 10:05 pm

to refresh the whole SEO urls, run

Code: Select all

DELETE FROM url_alias WHERE 1;
INSERT INTO `url_alias`
 SELECT 
  null as `url_alias_id`,
  concat('product_id=',`product_id`) as query,
  Replace(name,'''','') as keyword FROM `product_description`
UNION
 SELECT 
  null as `url_alias_id`,
  concat('category_id=',`category_id`) as query,
  Replace(name,'''','') as keyword FROM `category_description`; 

i modified /admin/model/catalog/product.php to have it executed on each product update. it works great.

Code: Select all

<?php
class ModelCatalogProduct extends Model {

	public function refreshURLs()
	{
		$this->db->query("DELETE FROM url_alias WHERE 1;");
		$this->db->query(
"INSERT INTO `url_alias`
SELECT 
 null as `url_alias_id`,
 concat('product_id=',`product_id`) as query,
 Replace(name,'''','') as keyword FROM `product_description`
UNION
SELECT 
 null as `url_alias_id`,
 concat('category_id=',`category_id`) as query,
 Replace(name,'''','') as keyword FROM `category_description`;");
	}

       public function addProduct($data) {
               ................
               $this->refreshURLs();
       }

       public function editProduct($product_id, $data) {
                .................
                $this->refreshURLs();
       }
}

Newbie

Posts

Joined
Thu Sep 26, 2013 9:44 pm

Post by phpMagpie » Sun Oct 20, 2013 9:21 pm

@pierre67: It is not efficient to delete and recreate all product and category url_aliases each time you update or add a new product. A better option would be to call your function only for the product you have just added or edited.

Here are my MySQL queries which build on the very first posters method, but use MySQL's ON DUPLICATE option to save deleting any existing records.

PLEASE NOTE: for these to work you should add UNIQUE indexes to the query and keyword columns in the url_alias table (which should be there anyway to avoid duplicate errors).

Products

Code: Select all

INSERT INTO `url_alias`
SELECT null as `url_alias_id`,
concat('product_id=',`product_id`) as query,
slugify(`name`) as keyword
FROM `product_description`
ON DUPLICATE KEY UPDATE query=query;
Categories

Code: Select all

INSERT INTO `url_alias`
SELECT null as `url_alias_id`,
concat('category_id=',`category_id`) as query,
slugify(`name`) as keyword
FROM `category_description`
ON DUPLICATE KEY UPDATE query=query;
Manufacturers

Code: Select all

INSERT INTO `url_alias`
SELECT null as `url_alias_id`,
concat('manufacturer_id=',`manufacturer_id`) as query,
slugify(`name`) as keyword
FROM `manufacturer`
ON DUPLICATE KEY UPDATE query=query;
Artists (if using the free artists extension)

Code: Select all

INSERT INTO `url_alias`
SELECT null as `url_alias_id`,
concat('artist_id=',`artist_id`) as query,
slugify(`name`) as keyword
FROM `artist_description`
ON DUPLICATE KEY UPDATE query=query;

Newbie

Posts

Joined
Sat Oct 01, 2011 4:06 pm

Post by paulsimmons » Wed Sep 24, 2014 7:22 pm

Code works great, tested on one our demo website, Thanks for sharing
hearyourself wrote:Hello,

I am posting a simple sql script to help those with a new installation to automatically generate the product seo urls.

Its elementary and I would suggest only using this where you have no product seo url aliases configured.

I used this after I loaded my products.

Hope this helps someone.

Code: Select all

INSERT INTO `url_alias`
SELECT null as `url_alias_id`,
concat('product_id=',`product_id`) as query,
 Replace(Replace(lower(`name`),' ','-'),'''','') as keyword FROM `product_description` 

Newbie

Posts

Joined
Fri Sep 19, 2014 7:42 pm
Location - Austin, Texas

Post by jakeostapes » Fri Oct 03, 2014 3:03 am

Pierr67 I used your code and it's completely ruined my site. Won't accept login details, says my template extensions aren't installed when I go to homepage. Any help would be greatly appreciated right now.

Thanks, Jake.

Newbie

Posts

Joined
Fri Oct 03, 2014 3:01 am

Post by akincicakabey » Sat Feb 10, 2018 8:31 pm

Hello there

I am using opencart 1551 default theme I tried to search tag seo url but I am looking for friends who will help me in this matter

Thank you for your interest

good work

Newbie

Posts

Joined
Sat Feb 10, 2018 7:07 pm

Post by jovinetbr » Wed Mar 07, 2018 7:37 am

Are the above code examples not current correct?
Please... Does anyone have the correct code to refresh this table?

Jovi Marcos - www.jovi.net.br

Image


User avatar
Newbie

Posts

Joined
Sat Oct 01, 2016 11:31 pm

Who is online

Users browsing this forum: No registered users and 5 guests