Welcome to WebmasterWorld Guest from

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Help with building / outputting an array of pages.

Creating sitemap like output from 3 tables

11:00 am on Aug 6, 2009 (gmt 0)

New User

10+ Year Member

joined:Nov 13, 2006
posts: 5
votes: 0

Hi All,

I am completely stuck here and really need some help.

I have a MySQL database which has 3 tables storing information relating to the pages of a website.

The first table contains pretty much all the main info and is called tbl_pages :

page_id (primary key)
page_menu_title (which is what is to be displayed as the anchor text for it in the menu)
etc etc

The second table is called tbl_pages_parents and contains two fields used to specify which page is the parent of each page:


The final table is called tbl_pages_order and is used to store the order of each page should be show within its parent page group


So for example I'll give a indented list of pages in the format [page_menu_title][page_id][parent_page_id][page_order] as they would currently be stored in the database

[Home] [1][1][1]

[Services] [2][1][2]

--------[Window Cleaning] [5][2][1]

--------[Sweeping] [6][2][2]

--------[Car Wash] [7][2][3]

----------------[Wash Only] [9][7][1]

----------------[Wash and Wax] [10][7][2]

----------------[Wash, Wax and Vac] [11][7][3]

--------[Gardening] [8][2][4]

[About Us] [3][1][3]

[Contact us] [4][1][4]

As you can see the page_id values have no bearing on either the order, or the menu level of the page, they are merely used as a reference in the other tables.

What I am struggling to do is create a query which can run through each of the tables, build a multidimensional array containing all relevant details of each page and then output it to create an indented list (like the one above) which is in the correct order, and has sub menu items correctly nested.

Basically I guess you could describe it that from this structure I am looking to create a sitemap page

Also just to point out, one problem I am having is that there isn't a limit to how deep the menu can go, so for example some pages may be 6 levels in (i.e the child of a child of a child of a child of a child of the top)

Any help would be greatly appreciated, and if any further clarification is needed please shout and I'll do my best to explain.

Many thanks in advance

1:28 pm on Aug 6, 2009 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:June 10, 2008
posts: 1130
votes: 0

Having a multidimensional array like this is the exact reason we use databases. Just create another table that is a join table. It would have an ID then all three of the ids of the other tables. That way, you can select your all of your ids from each table based on the main table's id.