MySQL Query to retrieve full URL slug

Question

First question!

I have a MySQL table which stores all content on the various pages of a site. Let's say there's three fields, id(int), permalink(varchar) and parent(int)(. parent is the id of it's parent page.

I need a query that will build a full URL of a page, I'm guessing using CONCAT. I have it working fine for two levels, but can't figure out a way to make it scale for multiple levels; /root/level1/level2/ etc.

Here's what I have so far.

SELECT 
CONCAT(
(SELECT permalink FROM content WHERE id = 2 LIMIT 1), # id = parent
"/", 
(SELECT permalink FROM content WHERE id = 11 LIMIT 1)) as full_url

Any help, greatly appreciated!

Answer 1

You cannot do recursion in a query, you would have to use stored procedures but this is not available in MySQL.

Answer 2

That would be a recursive query, you have to use a stored procedure on the server (Which are avaiable in MySql @Claude).