MySQL - Finding out the parents in oen query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

MySQL - Finding out the parents in oen query

Post 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 :)
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: MySQL - Finding out the parents in oen query

Post 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 :)
Last edited by Darhazer on Thu Feb 18, 2010 8:57 am, edited 1 time in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL - Finding out the parents in oen query

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply