CODEWITHSUNDEEP
mysql
php_nub_qq
php_nub_qq

Reputation: 16055

MySQL Select Children (Tree/Chain Structure)

I have created comments for my site which are all stored in one table regardless of the subject being user, post, photo or whatever. For which I have created a column named type and the types being respectively 0 = user, 1 = post, 2 = photo and so on. But I have also decided to add the option of replying to comments and it being indefinite. So simply I added a reply column to the table which holds the ID of the comment that the given comment is a reply to. If a certain photo's comments are to be displayed I select them like so

SELECT `stuff` 
FROM `opinions`
WHERE `type`=2

and then, say some comments have replies to them, and to get those I firstly have to check if each single comment has any replies at all, and if yes then send another SQL request to fetch the replies and do the same thing for the fetched replies and so on and so on and this seems a little.. Well wrong. Can someone lead me to the path of righteousness and show me the light please?

Table structure:

CREATE TABLE `opinions` (  
    `id` int(11) NOT NULL AUTO_INCREMENT,  
    `type` tinyint(3) unsigned NOT NULL,  
    `reply` int(11) NOT NULL,  
    `recipient` int(11) unsigned NOT NULL,  
    `sender` int(11) unsigned NOT NULL,  
    `opinion` text NOT NULL,  
    `time` int(11) NOT NULL
)

EDIT: Thank you guys for the help but all of this seems so complicated and I can't seem to understand how to implement it since I have unlimited number of children and most of the examples given have numbers of children and stuff. Unless somebody is willing to spend their time to explain this to me like to a complete idiot, I'm going to just have ajax calls to load replies.

Upvotes: 1

Views: 605

Answers (2)

Chris
Chris

Reputation: 8988

You could use a MPTT or also known as Nested set table http://www.sitepoint.com/hierarchical-data-database-2/

Good for read queries bad for write/update :)

Upvotes: 1

Wirus
Wirus

Reputation: 1190

I think you're looking for something like connect by prior in oracle See this post, this should help you.

http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/

Also you could use Tree Order Traversal alghoritm (you can find it also on wikipedia), but it requires recalculation of left and right values everytime you add/delete something to/from your table

Upvotes: 1

Related Questions