forum - php/mysql - recordset

[jsteinmann]

Current Recordset:
SELECT id, threadid, postersname, topic, message, dateposted, threadstarter,
mypicurl FROM forum WHERE threadstarter = '1' ORDER BY id DESC

repeat region displays results. Since the where clause limits to only those
that started threads, there are no duplicate topics.

Now I want to add two things to the results:

1. display the number of replies to the topic (i.e. all the records that match
the same threadid as the above recordset in the repeat region, but where
threadstarter = '0' )

I would also like to display the last person to enter a reply (i.e. threadid
matches the threadid of the recordset repeat region and is the last entry in
database).

How can I do this. I want to minimize my calls / recordsets to accomplish
this.

[jsteinmann]

I guess what I'm trying to figure out, do I want to use a nested recordset in the repeat region? I just am not sure how else I would do this.

[Dooza]

Post by jsteinmann
SELECT id, threadid, postersname, topic, message, dateposted, threadstarter,
mypicurl FROM forum WHERE threadstarter = '1' ORDER BY id DESC
repeat region displays results. Since the where clause limits to only those
that started threads, there are no duplicate topics.
1. display the number of replies to the topic (i.e. all the records that match
the same threadid as the above recordset in the repeat region, but where
threadstarter = '0' )
I would also like to display the last person to enter a reply (i.e. threadid
matches the threadid of the recordset repeat region and is the last entry in
database).
How can I do this. I want to minimize my calls / recordsets to accomplish
this.

You want a sub-query in your SQL, you basically put another select
statement in brackets followed by AS ColumnName where ColumnName is the
new name of that column. Make sense? If not I will try to explain a
little more.

Dooza

[jsteinmann]

well, I got it to work, but I nested the two recordsets within the repeat
region. My concern with doing it that way, which is why I posted the question,
was with performance doing it this way. If you could show an example that
would be great.

[Dooza]

Post by jsteinmann
well, I got it to work, but I nested the two recordsets within the repeat
region. My concern with doing it that way, which is why I posted the question,
was with performance doing it this way. If you could show an example that
would be great.

SELECT O.id, O.threadid, O.postersname, O.topic, O.message,
O.dateposted, O.threadstarter, O.mypicurl, (SELECT COUNT(id) FROM forum
AS I WHERE I.id = O.id AND I.threadstarter = 0) AS Replies, (SELECT TOP
1 R.postersname FROM forum AS R WHERE R.id = O.id ORDER BY R.dateposted
DESC) AS LastPoster
FROM forum AS O
WHERE O.threadstarter = 1 ORDER BY O.id DESC

Dooza