1

I've gotten my union statement to work the way I want it, but I cant seem to get it to order the 2 tables without erroring out.

$query = mysql_query("SELECT userid_2 FROM messages 
WHERE userid_1='$dbid' UNION SELECT userid_1 FROM messages WHERE userid_2='$dbid'");

and I'm trying to incorporate ORDER BY date into it but it doesn't seem to work.

Don't Panic
  • 13,965
  • 5
  • 32
  • 51
Joe Jankowiak
  • 295
  • 3
  • 15

2 Answers2

0

have you tried:

$query = mysql_query("
      SELECT * FROM (
         SELECT userid_2,date FROM messages 
         WHERE userid_1='$dbid' 
         UNION 
         SELECT userid_1, date
         FROM messages 
         WHERE userid_2='$dbid'
      ) as table1
 ORDER by table1.date");

EDIT:
based on your comment below I'll do something like that:

      SELECT * FROM (
         SELECT userid_1,userid_2 FROM messages 
         WHERE userid_1='$dbid' 
         ORDER BY date ASC) as table1
      UNION 
      SELECT * FROM (
         SELECT userid_1,userid_2
         FROM messages 
         WHERE userid_2='$dbid'
         ORDER BY date ASC) as table2
Marcx
  • 6,806
  • 5
  • 46
  • 69
  • the only thing id have to change on this would be the field that im trying to sort it by? so table1.date? If so that doesn't work either. Same error. – Joe Jankowiak Nov 02 '11 at 19:54
  • yes, I edited the query, you need to select also the date from the "internal" query... otherwise you cant sort by date... try now... I tried in a sample table and it worked... – Marcx Nov 02 '11 at 19:55
  • That ordered it but now its not being distinct. and you cant put DISTINCT in there can you because its supposed to already be it? – Joe Jankowiak Nov 02 '11 at 19:59
  • I don't get your problem the internal query return a table composed by some userid_2 and some userid_1, then you order it by date... maybe do you want to order by id and date? – Marcx Nov 02 '11 at 20:03
  • Ill try to explain this but it probably wont make much sense. what i have is a messaging system where it loads all ur recent conversations. Userid_1 is the sender and Userid_2 is the receiver of the message In the inbox it has to load all the messages that are sent to you along with the ones youve sent, but i need it to group it by the ID Basically it needs to only show the first message of each conversation and it needs to be ordered by the date. – Joe Jankowiak Nov 02 '11 at 20:06
  • edited the post, try the new query... hope this help, I haven't test it... – Marcx Nov 02 '11 at 20:18
  • I think you may have gotten it. Let me just do a bit more testing. – Joe Jankowiak Nov 02 '11 at 20:24
0

This doesn't have much to do with your problem, but you should not be using the mysql php extension anymore. It is planned for depreciation (see http://marc.info/?l=php-internals&m=131031747409271&w=2), you should consider switching to mysqli or PDO.

Green Cat
  • 141
  • 1
  • 2
  • 12