Page 1 of 1

MySQL - Finding out the parents in oen query

Posted: Tue Feb 16, 2010 8:39 am
by Mr Tech
I am creating a CMS that uses permalinks to display pages...

My permalinks will look something like this:

http://domain.com/about


Any sub pages of the about page will look like this:

http://domain.com/about/testimonials

Any sub pages of the about/testimonials page will look like this:

http://domain.com/about/testimonials/page2


And so on... I trust you catch what I am trying to say....

Each page has a unique ID... So say I know the id of page2. I will need to find out that page2 is a sub page of testimonials and then i'll have to also find out that testimonials is a sub page of about. Hopefully I haven't lost you :)

Is there any way I can do this in one MySQL query? Rather than running a query that finds the parent_id of page2 and then run another query that finds the parent_id of testimonials? Obviously the more parents, the more loops I'll have to run...

Hopefully I am making sense. I'm wanting to cut down as much MySQL queries as possible so I presuming that putting it in one query would be faster right?

If I don't make sense please let me know and I'll try think of a better way to explain :)

Re: MySQL - Finding out the parents in oen query

Posted: Tue Feb 16, 2010 10:00 am
by Darhazer
I think the only way you can do this is a with a stored procedure.
However you can load both object data and it's parent data with a single query, using a self-join, and you will shorten the loop.
Additionally, too long URLs are not SE-friendly at all :)

Re: MySQL - Finding out the parents in oen query

Posted: Thu Feb 18, 2010 8:57 am
by VladSun
As I suggested in another thread of yours - use the "Nested Set Model" - take a look at "Retrieving a Single Path" in its section.