Own forum - number of posts and stars/ranges - how to make it ??

So I have simple forum. At main page I have gridView which display topics. When I go to topic #5 (for example) I use repeater to display posts and authors. Here is a code:

SELECT aspnet_Users.UserName, forum_posts.post_id,
forum_posts.post_content, forum_posts.topic_id, forum_posts.post_date
FROM aspnet_Users
INNER JOIN forum_posts
ON aspnet_Users.uID = forum_posts.user_id
WHERE (forum_posts.topic_id = @.topic_id)

Now I have a problem - how can I display with each user his number of posts - eventually how can I display for example if he has 25 posts - one star, 50 posts - two stars, or maybe display "Starter" range, etc. How to make this, cause I don't have any idea.

This is complex stuff but the creator of the London SQL Server user group have very nice existing code which also include a function so go to the link below and in the left search pane write in Full text index and you will see his presentation PPT slides you will get all you need in it. Hope this helps.

http://www.sqlserverfaq.com

|||

Thx for reply, but I don't know what I have to look for:

in the left search pane write in Full text index and you will see his presentation PPT

My english is not good, so forgive me, what I have to look for ? :)

|||

If what you are looking for is like what is in this thread then send me an emailCaddre@.hotmail.com and I will send you the whole presentation. Hope this helps.

http://forums.asp.net/thread/908572.aspx

|||

I sent an email to you :-)

You see - on the left from the post message here you have your name, photo, date of join and number of posts. I want this number and also make some stars/ranks depends on the number of posts. How to do it ? :-)

Little explaination about what I have:

I have Asp.net tables (by Membership).

I have also table "forum_topics":

topic_id; topic_title; user_id; topic_catId; topic_date

And table "forum_posts":

post_id; topic_id; user_id; post_content; post_date

I use stored procedures to add topic/post.

I use repeater to display posts from topic, to display topics I use gridView.

I hope you will help me :-)

|||I have sent you the file you need a function to get that info from a T-SQL tree code and it also include FTP address to get the .NET code if you need it. The person in that thread went through a lot but the file helped the person because it includes your create table statement.|||

FTP doesn't work :-(

But as you said I have to look for function in T-SQL to take out this numbers, etc, yes ?

|||All the T-SQL code is in the PPT slides and you can send Tony an email I have sent him an email 6years ago. You have to take time and create your version because the person in that thread got all they needed.|||

Ok, I've made to display a UserName with posts count :

SELECT aspnet_Users.UserName, COUNT(forum_posts.post_id) AS IlePost
FROM aspnet_Users INNER JOIN
forum_posts ON aspnet_Users.uID = forum_posts.user_id
GROUP BY aspnet_Users.UserName

Now simply I have to make something to calculating stars depend on posts.

But I think that I could do this by the code-behind in asp - simply comparing:

x - number of posts:

0<x<20 - display one star
21<x<50 - display two stars
etc.

Do you think that this is good way to do that what I want ?

|||

I have told you it is not that simple and you don't need a Group By, here is the baseline code, now go do some copying of the code in the file I sent you.

CREATE TABLE mb_mesage(
id int IDENTITY (1, 1) NOT NULL
parent_mb_message_id int NULL

entry_date datetime NOT NULL DEFAULT (GETDATE()),
parent_entry_date datetime NOT NULL DEFAULT (GETDATE()),
flattened_tree Varchar (900) NOT NULL
DEFAULT(CONVERT(varchar(20), GETDATE(),121) + REPLICATE('0',50)),
message_depth tinyint NOT NULL DEFAULT (0),
last_posting AS (dbo.fn_message_cal_last_posting(id)),
top_parent_mb_message_id
AS (LEFT(flattened_tree(CHARINDEX( ',', flattened_tree)-1)))
)

CREATE TABLE mb_message_detail(
mb_message_id int NOT NULL,
author_id int NOT NULL,
title varchar (500) NOT NULL,
body text NOT NULL,
timestamp timestamp NOT NULL,
)

NORMAl QUERY RECENT POSTING

CREATE FUNCTION fn_message_calc_last_posting (mb_message_id int)
RETURNS datetime
AS
BEGIN
DECLARE @.dt datetime=
(SELECT MAX (entry_date)
FROM mb_message
WHERE flatened_tree LIKE CAST(@. mb_message_id AS varchar (20)) + ',%'
)
RETURN @.dt
END

----
SELECT*
FROM mb_message
WHERE parent_mb_message_id IS NULL
ORDER BY Last_post DESC

To get all children posting simply use LIKE using the root message ID as the source

DECLARE @.root_mb_message_id int
SET @.root_mb_message_id =100895

SELECT spacers =REPLICATE ('+-' message_depth ),
mb_flattened_tree,
entry_date = mb_entry_date,
reads = CAST (mb_message_reads AS varchar (20)),
mb_parent_mb_message_id,
mb_message_id = mb_id,
mb_message_depth,
FROM mb_message mb,
INNER JOIN mb_message_detail md On md mb_message_id = mb_id
WHERE flattened_tree LIKE CAST (@. root_mb_message_id AS vachar (20) ) + ',%'
ORDER BY flattened_tree