Count with Inner Join

[bphein1980]

I have 2 tables that I need to Inner Join and then count how many duplicates of a record I have.

Here is the set up

Table 1
Username / Post
------------------
user1 / POST1
user1 / POST2
user4 / POST3
user5 / POST4
user4 / POST5

Table 2
Username / Reply
------------------
user2 / REPLY1
user2 / REPLY2
user3 / REPLY3
user1 / REPLY4
user1 / REPLY5

I am trying to count how many posts and replies each username has. Such as:


user1 = 4
user2 = 2
user3 = 1
etc...

I am stuck on this. I have researched using google and such, found some example, but cant seem to get them implemented correctly. Here is was I have so far, but it is coming out totally wrong. There are at least 3 usernames listed between the 2 tables and it is only displaying 1 with a wrong count. What am I doing wrong?

$query = mysql_query("
SELECT forum_reply.username, count(forum_reply.username)
AS userc FROM forum_reply
INNER JOIN forum_posts ON forum_reply.username = forum_posts.username
GROUP BY forum_reply.username");
while($r = mysql_fetch_array($query)){
echo $r['username']." ".$r['userc']."<br/>";
}

[bphein1980]

I have found that if the the username is not is both tables it is causing the username to not show up in the list. How can that be corrected. The count is still wrong too.

For example, one username has 6 posts and 4 replies, but the count is showing 24... ????

Edit: It seems that it is multiply the numbers. I deleted one of the posts from the user and now the count is 20. Why is the count multiplying and not adding??

[harsh789]

try changing INNER JOIN with LEFT JOIN.

[bphein1980]

Thank you for the reply.

I am still encountering pretty much the same trouble.

Problem 1:
The first poster (where id=1) is still having the posts multiplied by the replies. All others are calculating correctly.

Problem 2:
If the username is not listed in both tables, the username is not added on the list.

[guelphdad]

what version of mysql are you using?

[bphein1980]

MySQL 4.0.22


Here are the 2 statements that work individually, I just cannot seem to combine them to come up with a total...

$query_posts = mysql_query("SELECT username, COUNT(username) AS postsc FROM forum_posts GROUP BY username");
while($row = mysql_fetch_array($query_posts)){
echo $row['username']." ".$row['postsc']."<br/>";
}

$query_reply = mysql_query("SELECT username, COUNT(username) AS replyc FROM forum_reply GROUP BY username");
while($r = mysql_fetch_array($query_reply)){
echo $r['username']." ".$r['replyc']."<br/>";
}

[guelphdad]

then total those variables together in php using variables in php to hold the sum of the two totals.

Other than that you are s.o.l. because your host uses an old version of mysql.

[bphein1980]

OK. Thanks for info. :thumbsup: