Limiting user to 1 comment per post in PHP and MYSQL-CodePudding

so i just have a simple app that a user can comment on. However, the user needs to be able to post just 1 comment on particular post.

This is the method i use to insert:

public function insertComment($user_id, $id, $comment)
{

    $sql = "INSERT INTO comments(user_id, post_id, comment_content)
            VALUES(:user_id, :id, :comment)";

    $sqlArr = [
        "user_id" => $user_id,
        "id" => $id,
        "comment" => $comment
    ];

    $stmt = parent::connect()->prepare($sql);

    if ($stmt->execute($sqlArr)) {
        echo "success";
    } else {
        echo "error";
    }
}

This does get the job done, but how can i limit the user to be able to comment only 1 for particular post, if he tries to get an error, for example:

if(commentExists()){
   echo "You have already commented!";
}else{
 //call insert method
 insertComment($user_id, $id, $comment);
}

CodePudding user response：

To limit a user to only one comment per post in PHP and MySQL, you can use a combination of a unique constraint on the comments table and a check for the existence of a comment in the insertComment() method.

First, you can add a unique constraint on the comments table to prevent duplicate entries with the same user ID and post ID. This can be done using the following SQL statement:

ALTER TABLE comments
ADD UNIQUE (user_id, post_id);

This statement will add a unique constraint on the user_id and post_id columns in the comments table. This means that any subsequent inserts with the same user_id and post_id values will fail.

Next, you can modify the insertComment() method to check for the existence of a comment with the given user_id and post_id values before inserting the comment. You can do this by adding a SELECT query to check if a comment with the same user_id and post_id exists, and only inserting the comment if it does not exist. Here is an example of how to do this:

public function insertComment($user_id, $id, $comment)
{
    // Check if a comment with the same user_id and post_id already exists
    $checkSql = "SELECT * FROM comments
                 WHERE user_id = :user_id AND post_id = :id";
    $checkStmt = parent::connect()->prepare($checkSql);
    $checkStmt->execute(["user_id" => $user_id, "id" => $id]);
    $commentExists = $checkStmt->fetch();

    if ($commentExists) {
        // If a comment already exists, return an error message
        return "You have already commented on this post!";
    } else {
        // If a comment does not exist, insert the comment
        $sql = "INSERT INTO comments(user_id, post_id, comment_content)
                VALUES(:user_id, :id, :comment)";
        $sqlArr = [
            "user_id" => $user_id,
            "id" => $id,
            "comment" => $comment
        ];
        $stmt = parent::connect()->prepare($sql);

        if ($stmt->execute($sqlArr)) {
            return "success";
        } else {
            return "error";
        }
    }
}

In this example, the insertComment() method first checks for the existence of a comment with the same user_id and post_id values using a SELECT query. If a comment already exists, an error message is returned. Otherwise, the comment is inserted using the original INSERT statement.

You can then call the insertComment() method in your code to insert a comment, and handle the returned value to display an error message if the user has already commented on the post. Here is an example of how to do this:

// Call the insertComment() method
$result = insertComment($user_id, $id, $comment);

// Check the result and display an error message if necessary
if ($result === "You have already commented on this post!") {