mySQL: Designing a TreeView Structure.

Hello Everyone..

I am looking for designing a database structure for a treeview plugin.

The nodes should be populated from the database. The root node can have unlimited level of child nodes and each child node can further have unlimited level of sub-child nodes.

Can someone please suggest me the database structure and possibly the query to fetch each nodes/child nodes.

This is a structure I have thought of so far but I am not sure if this is the proper way i should be desining the table.

CREATE TABLE `masters` (
  `master_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `master_title` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`master_id`)
) ENGINE=MYISAM AUTO_INCREMENT=208 DEFAULT CHARSET=latin1


CREATE TABLE `mapping` (
  `mapping_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `master_id_1` INT(10) DEFAULT NULL,
  `master_id_2` INT(10) DEFAULT NULL,
  `master_level_number` INT(10) DEFAULT NULL,
  `master_level_sequence` INT(10) DEFAULT NULL,
  PRIMARY KEY (`mapping_id`),
  KEY `id_index` (`master_id_1`)
) ENGINE=MYISAM AUTO_INCREMENT=151 DEFAULT CHARSET=latin1

Code (markup):

Kindly Help.

Thanks

 

Is this usable for your search?
http://www.innvo.com/c/PHP/1312794035-directory-tree-php-mysql

 

Turn your problem upside down. Each child node has a pointer to its parent. You don't keep a record (in the record) of the children, just of the one parent.