forum - php/mysql - recordset

Report · Mar 30, 2009

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.

Report · Mar 30, 2009

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.

Report · Mar 31, 2009

jsteinmann wrote:
> 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.

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
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html
How To Report A Bug To Adobe
http://www.adobe.com/cfusion/mmform/index.cfm?name=wishform

Report · Mar 31, 2009

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.

Report · Apr 01, 2009

jsteinmann wrote:
> 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
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html
How To Report A Bug To Adobe
http://www.adobe.com/cfusion/mmform/index.cfm?name=wishform