How do I list a count from a seperate table next to users in another table?

Question

4.00/5 (1 vote)

See more:

Hi,

I have a users table displayed on a page with their userID and username.

I want to get a count of usernames from a seperate table (Reported Users table) and list the count alongside their respective username and userID.

In the reported table I have a userID and username, and for every time a user is reported, their username is added to the reported users table. I know how to get a count from the reported table but im unsure of how to list the amount of times their username appears in the reported users table next to their username from the users table.

PHP

$db = Database::getConnection();
				$query = 'SELECT * FROM users WHERE activated=1';
				$output = $db->query($query);
				$results = $output->fetchAll(PDO::FETCH_ASSOC);
				if (count($results) > 0) {
					echo "<table cellspacing=\"0\">";
					echo "<tr><th>UserID</th><th>Username</th><th>Reported</th></tr>";
					foreach($results as $result){
						echo "<tr>\n";
						$username = $result['username'];
						echo "<td>$result[userID]</td>";
						echo "<td>$result[username]</td>";
						$reportsQuery = "SELECT reported COUNT(*) FROM reports WHERE reported = '$username'";
						$reportsOutput = $db->query($reportsQuery);
						while ($row = $reportsOutput->fetch(PDO::FETCH_ASSOC)){
						echo '<td>Report count</td>';
						}
						echo "</tr>\n";
					}
				} else {
					echo 'There are no active users!';
				}
				echo "</table>";?>

Posted 3-Feb-15 1:45am

jba1991

Add a Solution

Comments

Zoltán Zörgő 3-Feb-15 7:58am

1) LEFT JOIN
2) subqquery

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2015-02-03T01:58:00

You could try something like this to get all of the details back in a single query

SQL

SELECT U.* , ISNULL(R.reportedCount,0) AS reportcount
FROM users U
LEFT OUTER JOIN (SELECT reported, COUNT(*) AS reportedCount
                    FROM reports GROUP BY reported)  R on U.username=R.reported
WHERE U.activated=1

**Zoltán Zörgő** · Accepted Answer · 2015-02-03T02:02:00

Solution 2

Option 1

SQL

SELECT username, userID, count(reports.reported) as numreports FROM 
users LEFT JOIN reports ON users.username=reports.reported
WHERE activated=1 
GROUP BY username, userID

Option 2:

SQL

SELECT username, userID, 
(select count(*) from reports WHERE users.username=reports.reported) as numreports
FROM users 
WHERE activated=1

Posted 3-Feb-15 2:02am

Zoltán Zörgő

Updated 3-Feb-15 3:41am

v2

Comments

CHill60 3-Feb-15 9:39am

Caution - Option 1 will always return a minimum count of 1 as numreports because the group by causes a count of everything on the left table (users)

Zoltán Zörgő 3-Feb-15 9:41am

True, updated