CODEWITHSUNDEEP
mysqlsql
Broak
Broak

Reputation: 4187

User 'reputation system' - Checking if a user has already +1 -1'd someone?

CREATE TABLE IF NOT EXISTS `User_Rep` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `USER_ID` int(11) NOT NULL,
  `REPPER_USER_ID` int(11) NOT NULL,
  `REP` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
)

This is my current table, please ignore the missing foreign key for the user_id's, just not added them for brevity.

When a user decides to rep someone, a new record is added to this table, i.e.

INSERT INTO `User_Rep` (`ID`, `USER_ID`, `REPPER_USER_ID`, `REP`) VALUES
(1, 72, 3, 1),
(2, 72, 3, 1),
(3, 72, 3, -1);

And i can pull any particular users rep with the following query:

SELECT SUM(REP) FROM User_Rep WHERE USER_ID = 72

However, i'm struggling to devise the appropriate query to use to ascertain whether a particular user has already upvoted or downvoted a user, such that the UI can respond accordingly and perform the relevant action (up vote or down vote)

What happens when a user upvotes, downvotes, upvotes again, etc... and there is now multiple of those rows in the table? How could i find only the relevant True/False?

Any assistance with this?

Upvotes: 0

Views: 121

Answers (1)

Karl M.W.
Karl M.W.

Reputation: 747

The first is to change your table. I would delete the existing ID column and create a UNIQUE index on the USER_ID and REPPER_USER_ID fields. This will prevent the database from allowing duplicates in the first place, and will also vastly speed up the next part.

Then in code so you can prevent the vote function/button from being available:

SELECT 1 FROM `User_Rep` WHERE `USER_ID`=72 AND `REPPER_USER_ID`=3

To get the actual vote from 3 to 72, it would be:

SELECT `REP` FROM `User_Rep` WHERE `USER_ID`=72 AND `REPPER_USER_ID`=3

But be careful how you use that in code... if you do a simple "if true" test you might get -1 or 0, and then any true/false evaluation would return FALSE, even though it's set (zero and negative usually translates to "false".

Assign that to a property/variable in code, then you can then do your check in code to check for evaluation. Depends what you're using:

Python:

if has_voted:
    ...

PHP

if(has_voted==TRUE) {
    ...
}

Upvotes: 0

Related Questions