Select and order by date from 2 different tables

[ebolt007]

I have 2 tables I have settup called Users_Messages and Users_Message_Replies inside each of these tables I have a row called DateSent, I'm trying to select from both of these tables and only display the latest Sent item by ID and order them all by date. I can get it to display the items correctly using the below code, but I can't get them to order correctly by the latest date in both of the Tables.

$page_query = mysql_query("
                         SELECT  MessageID, DateSent
                         FROM Users_Messages 
                         WHERE ToID = '$user_ID'
                           
                           UNION
                         
                         SELECT  MessageID, DateSent
                         FROM Users_Messages
                         WHERE FromID = '$user_ID'

                         
                         ORDER BY DateSent DESC
                         ");

                      	while ($replycheck = mysql_fetch_assoc($page_query)){

                          $message_idmainnew = $replycheck['MessageID'];
                          $date = $replycheck['DateSent'];

                          $sql2 = "SELECT  MainMessageID FROM Users_Message_Replies WHERE MainMessageID = '$message_idmainnew '";
                          $sql_result2 = mysql_query($sql2);
                          $replycheck2 = mysql_fetch_assoc($sql_result2);
                          $newreplyID = $replycheck2['MainMessageID'];
           
                          $sql4 = "SELECT MessageID FROM Users_Messages WHERE MessageID= '$message_idmainnew'";
                          $sql_result4 = mysql_query($sql4);
                          $messagecheck2 = mysql_fetch_assoc($sql_result4);
                          $newmessageID = $messagecheck2['MessageID'];

                          if ($newreplyID == NULL){
                          $sql2 = "SELECT * FROM Users_Messages WHERE MessageID= '$newmessageID' ORDER BY DateSent ASC";
                          $sql_result2 = mysql_query($sql2);
                          $message_row = mysql_fetch_assoc($sql_result2);
                          
                                $message_01 = $reply_row['Message'];
                      		$date = $reply_row['DateSent'];
                      		$date1 = strtotime($date);
                      		$datemain = date('F j, Y, g:i a', $date1); 
                      		
                          }else{

                          $sql2 = "SELECT * FROM Users_Message_Replies WHERE MessageID= ' $newreplyID ' ORDER BY DateSent DESC";
                          $sql_result2 = mysql_query($sql2);
                          $reply_row = mysql_fetch_assoc($sql_result2);
  
                      		$message_01 = $reply_row['Message'];

                           
                      		$date = $reply_row['DateSent'];
                      		$date1 = strtotime($date);
                      		$datemain = date('F j, Y, g:i a', $date1); 
                           }

                      	{

 

Is there an easier way to do this? And how would I get the dates to line up, with a Join? Thanks.

[ebolt007]

I decided to simplify it and just put everything into one table so now I just have a Users_Messages table, but I still have to select from a to and a from row to see if it matches my ID, then I want to be able to GROUP these by a Unique number that is in the messageID row.

             SELECT * 
                         FROM Users_Messages 
                         WHERE ToID = '$user_ID'
                           UNION
                         
                         SELECT * 
                         FROM Users_Messages
                         WHERE FromID = '$user_ID'
                         
                         ORDER BY DateSent DESC
  

 

So I want it like this, but I can't seem to group by my unioned SQL statement. Any IDEAS? Because I may have the following

ID---------MessageID--------ToID---------FromID------------comment

1-------------256id-------------1-----------------2---------------test comment1

2-------------257id-------------3-----------------2---------------test comment to other person1

3-------------256id-------------2-----------------1---------------test comment2

4-------------256id-------------1-----------------2---------------test comment3

5-------------258id-------------5-----------------2---------------test comment to other person2

 

So I would like it to just show

test comment to other person1

test comment3

test comment to other person2

 

but the way I have the above SELECT it shows all of the above comments.

and

             SELECT * 
                         FROM Users_Messages 
                         WHERE ToID = '$user_ID'
                           UNION
                         
                         SELECT * 
                         FROM Users_Messages
                         WHERE FromID = '$user_ID'
                         
                         ORDER BY DateSent DESC
GROUP BY MessageID
  

errors out when I try to group them.

[ebolt007]

Ok, so I'm stupid and got rid of the union I was using that for a join I was trying to do earlier and wasn't thinking, so now I have

SELECT * 
                         FROM Users_Messages 
                         WHERE ToID = '$user_ID' OR FromID = '$user_ID'
                         GROUP BY MessageID
                         ORDER BY DateSent DESC	

 

And while this works, it doesn't show the latest message in my group, it always shows the first. So how do I use the above, but then Order the Group by ID DESC so the last message always shows for the group display?

[ebolt007]

Nevermind, I got it.

SELECT * FROM 

                          (
                          select * from Users_Messages WHERE ToID = '$user_ID' OR FromID = '$user_ID' order by DateSent desc
                          ) as Users_Messages
                          
                          group by MessageID
                          
                          order by DateSent desc