homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Multi-level data and best handling practices with mysql and php

 8:17 am on Aug 1, 2011 (gmt 0)

In a database with a category tree or hierarchy what is a best practice method for getting additional data from higher up the tree or hierarchy?


category 456 is called bluewidgets and has a parent category numbered 123 called widgets. In the database each row has a unique catnumber as well as a catname and parentcatnumber. There is also a 4th table with the tree level, ie: 1,2,3 or 4.

what I want it to do
I'm trying to build a breadcrumb navigation system but there are 10's of thousands of entries in the database so I need an efficient method of extracting the parentcatnumber and catname of that parentcatnumber. If the catnumber I specify is 3 levels deep I will need the parentcatnumber and name of the level 2 and level 1 categories it belongs to.

best practice

Would it be better to leave the database at 4 tables and to extract the data via php or would it be better to create additional tables and store the name and numbers of any "higher up" categories into each row?

My thinking is that the data is in the database so I shouldn't need to duplicate it inside the database by adding entries to other entries... but I'm not sure how I'd extract the data efficiently via php either.

Has anyone had experience they are willing to share in building a breadcrumb like feature where the information is in multiple rows?



 2:38 pm on Aug 3, 2011 (gmt 0)

it takes a few reads to get round it, but the MODIFIED PREORDER TREE TRAVERSAL is the best way i've found.


all my sites use this as navigation. this a completely separate table which references all products using a product ID. just one table is needed.

takes care of all nav, breadcrumbs, etc


 10:10 pm on Aug 3, 2011 (gmt 0)

Thanks jamie

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved