How do display ranks in a forum system

Archbob

I know that there is a rank table and a user table. THe rank table has the rank and the number of posts needed to achieve that rank, so how do I get it to display the user and the rank right. I know how to select and display from two tables but the problem is its not an equals thing, I need to to display the lowest rank equal or greater to the number of posts you have

drawmack

Assumptions
tblRanks has a column called posts which is the number of posts needed to achieve a rank

tblUsers contains a column called posts which is the number of posts a user has.

select max(b.posts), b.RankName from tblUsers as a, tblRanks as b where a.ID = $userid and b.posts >= a.posts group by b.posts

Archbob

I actually have to select from 3 tables at once:
This is the Select I have right now that shows the post and all the poster's information:

$threadselect="SELECT * from b_posts,b_users where b_users.userID=b_posts.author and b_posts.threadparent='$ID' order by ID ASC";

So how would you incorporate the ranks thing into that?

BuzzLY

Actually, your SELECT statement pulls posts in a thread, which happens to include the author information. That doesn't do anything for you. You need to figure out how many posts each user has made. You could do SELECT count(*), but depending on the number of users in the thread, that could take FOREVER.

I suggest that you create a column called "post_count" in the user table (or better yet, a separate table with post_count and user_id columns). That way all you have to do is pull the post_count value and use this, which is almost identical to drawmack's:

$sql = "SELECT max(r.num_posts), r.rankname " . 
       "FROM u_ranks u, ranks r " . 
       "WHERE u.post_count >= r.num_posts " . 
       "AND u.id = '$userid' " .
       "GROUP BY r.num_posts";

(code untested... try it out yourself)

Archbob

Is it possible to select from 3 tables at once? Because I need the query to pull the thread, author information, and the author's rank.

BuzzLY

You can pull data from as many tables as you want. If all your JOINS are simple inner joins, then just use:

SELECT *,max(r.posts) as th 
FROM users u, posts p, ranks r 
WHERE u.post_count >= r.num_posts 
AND u.id = p.user_id 
AND u.id = '$userid' 
GROUP BY r.num_posts

If you have more complex joins, then I suggest you go to www.mysql.com and look up JOINs in the manual. The syntax is a little different (SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.ID = table2.fkey_ID WHERE table1.ID = $yourID)...

Archbob

I love you guys!

Archbob

Okay I have the following code:

$postselect="SELECT *,max(r.postsneeded) as th FROM b_users u, b_posts p, b_ranks r WHERE u.posts>= r.postsneeded AND u.userID = p.author and p.ID='$ID' GROUP BY r.postsneeded";

Now this work but with one large glaring problem. It displays each method x amount of times based on what rank they are. For instance if they go above 5 posts and achieve the 2nd rank , it will display the message twice, once with the name with the first rank and once with the name a the 2nd rank.

Can I possibly get it to just display the message once with the highest rank the user has achieved?

Archbob

Okay, I figured it out, you have to group by something that is unrelated.