Showing most active forum

[doddsey_65]

How would i get it so i can show the most active forum the user has posts in. I have a posts table which lists the posts aswell as the forum they are in by id. So how would i select all of the posts with their username(easy) but then from these results calculate which id shows up the most so i can echo that?

[Psycho]

Not knowing the exact table and field names, this is just an example:

 

$query = "SELECT u.username, u.userid,
                 f.forumname, COUNT(f.forumid) as forumcount
          FROM users u
          JOIN posts p ON u.userid = p.userid
          JOIN forums f on p.forumid = f.forumid
          GROUP BY f.forumid
          WHERE u.userid = $userID";

[doddsey_65]

hopefully you will be kind enough to add my tables into that code as i have never used joins before.

 

table:

 

forum_posts

 

rows

 

forum_posts.forumid -> the id of the forum so i know which forum the post belongs to

forum_posts.postedby -> holds the username

[shortysbest]

hopefully you will be kind enough to add my tables into that code as i have never used joins before.

 

table:

 

forum_posts

 

rows

 

forum_posts.forumid -> the id of the forum so i know which forum the post belongs to

forum_posts.postedby -> holds the username

 

I'm not too sure what he was doing with that code, but I think i can help you . so the forumid would be like. well for instance:

 

Forum name = Php Test

Forum id = 1

 

Forum name = Php Test #2

Forum id = 2

 

etc. however you have yours.

 

and then in the database if the user, John let's say, posts in Forum "Php Test", then "Php Test #2", then "Php Test"

the database would look like:

 

forumid---+----postedby

1----------+-------John

2----------+-------John

1----------+-------John

 

is that correct?

[Psycho]

Can't do it with what you provided. I assumed there are three tables: users, posts and forums. You stated the posts table included an id to the forum.

 

Just replace "users", "posts" and "forums" with the respective table names. Don't forget to change the field names to their respective values as well.

 

Red = table names, blue = field names

$query = "SELECT u.username, u.userid,

                f.forumname, COUNT(f.forumid) as forumcount

          FROM users u

          JOIN posts p ON u.userid = p.userid

          JOIN forums f on p.forumid = f.forumid

          GROUP BY f.forumid

          WHERE u.userid = $userID";