How to get a count of common rows in a column and display them?

Question

0.00/5 (No votes)

See more:

Hi,

I have a DB table that adds a username when a user is reported. Because many different users can report one user, I want to get a count of how many times the username appears in the database table column and echo it next to their username.

PHP

if (count($results) > 0){
				//echo table
				echo '<table>';
				echo '<tr><th>Username</th><th>Reports count</th></tr>';
				$rows = $output -> fetchAll();
				foreach ($rows as $row){
					$username = $row['reportedUser'];
					echo "<tr>\n";
					echo "<td>".$username."</td>";
					$reportsQuery = "SELECT reportedUser COUNT(*) AS count FROM duk_reports WHERE reported = :username";
					$reportsOutput = $db->prepare($reportsQuery);
					$reportsOutput->bindParam(':username', $username);
					$success = $reportsOutput->execute();
					echo "<td>".$reportsOutput['count']."</td>";
					echo "</tr>";
				}
			}

I tried this but this doesnt work. Am I close to getting this to work?

Any help greatly appreciated!

Posted 1-Apr-15 12:17pm

jba1991

Add a Solution

1 solution

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)

Sascha Lefèvre · Accepted Answer · 2015-04-01T12:31:00

Solution 1

Here the reportedUser makes no sense:

PHP

$reportsQuery = "SELECT reportedUser COUNT(*) AS count FROM duk_reports WHERE reported = :username";

If it is correct that the name of the column that contains the user name is "reported" then this should work:

PHP

$reportsQuery = "SELECT COUNT(*) AS count FROM duk_reports WHERE reported = :username";

Edit: I'm actually not sure if your database will allow you to call your result "count" because it's a keyword - you may have to call it differently:

PHP

$reportsQuery = "SELECT COUNT(*) AS countReported FROM duk_reports WHERE reported = :username";

Edit 2: $reportsOutput is of type PDOStatament which is a class and not an array. Therefore you can't call the [] operator on it.

To access the query result try this:

PHP

$success = $reportsOutput->execute();
$results = $reportsOutput->fetch(PDO::FETCH_ASSOC);
echo "<td>".$results['countReported']."</td>";

cf. http://stackoverflow.com/questions/15759223/cannot-use-object-of-type-pdostatement-as-array[^]

Posted 1-Apr-15 12:31pm

Sascha Lefèvre

Updated 2-Apr-15 8:29am

v3

Comments

jba1991 2-Apr-15 13:36pm

I tried this:

$reportsQuery = "SELECT COUNT(*) AS countReported FROM reports WHERE reportedUser = :username";
$reportsOutput = $db->prepare($reportsQuery);
$reportsOutput->bindParam(':username', $username);
$success = $reportsOutput->execute();
echo "<td>".$reportsOutput['countReported']."</td>";

but got this Error:

Fatal error: Cannot use object of type PDOStatement as array

Sascha Lefèvre 2-Apr-15 14:30pm

I updated my answer above, please take a look.

jba1991 4-Apr-15 16:03pm

Thanks that worked! I have a follow up:

How can I get just one username instead of the amount in the table? (e.g. user1 having 3 reports in the table shows in the table for the code above 3 times)

Sascha Lefèvre 4-Apr-15 16:56pm

You're welcome, glad I could help!
Please be so kind and mark my solution as accepted :)

Regarding your follow-up-question: As far as I can interprete what you're doing there, the reason is probably that $rows already contains the same username multiple times.

jba1991 4-Apr-15 16:59pm

Yeah it does because it adds a report everytime, rather than adding a count. I am aware I could use multiple tables to do this, but I really just want to use 1. Is there a way of getting round this?

Sascha Lefèvre 4-Apr-15 17:11pm

Can $rows contain different user names?

$rows appears to be the result of a previous database-query. Maybe you simply have to use a DISTINCT in that query.

jba1991 7-Apr-15 7:01am

That sounds correct, I just checked out DISTINCT and that should work. Many many thanks for you help!

Sascha Lefèvre 7-Apr-15 7:14am

You're welcome, call again :)