mysql查询从给定的表结构创建SEO友好的url

问题描述

我想使用下面的表格创建SEO友好的网址：

我想写一个mysql查询

预期输出：

这里是MySql查询，我试图生成URL多达4段：

  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 
  

$ b b

链接到

解决方案

这里是我自己的问题的答案： / p>

我尝试使用我发现通过stackoverflow在查询优化和其他研究方面更有用。

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

Category table

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:

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

解决方案

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.

这篇关于mysql查询从给定的表结构创建SEO友好的url的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！