mysql query to create SEO friendly url from given table structure

Question

I am trying to create SEO friendly URLs using the below tables:

category

pages table

I am trying to write a mysql query that will generate URLs for all the pages in the pages table using the category table producing the below output.

Expected Ouput:

expected

Here is the MySql query that I tried for generating URLs upto 4 segments:

SELECT pg.id AS page_id, p3.id, p1.category AS segment1, p2.category AS segment2, p3.category AS segment3, 
pg.page_name AS PAGE , concat( '/', p1.category, '/', p2.category, '/', p3.category, '/', pg.page_name, '/' ) AS url
FROM category AS p1, category AS p2, category AS p3, pages AS pg
WHERE pg.category_id = p3.id
AND p3.parent_id = p2.id
AND p2.parent_id = p1.id

Link to SQL Fiddle

Have you checked out any database recursion? this looks like an exercise on recursion... just sayin' — hanzo2001, Nov 29 '13 at 09:14
@mahavir-munot, I believe that you can't do that for arbitrary URL depth without creating stored procedure (which would call itself recursively). — Dmitry Pljonkin, Nov 29 '13 at 09:35
Things like this have been asked before: [stack overflow](http://stackoverflow.com/questions/4345909/mysql-recursion). You should do some more research before attempting to do this — hanzo2001, Nov 29 '13 at 09:35

score 1 · Accepted Answer · edited May 23 '17 at 11:44

1

Here is the answer to my own question:

I tried using the "MySql Modified preorder tree to create url" method which I find more useful in terms of query optimization and other research through stackoverflow.

edited May 23 '17 at 11:44

Community

1
1

answered Dec 06 '13 at 15:33

Mahavir Munot

1,464
2
22
47

score 0 · Answer 2 · answered Nov 29 '13 at 08:08

0

Not the best solution, but it works ;)

SELECT pagetable.id AS page_id
, c1.id
, pagetable.page_name AS PAGE 
, concat( 
    IF(c3.category IS NULL,'','/')
  , IF(c3.category IS NULL,'',c3.category)
  , IF(c2.category IS NULL,'','/')
  , IF(c2.category IS NULL,'',c2.category)
  , IF(c1.category IS NULL,'','/')
  , IF(c1.category IS NULL,'',c1.category)
  , '/'
  , pagetable.page_name
  , '/' ) AS url
FROM pages AS pagetable 
LEFT JOIN category AS c1
ON pagetable.category_id = c1.id
LEFT JOIN category AS c2
ON c1.parent_id = c2.id
LEFT JOIN category AS c3
ON c2.parent_id = c3.id

answered Nov 29 '13 at 08:08

Jacob A.

270
3
10

1

yes, because there are only, 3 Joins, you could add more joins, but like i said, its not the best solution – Jacob A. Nov 29 '13 at 08:21
Everyone should go check out another [stackoverflow question](http://stackoverflow.com/questions/4345909/mysql-recursion) – hanzo2001 Nov 29 '13 at 09:37
@hanzo2001 I can use the left - right approach but I want to see if there is any other approach – Mahavir Munot Nov 29 '13 at 09:46
1

here's the thing, you want to generate a **hierarchical** structure through a flat (**linear**) data table. I'm sorry to say this but the best (and complete) approach is through recursion. The other way is through the _Adjacency List Model_ a la @lgoel style – hanzo2001 Nov 29 '13 at 10:00
@hanzo2001 please see http://stackoverflow.com/questions/20422497/mysql-modified-preorder-tree-to-create-url – Mahavir Munot Dec 06 '13 at 11:19
@Igoel please see http://stackoverflow.com/questions/20422497/mysql-modified-preorder-tree-to-create-url – Mahavir Munot Dec 06 '13 at 11:19

mysql query to create SEO friendly url from given table structure

2 Answers2

Linked