order by

[fazzfarrell]

I have my forum working fine, the data comes form two tables Message and MessageR (messageReply table)

they are tied together by
[code}
SELECT Message.ID, Message.`DATE`, Message.USERNAME, Message.EMAIL, Message.MESSAGE, Message.TITLE, Message.BLOCKED, Message.NoReply FROM Message    UNION  SELECT MessageR.ReplyID, MessageR.`DATE`, MessageR.USERNAME, MessageR.EMAIL, MessageR.MESSAGE, MessageR.TITLE, MessageR.ID, Message.BLOCKED
FROM MessageR, Message
ORDER BY Date DESC
[code]

As they run by date order the newest reply appears at the top of each message, I want the original message not the reply to appear at the top?

any ideas?[/code]

[trq]

You need to make another field in your Message table to store the time when a reply is added to the MessageR table.

You then order by this.

[fazzfarrell]

How can I have two ORDER by? in the same recordset

[Snooble]

calculate both columns and order by that.

[fazzfarrell]

Cant seem to get this to work at all!

[trq]

Why would you need to order by both?

[fazzfarrell]

Yes,

They all need to decend in date order, buy the replys are binding to the origanal post so:

I need the original post to be first
    then the second post to appear below it like this
    then the next one

At the moment it apears like this:

    then the next one
    then the second post to appear below it like this
I need the original post to be first

So date order is fine untill its a relpy!

?

[trq]

Make two fields, post_date reply_date in your Message table. When an initial post is made put the current time in both, then, when a reply to a post is made update the reply_date to the replies time.

Then, simply sort your posts based on the replay_date field.

You could actually get away with one date field if you wished.

[shoz]

Post a small example of the the data in the two tables and the output you're looking for. I don't follow why there's a JOIN in the second query of the UNION with no condition on which to join the tables.

[quote=fazzfarrell]
SELECT MessageR.ReplyID, MessageR.`DATE`, MessageR.USERNAME, MessageR.EMAIL, MessageR.MESSAGE, MessageR.TITLE, MessageR.ID, Message.BLOCKED
FROM MessageR, Message
[/quote]

[fazzfarrell]

is table atteched

[attachment deleted by admin]

[fazzfarrell]

second table attched

[attachment deleted by admin]

[fazzfarrell]

Reffin' Hell
By: Grant Stevens. on: 2007-22-01 03:48:27 PM

  Winshill U12B 4 TL Darby 2     
  By: paul-walters on: 2007-23-01 01:13:48 PM
  Reffin' Hell 
  By: Kerry Walker on: 2007-23-01 03:57:03 AM

Well done lads
By: Ryan Pirrie on: 2007-21-01 08:37:07 PM
Under 9 B Team lose 3-2 to Gresley - It just wasn't our day 
By: Kevin Smith on: 2007-20-01 11:41:35 AM
Cancelled Again!!!
By: Andy Ward on: 2007-20-01 09:15:52 AM

[shoz]

You'll need to modify the UNION carefully if there's other information that you need for the page.

[code]
(SELECT id, DATE, USERNAME, TITLE FROM Message)
UNION ALL
(SELECT ReplyID, DATE, USERNAME, TITLE FROM MessageR)
ORDER BY
id DESC, DATE ASC
[/code]

If you do a JOIN you'll have better control IMO.
[code]
SELECT
....
FROM
Message
LEFT JOIN
MessageR
ON
Message.ID=MessageR.ReplyID
ORDER BY Message.ID DESC, MessageR.DATE
[/code]

[fazzfarrell]

Tried both ways but I keep getting an error in dreamwaever when I test it?

[shoz]

What's the error?

Note that [b]If[/b] you're using DreamWeaver to generate the code I'll have to recommend you read some of the tutorials on this and other sites so that you'll be better able to write the code yourself.

[fazzfarrell]

I now have this as a statement

SELECT Message.ID, Message.`DATE`, Message.USERNAME, Message.TITLE, Message.MESSAGE, MessageR.ReplyID, MessageR.`DATE`, MessageR.USERNAME, MessageR.TITLE, MessageR.MESSAGE
FROM Message JOIN MessageR ON Message.ID=MessageR.ReplyID
ORDER BY Message.ID DESC, MessageR.DATE

All I need to do now is get it to show all the records not just the one that have a reply!

any one!!!!

[shoz]

Use a LEFTJOIN.

[fazzfarrell]

tearing what little hair i have left out now!

I now have


SELECT Message.ID, Message.`DATE`, Message.USERNAME, Message.TITLE, Message.MESSAGE, Message.NoReply FROM Message UNION ALL SELECT MessageR.ReplyID, MessageR.`DATE`, MessageR.USERNAME, MessageR.TITLE, MessageR.MESSAGE, MessageR.NoReply
FROM MessageR
ORDER BY DATE DESC, ID  DESC

This puts everything in order except if the 'Message' table has a reference in 'MessageR' the original message in 'Message' does not appear on the page!

HELP!!!!

[fazzfarrell]

Ha ha done it, database was slightly out


thanks for all your help!