Welcome to WebmasterWorld Guest from 54.166.46.226

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)

5+ Year Member



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_meta_title
page_meta_desc
page_content
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:

page_id
parent_page_id

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

page_id
page_order

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)

WebmasterWorld Senior Member 5+ Year Member



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.