How to properly organize the rating of records?

Question

PHP

Help with advice on how to properly organize the table for the rating of entries and comments.
There is a record, a person can make + or -, it is necessary that a person can re-vote if he needs it, hence it follows that his choice must be entered into the database.
I am very worried about the workload, when checking what I voted for and what I chose.
At the moment, when voting, I have an overall post rating that works like this
overall rating + current
and there is a line with user id separated by commas
If you enter the value +1 -1 separated by a comma into another line, then how demanding will the cycle be if the entry 10 comments and 500 upvotes ?
Or how to properly organize a table with selected user values?

Answer 1

I am also for the generalization of rating objects.
That is, let's say you have an Article and a Comment . Let's introduce a RatingEntity object with the resulting rating and add a 1-to-1 relationship with it for Article and Comment ratingEntityId . So the tables will look like this:

rating_entities
------------
id | rating_sum

articles
------------
id | rating_entity_id | ...

comments
------------
id | rating_entity_id | ...

Next, we make a Rating like this:

ratings
------------
id | rating_entity_id | user_id | value

where there will be a composite key (rating_entity_id + user_id), and value will have values ​​+/- 1.
As for performance, I don’t see a problem. The fact is that when voting, it is obvious that you need to show the current rating +/- 1, which can be done using the frontend. That is, you can send a request to the server and not wait for a response.
You can update the resulting rating through a trigger in the database.

Answer 2

comments
comment_id, user_id, text, rate_count, rate_sum......
users
user_id, name ....
ratings
id, comment_id, user_id, value (1, -1)
SELECT *, (rate_sum/rate_count) as rate FROM comments WHERE article_id=1
check if a person voted according to the ratings table
when adding a rating to the table, recalculate the rating for this comment and record the number of voters and the total rating in the comment table. or immediately the final digit of the rating

Answer 3

The first thing that came to mind
You must have some kind of common id type for all entities that you can vote for, then you need to store in the user the path to the list of all entities for which he voted, of course with a value of 0:1
then at the moment loading a list of comments and messages for a particular user, you can refer to the list and specify whether this user can vote / re-vote.
Otherwise, there is a general value attached to a comment or a post or something else.

Answer 4

PDO

php.net/pdo

Answer 5

Сегодня в любом случае стоит использовать PDO (или уже какие-то обёртки поверх PDO), вроде Query-builder-ов.
От большей части инъекций избавляет подготовка запроса. На примере PDO:

$stmt = $dbh->prepare("'SELECT * FROM users WHERE id = ? OR email = ?');
$result = $stmt->execute(array($_GET['id'], $_GET['email']));

Также можно использовать именованные параметры, тогда используйте асоциативный массив и параметры вроде :email и :id.
Почитать можно тут.

Answer 6

юзаем PDO подготовленные запросы, фильтруем все входящие данные параметры в запрос. Для фильтрации параметров хорошо использовать небольшой файлик со списком команд, которые нельзя использовать и проверять параметр на их наличие.
И опять же, даже если и до чего то, смогут добраться на сайте, всегда работайте на сайте под отдельным пользователем с самыми урезанными правами, конечно это тоже конечно не дает полной защиты, однако продвинутые школьники и студенты уже заступорятся и бросят это занятие :)