Jump to content


Photo

Complex sort for forum software


  • Please log in to reply
3 replies to this topic

#1 bigmatt19

bigmatt19
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 12 August 2006 - 03:27 PM

On the one hand I hope this is simple, but on the other I'd like to think of myself as being pretty good with this stuff  ;)... so here's the issue... I am creating a threaded message forum from scratch.  I have figured out a way to display the threads using a recursive algorithm... but it seems inefficient.  The forum would be sorta like slashdot's in that the replies to each post will be nested...

I have a two tables: messages and messagerelation.  Messages contains:
messages













messageID parentID etc.
0NULLNULL
31etc..
42etc..
52etc..
10etc..
21etc..
63etc..


Then messagerelationships contains:
messagerelationships
















messageIDrelatedto
10
21
20
31
30
42
41
40
52
51
50
63
61
60


Ok.. so here is what I want to get when I perform a query to grab every message related to a messageID:
RESULT DESIRED












messageID parentID etc.
10etc..
21etc..
42etc..
52etc..
31etc..
63etc..


but what I get:
ACTUAL RESULT












messageID parentID etc.
10etc..
21etc..
31etc..
42etc..
52etc..
63etc..


The query I am using I knew wouldn't give me what I wanted, but I hoped it would be a good start to inspire me to write the correct query.  Here it is:
SELECT * FROM messages
WHERE messageid IN (SELECT messageid 
                 FROM messagerelationships
                 WHERE relatedto = 1) 
      OR messageid = 1
ORDER BY parent;

Any help you can give would be a life-saver!

TIA!
Matt

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 12 August 2006 - 05:03 PM

I'm confused... how is the desired result supposed to be organzied?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 bigmatt19

bigmatt19
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 12 August 2006 - 07:02 PM

The desired result would allow me to produce something like this:
[pre]
Original Message (1)
  Reply to Original (2)
      Reply to 2 (4)
      Reply to 2 (5)
  Reply to Original (3)
      Reply to 3 (6)
[/pre]

And really to be able to nest as deep as I want... dynamically.

Hope that clarifies things a bit.

-Matt

#4 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 13 August 2006 - 05:49 AM

You would be easiest off with a Main-post ID in your table, all sub-posts would relate to the main id, then the parent id will sort further.
That way cou can order them by one table.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users