I am developing a little web application and i want to have a rudimentary commenting system. This has turned into an academic exercise
I want a user the ability to reply to a comment which itself may be a reply.
The comments table would be something like
replyTo (null if top ancestor an existing id if a reply)
So for example the following structure may be a snapshot
Comment 3 Reply to 1
Comment 11 Reply to 3
Comment 16 Reply to 11
Comment 18 Reply to 1
Comment 27 Reply to 18
Comment 29 Reply to 27
Comment 5 Reply to 1
Comment 19 Reply to 1
Comment 30 Reply to 19
Comment 6 Reply to 2
Because of the recursiveness of the comments i cannot think of how i can get an ordered system which i then then just dump to a div with an indentation to imply the level.
Can anybody help me pls in how to get an ordered recordset using Mysql (if it is possible even ). I am stumped
There may be a restriction say of 3 levels say if that is any help ie there can be a reply to a reply, but not a reply to a reply to a reply
They would be chronologically-ordered in reverse (by ancestral comment) and maybe by priority of ancestral comment.
I ave implemented a similar system using a comment table and a replies table and just merged the two.
I was basically wondering if it were possible to use a single table and have any nested level.
your replyTo column indicates that you are using the adjacency model
see Categories and Subcategories for further information on returning chained results
this is definitely feasible if you have a limit on the number of levels (you say 3 but you could make it up to 15 without worrying about performance)
Thank you Rudy for your quick reply
Reliable as ever
If it is of any worth, i did buy your book :rofl:
ta very much
every few pennies help, eh
i enjoyed reading your Categories and Subcategories article
Is there an index page for what is on sqllessons Rudy?
yeah, but it's broken at the moment
no estimated fix date either, sorry (shoemaker's children syndrome)
here's the other published article (i have a dozen or so more on my "to do" list) -- Minimize Bandwith in One-to-Many Joins
lol like idea of shoemaker's children syndrome
just realised if i add a level field (implicit when adding a comment as you can determine the level to which a user is replying to (and adding 1) then just adding another ORDER BY parameter
now makes algorithm easier