SELECT mess.subject, mess.message_txt, mess.mess_id, mess.parent_id, mess.created, users.name
FROM messages AS mess
INNER JOIN `users`
ON users.id = mess.user_id
WHERE mess.parent_id = 3
OR mess.mess_id = 3
GROUP BY mess.created
I then run it through a while loop to print each record
Two Choices:
Dont immediately process the data.
fetchAll, or while($row = $stmt->fetch()) { $datarow = $row; }
Then you can manipulate it as you do any other array. Your GROUP BY should probably be an ORDER BY instead, as you’re not actually doing any grouping. (This bit applies to both choices.)
This is the preferred choice if you’re going to also do things to other elements of the array. (Last, somewhere in the middle, etc)
Other Choice:
fetch a single row before the while loop, process it as you would the first message
then fetch the rest inside the loop.
This is probably faster if you’re only looking at the first element of the array.
If you order on your creation time field in addition to grouping by it you should always have the parent show up as the first result (it normally will be first anyway because it should have the lowest primary key). This is sufficient for a single generation pass. In multiple generation displays you’ll have to order by parent_id, creation_time.
I can use the SELECT TOP FROM …
(http://www.w3schools.com/sql/sql_top.asp) but I dont understand how to select that record as well as all the others using 1 query?
So the general form of forum handling is:
A post is a post is a post.
All posts are the same thing. A post. It’s got some text in it, etc.
All posts belong to a Thread. A thread is a new object.
All posts in a thread refer to that thread (IE: your parent_id).
Thus, all posts in a thread can be pulled by the thread ID.
You’ve structured it slightly differently, in that you’ve said all posts are in a tree; the posts in a thread are children of the thread-starter’s post.
That said, you can STILL pull everything in a single query, as you have, by saying WHERE id = 3 OR parent_id = 3.
Which post is your original? ORDER BY date. A child cant have a date smaller than it’s parent, so it is guaranteed that the first row of your result set is the thread-starter.