How can I increase the value of a row (INT) every time someone reports a user?

Question

0.00/5 (No votes)

See more:

I am trying to create a user based website, where other users can report users that misbehave.

I have a database table for users and one of the columns is a count of the amount of times they have been reported (default value 0). As a user is reported by others, the value in this column goes up, for example, if 3 separate users report user A, user A's report count would be 3.

My question is:

What would be the best way to do this?

Many thanks,

Joel.

Posted 13-Jan-15 9:00am

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)

Ravi Bhavnani · Accepted Answer · 2015-01-13T09:12:00

Solution 1

> What would be the best way to do this?

You may want to consider simply adding a row to the Report table when a report is made. Counting the rows associated with a specific user will give you the count of reports. This has the added benefit of retrieving counts for a specific period, or by a specific user, or both.

/ravi

Posted 13-Jan-15 9:12am

Ravi Bhavnani

Comments

PIEBALDconsult 13-Jan-15 15:31pm

Yes, that is the correct way.

Santosh K. Tripathi 13-Jan-15 23:34pm

this is the correct, way go with it.

jba1991 14-Jan-15 9:04am

Thanks very much Ravi,

Sorry for such a vague question, but I didnt even know what to search Google for so came here.

Ravi Bhavnani 14-Jan-15 9:08am

No apology needed. We're here to help (if we can).

Cheers,

/ravi

jba1991 14-Jan-15 9:09am

If I create the reports table and have userID and reports as columns, would I join the user table and reports table on userID? Meaning that each report in the reports table will be associated with a userID and from there would I count the amount of rows where userID = XX?

Ravi Bhavnani 14-Jan-15 10:27am

The Reports table will have columns:
- ReportId (identity)
- ReportingUserId (FK to User.UserId)
- TargetUserId (FK to User.UserId)
- ReportText (not null)
- TimeCreated

This will let you query the Reports table for reports by/for a specific user and/or reports issued within a certain time frame and/or reports containing specific text.

/ravi

jba1991 14-Jan-15 10:56am

Thanks!

Im having another problem that you may be able to help me with.

Currently I have a basic function where the reports table works and contains the userID and the username, each row showing who was reported (ive havent yet added the date created and report text but I will).

On another page (admin page), I want to list all of the users in a table and sort it by the amount of reports a user has in the reports table. I have listed the userID and username from the users table but im unsure how I can connect the amount of reports from the reports table, to the information from the users table.

This is what I have so far:

$db = Database::getConnection();
//select only users with activated accounts to display
$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>Report Count</th></tr>";
foreach($results as $result){
echo "<tr>\n";
echo "<td>$result[userID]</td>";
echo "<td>$result[username]</td>";
//select data from reports page
$displayReports = "SELECT * FROM fh_reports";
$reportCount = $db->prepare($displayReports);
$reportCount->execute();
//display report count next to respective username from above
echo "</tr>\n";
}
}