SQL question, COUNT?

[helraizer]

Hi folks.

 

In my table I have

 

 

tblcomments

post_id

username

comment

time

user_id

 

and another

 

tbluser

user_id

username

password

email

dob

registered (date)

ip

logged_in

last_login

warned

active

key

banned

ban_date

 

each user has a chatbox, and each post is put into the tblcomments table with the user ID so the chatbox can pull the posts for that user's box.

 

On my home page I was thinking of having

 

 <?php 

$table = <<<TABLE
<table align="center">
<tr>
<td colspan="1"><b>Top chatbox with $posts posts</b></td>
</tr>
<tr>
<td>
<img src="user/$user.gif">
</td>
</tr>
<tr>
<td>
<a href="chatbox.php5?uid=$uid">Comment $user</a>
</td>
</tr>
</table> 
TABLE;

echo $table;
?>

 

Where $posts is going to be the number of posts;

$user is the username of the user_id with the most posts;

and $uid is the user_id of that user.

 

How would I structure a SQL statement to calculate which user_id in tblcomments has the most posts for it? and to pull the user_id and username from that?

 

Thanks,

Sam

 

[rofl90]

count all the users then you could use an if statement using <> to like make almost a high-score table.

[revraz]

You should remove the redundant username field under bltcomments, since you can get it from tbluser from the user_id.

[helraizer]

Ah, I see why that's confusing, but they're not the same.

 

username in tbluser is the registered username for the user

 

and

 

username in tblcomments is the username of the person posting the comment, it's user submitted, so could be anything.

 

Sam