How to Prevent Multiple Votes in a Comment Rating System Without Storing Arrays in MySQL?

Storing Arrays in MySQL: An Alternative Approach

To prevent multiple votings in a comment rating system, you wish to store an array of user IDs in a MySQL field. While this is not directly supported by MySQL, consider the following alternative approach:

Database Schema

Create three tables:

• comments: Contains comment IDs and bodies.
• users: Stores user IDs and usernames.
• comments_votes: Links comments to users who have voted on them, including the vote type.

CREATE TABLE comments (
    comment_id int, 
    body varchar(100), 
    PRIMARY KEY (comment_id)
);

CREATE TABLE users (
    user_id int, 
    username varchar(20), 
    PRIMARY KEY (user_id)
);

CREATE TABLE comments_votes (
    comment_id int, 
    user_id int, 
    vote_type int, 
    PRIMARY KEY (comment_id, user_id)
);

Example Data

INSERT INTO comments VALUES (1, 'first comment');
INSERT INTO comments VALUES (2, 'second comment');

INSERT INTO users VALUES (1, 'user_a');
INSERT INTO users VALUES (2, 'user_b');

INSERT INTO comments_votes VALUES (1, 1, 1);
INSERT INTO comments_votes VALUES (2, 1, 1);

Checking for User Eligibility

To determine if a user has already voted on a particular comment, use a simple query:

SELECT COUNT(*) FROM comments_votes WHERE comment_id = <comment id> AND user_id = <user id>;

This approach prevents multiple votes as the primary key constraint on the comments_votes table ensures uniqueness.

Preventing Multiple Votes

If the user has already voted, disable the voting icons in the system logic based on the query result.

Advantages of This Approach

• Ensures data integrity through foreign key constraints.
• Avoids the need to serialize arrays into a single field.
• Simplifies database operations and reduces the risk of data corruption.

The above is the detailed content of How to Prevent Multiple Votes in a Comment Rating System Without Storing Arrays in MySQL?. For more information, please follow other related articles on the PHP Chinese website!