[SOLVED] Need help with a somewhat complex query.

[NeoMarine]

Ok so here is the structure of my table:

ID - OwnersID - TheMessage - DateTimeAdded - UsersID - RepliedToStartID - RepliedToLevel

 

What I'm trying to accomplish:

I display a series of messages on the page, and I allow users to "reply" to a particular message. When someone replies to that message, I need it to be displayed "underneath" the message it was a reply to.

 

How I store my data:

I use the column RepliedToStartID as the "ID" of the row that the message is owned by, at the very base level. The RepliedToLevel tells me the "level" deep the message is at. So, if john (whos ID is 100) wrote a message and sarah replied to the message later on (whos ID is 105), on screen it should show johns message, followed by sarahs message - and sarahs RepliedToStartID = johns ID (100), and RepliedToLevel = 1.

 

The query:

The query therefore needs to "ORDER" the messages "BY" the DateAdded, and then if a previous rows ID = the next rows RepliedToStartID, that row should be placed after the previous row.

 

One idea I had was to use multiple queries, which would be rather simple - but also taxing on the server - I dont want to slow things down. So, essentially I must create a query that selects the rows by their RepliedToStartID, as well as the DateAdded and RepliedToLevel - but in the ORDER of "Message (100)" and then "Message (105)" -- 105 being the message with RepliedToStartID of 100 and Message 100 being the previous message displayed.

[fenway]

mysql has no concept of next/previous.

 

not sure why you need the level, can't you tell directly?

 

also, why not build this structure in script?

[kickstart]

Hi

 

Can't really see a way of doing it in 1 statement, but would be simple to do recursively.

 

All the best

 

Keith

[NeoMarine]

Its ok, I decided to leave this feature out of my project considering its a bit excessive for my needs at the moment - will look into solutions in future if needed. Thanks for your time.