Sorting an Array

Anon

Hi All,

I am trying to construct a sitemap dynamically, all page information is stored in a mysql database like so:

page_id | page_parent | page_level | page_name
1 | 0 | 1 | Homepage
2 | 1 | 2 | Section 1
3 | 1 | 2 | Section 2
4 | 1 | 2 | Section 3
5 | 2 | 3 | Section 1 Sub Page 1
6 | 2 | 3 | Section 1 Sub Page 2
7 | 3 | 3 | Section 2 Sub Page 1
8 | 4 | 3 | Section 3 Sub Page 1
etc...

I need to extract the information so it displays like this:

Homepage
-->Section 1
-->-->Section 1 Sub Page 1
-->-->Section 1 Sub Page 2
-->Section 2
-->-->Section 2 Sub Page 1
-->Section 3
-->-->Section 3 Sub Page 1

I have managed to do it with multiple SELECT queries but the database is quite large with a lot of sections and subsections resulting in 179 queries needing to be executed to build the site map, which is taking about 16 seconds.
Is there a way to extract all the data in one SELECT query and then sort it using PHP arrays?
Any help would be much appreciated.

TIA

Mike

crislewis

create a new index column which contains a heirachical page index, e.g.

000 = home page

001 = section 1

002 = section 2

001001 = section 2 sub page 1
001002 = section 2 sub page 2

You will need to allow as many digits as you need for each level.

Construct the column whenever a page is added or changed.

A simple sort on the column will give you what you need.

Note also that you could do away with page parent and page level as you can extract this from the new column. Page level is merely a factor of the length of the column value (3,6,9,12...) and page parent is the value of the column less the last three (or whatever) digits.

This also means the model is extendable to as many levels as you like. ooohhh - you could even have a use for recursive functions!

If you can be sure of your code you can use the column as the primary key and you may not need the page_id either.

Hope this helps,

Cris

mickro

Hi Cris,

Thanks for that great idea I wish I had thought of it at the start of the project. I have done so much programming based on the page_parent & page_level that I think it will be a complete nightmare to change now.

I have come up with a solution (of sorts) though. What I am going to do is execute the query that I have already got to write to a text file (effectively caching it) I will then include this on the page. The text file will only be recreated when a new page is added. This will helpme to reduce the loading time of the page.

(Isn't it funny how you can be doing your head in over something for weeks, then as soon as you ask for help you work it out 😉

Thanks for your help anyway, I will certainly keep it in mind next time I do a project like this.

Cheers

Mike
www.digitalegg.net