CODEWITHSUNDEEP
phpsqlajaxpdo
Zanic L3
Zanic L3

Reputation: 1088

SQL/PHP - Check if user already liked post and if yes unlike?

So I have these tables.

Table: Likes

user_id : The user that logged in

post_id : The article where the user clicked like.

enter image description here

 

Then I have a Like.class.php which stores the getters and setters and the functions.

I have two right now, Addlike(); which enters the id (auto increment) and the user_id and post_id of the article/posts he liked.

Countlike(); counts the entries with a simple rowCount(); of all the rows.

public function Addlike($postid){

        $conn = db::getInstance();
        $query = "insert into likes (post_id, user_id) values (:post_id, :user_id)";
        $statement = $conn->prepare($query);
        $statement->bindValue(':post_id',$postid);
        $statement->bindValue(':user_id',$this->getUserId());
        $statement->execute();
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        return $result;
    }

    public static function Countlike(){
        $conn = db::getInstance();
        $statement = $conn->prepare("SELECT * from likes");
        $statement->execute();
        $likecount =  $statement->rowCount();
        return $likecount;
    }

 

My question: How can I now check if the user already liked the post (out of the DB) and if he presses like again it should remove his entry from the DB, any ideas on how I can turn this into code? Also the likes need to be bonded to the posts for example, every post has it own like counter/number

Upvotes: 1

Views: 1409

Answers (2)

Yura Rosiak
Yura Rosiak

Reputation: 255

You can check it in the DB

SELECT COUNT(*) FROM likes WHERE post_id=:post_id AND user_id=:user_id

Another way is to write into COOKIES or better Web Storage id of posts liked by user.

More into code :

private function Addlike($postid){
    $conn = db::getInstance();
    $query = "insert into likes (post_id, user_id) values (:post_id, :user_id)";
    $statement = $conn->prepare($query);
    $statement->bindValue(':post_id',$postid);
    $statement->bindValue(':user_id',$this->getUserId());
    $statement->execute();
}
private function Deletelike($postid){
    $conn = db::getInstance();
    $query = "DELETE FROM likes WHERE post_id = :post_id AND user_id =:user_id";
    $statement = $conn->prepare($query);
    $statement->bindValue(':post_id',$postid);
    $statement->bindValue(':user_id',$this->getUserId());
    $statement->execute();
}

public function CheckLike($postid){
    $conn = db::getInstance();
    $query = "LECT COUNT(*) FROM likes WHERE post_id=:post_id AND user_id=:user_id";
    $statement = $conn->prepare($query);
    $statement->bindValue(':post_id',$postid);
    $statement->bindValue(':user_id',$this->getUserId());
    $statement->execute();
    $result = $statement->fetchAll(PDO::FETCH_ASSOC);
    if($result["COUNT(*)"] == 0){
        $this->Addlike($postid);
    }else{
        $this->Deletelike($postid);
    }
    return $result;
}

Upvotes: 1

Kaddath
Kaddath

Reputation: 6151

As i said in the comment, the simplest in term of SQL is to use 2 separate queries. For your use case, this should be allright, the query doesn't deal with multiple rows/values.

There actually should be some verifications after your queries too, in case you have a SQL error, i haven't added them.

NOTE: in good practices, names beginning by capitals are usually reserved for Classes, functions are commonly in camel-case like in the following:

public function delLike($postid){
    $conn = db::getInstance();
    $query = "SELECT id from likes WHERE post_id=:post_id AND user_id=:user_id";
    $statement = $conn->prepare($query);
    $statement->bindValue(':post_id',$postid);
    $statement->bindValue(':user_id',$this->getUserId());
    $statement->execute();
    $result = $statement->fetchAll(PDO::FETCH_ASSOC);
    //there should be only one result at this point
    if(!empty($result)){
        $query = "DELETE from likes WHERE id=:id";
        $statement = $conn->prepare($query);
        $statement->bindValue(':id',$result[0]['id']);
        $statement->execute();
        return true;
    }
    return false;
}

As a side note, you could use a single query with combining select, delete and exists, you have examples here

Upvotes: 0

Related Questions