2 replies to this topic

[Bertan]

When executing this code the both counts is added together showing the result of $submissions + $comments. I want to show the result of $submissions and $comments seperated from each other.

For example if submissions is 2 and comments is 1 then submissions should show 2 and comments 1. Now both of them are showing 3.

What am I doing wrong?

$query = "SELECT u.user_prestige, u.user_name, COUNT(c.comment_id) AS NumberOfComments, COUNT(c.image_id) AS NumberOfsubmissions

		  FROM users u 

		  LEFT JOIN images i ON i.user_id = u.user_id

		  LEFT JOIN comments c on c.user_id = u.user_id

		  WHERE u.user_id = '$user_id'";

$result = mysql_query($query);

$user = mysql_fetch_array($result);

$submissions = $user['NumberOfsubmissions'];

$comments = $user['NumberOfComments'];

[SoN9ne]

Well it looks like it is counting all the records for that user id. You may want to add an AND clause to the WHERE to restrict the query to be tied to the specific ID of the topic.

it also looks like the

COUNT(c.image_id) AS NumberOfsubmissions

should be

COUNT(i.image_id) AS NumberOfsubmissions

[Bertan]

I got it working using DISTINCT inside the count, and yes that was a typo thanks for that :)