0

Ok, so I have this private messaging system I'm working on, and here's kind of how it's organized

ID From-ID To-ID Message Thread
1 0 1 "blah blah blah" 1
2 0 1 "blah blah blah, blah!" 1
3 0 3 "hey <3>, how are you? Signed, <0>" 2

The way it's like this is so that you can have multiple messages in a thread, so you can reply.

Then on the left side of my private messages page, you can select which thread to look at.

So, it'll be a button that says the sender's name, so I used this SQL:

mysql_query("SELECT * FROM pms WHERE to-id='".$_SESSION['id']."' ORDER BY id ASC");

But, I want it to be something like (pseudo-code)

mysql_query("SELECT * FROM pms WHERE to-id='".$_SESSION['id']."' LIMIT 1 per `thread` ORDER BY id ASC");

So that it will only show one link to a thread.

What's happening now though is that it will show a new button, every time there is a reply to the thread. So, if there are 100 replies to message, and they're all in the same thread (since they're "replies") there will be 101 buttons, which is not ideal, for obvious reasons.

Everything is currently working, except for that button limitation that I'm talking about.

zbee
  • 959
  • 1
  • 7
  • 29

1 Answers1

1

First things first... your ID column... shouldn't it be auto-incrementing? Just checking...

If you want to get all the threads, you might want to do something like...

Select distinct thread from pms

-- then optionally...
where to-id = '".$_SESSION['id']"' order by ID asc

That way, you'll just get the first instance of that each thread.

napo
  • 869
  • 9
  • 19
  • Ok, yeah, I'm auto-incrementing, sorry bout that. I'll try your code out and see what happens :) EDIT: WOOT! It now only shows 1 button per thread, but it only grabs the thread value from that row, so I can't get the username :/ Would `distinct *` work? I imagine not but ... – zbee Oct 20 '13 at 18:40
  • 1
    Glad to hear! "Distinct *" will probably get you all rows, because you're starting with the ID. What you can do is "select distinct thread, from-ID, message, ...", which will get the distinct threads and all desired columns with that particular row. And there you'd have it. :) – napo Oct 20 '13 at 18:48