How to implement comment notification?

Question

I am trying to implement notifications on comments, that is - if I comment on a post and another user comments on the same post, I'll get a notification. I've handled the notification for the post owner using this query

SELECT id, owner_id, post_id, user_id2, COUNT(user_id2) AS num, type, UNIX_TIMESTAMP(date_done) AS date 
FROM notification
WHERE owner_id = '$user_id' AND user_id2 != '$user_id' 
ORDER BY date_done DESC

notification table

In the table above, owner_id is the person that owns the post, the user_id2 are those that commented on the post.

How do I tell the user with id 17 that two persons (with user id 2 and 1) also commented on the post?

If I should use a second table, please tell me how the structure should be. Thanks

You need something to ID the post don't you? The query you've pasted above only selects from the database by user_id. — Pete
– Pete, Commented Apr 11, 2012 at 10:46
how do you know notification is read , and to not show it again ? — safarov
– safarov, Commented Apr 11, 2012 at 10:47
@Pete, the post has a unique ID already. The query I posted isn't really the challenge now, but the query to send notification to other users that commented on the same post. — Chibuzo
– Chibuzo, Commented Apr 11, 2012 at 10:49
@safarov, I've thought of that but I want to get the notifications first. However, there's a remove notification link on each displayed notification, so the owner can remove the notification if they want. — Chibuzo
– Chibuzo, Commented Apr 11, 2012 at 10:51
@Chibuzo removing notification is not correct. For example if user 17 read notification but user 2 dont. And user 2 will never been notifed — safarov
– safarov, Commented Apr 11, 2012 at 10:53

Pete · Accepted Answer · 2012-04-11 11:09:57Z

3

The following query gets the user(s) who have commented

SELECT   DISTINCT user_id2 
FROM notification
WHERE post_id = '$post_id'

But you don't want to notify the person currently posting so...

SELECT   DISTINCT user_id2 
FROM notification
WHERE post_id = '$post_id'
AND user_id2 != '$user_id'

Or am I missing something?

answered Apr 11, 2012 at 11:09

Pete

1,30910 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vel · Accepted Answer · 2024-07-04 09:26:17Z

1

I suggest separating posts and comments into separate tables with a one to many relation

post {
  id,
  user_id,
  content,
  date
}


comment {
  id, 
  post_id, 
  user_id,
  comment,
  date
}

Then you can simply poll your comments via a timeout ajax call. If comments have been added by a user_id that's not your, inject a notification into the page.

edited Jul 4, 2024 at 9:26

Vel

9,4286 gold badges42 silver badges71 bronze badges

answered Apr 11, 2012 at 11:09

Lee Davis

4,7763 gold badges31 silver badges40 bronze badges

4 Comments

Chibuzo Over a year ago

The post and comments each has a table just as you suggested. I want other users that comments on the post to also get a notification, not only the post owner.

Lee Davis Over a year ago

Sure so whenever any user is on the post page, you poll for any comments submitted for that post. And if there are any, inject a notification.

Chibuzo Over a year ago

I get your point, but the situation is that I want the users to get the notifications on their user page and then a link to the post page. Thanks anyway.

Lee Davis Over a year ago

So you poll for all comments on any post that user has created (or commented on).

stack overflow fan · Accepted Answer · 2016-06-09 04:00:57Z

This class that I created will give you the detail. I have done what you need to do with the method 'notify_chain'. You will need to see the other functions, so I will post the whole class for you here.

<?php 


class Notification extends DatabaseObject{

public $id;
public $type;
public $post_id;
public $owner_id;
public $user_id2;
public $date_done;
public $read_or_not;
public $notifications;
public $message;
public $count;

protected static $table_name="notifier";
protected static $db_fields = array('id', 'type', 'post_id', 'owner_id', 'user_id2', 'date_done', 'read_or_not');


public function check_notification(){
    global $database;
    global $session;
    if($session->is_logged_in()) { 
    $id=$session->user_id;

    $sql = "SELECT * FROM notifier WHERE owner_id={$id} AND read_or_not = 0";
    $chk = $database->query($sql);
    if(!empty($chk)){
        while($notifs = $database->fetch_assoc($chk)){
            $message="";
        $this->notifications[]  = $notifs;
        $this->id                           = $notifs['id'];
        $this->type                         = $notifs['type'];
        $this->post_id                  = $notifs['post_id'];
        $this->owner_id                 = $notifs['owner_id'];
        $this->user_id2                 = $notifs['user_id2'];
        $this->date_done                = $notifs['date_done'];
        $this->read_or_not          = $notifs['read_or_not'];
            foreach($notifs as $notif){
                $user=Member::get_name($this->user_id2, false);
                if($this->type=="comment"){
                    $message = "<a href='post.php?post=".$this->post_id."&ntf_clr=".$this->post_id."'>".$user." commented on your post.</a>";
                } elseif($this->type=="reply"){
                    $message = "<a href='comment_reply.php?c_id=".$this->post_id."&ntf_clr=".$this->post_id."&cmt=Comment&rpl=CommentReply'>".$user." replied to your comment.</a>";
                } elseif($this->type=="discussion_comment"){
                    $message = "<a href='discussion.php?discussion=".$this->post_id."&ntf_clr=".$this->post_id."&cmt=Comment&rpl=CommentReply'>".$user." Commented on your discussion.</a>";
                } elseif($this->type=="discussion_reply"){
                    $message = "<a href='comment_reply.php?c_id=".$this->post_id."&ntf_clr=".$this->post_id."&cmt=Comment&rpl=CommentReply'>".$user." replied to your discussion comment.</a>";
                } elseif($this->type=="article_comment"){
                    $message = "<a href='article.php?article=".$this->post_id."&ntf_clr=".$this->post_id."'>".$user." Commented on your article.</a>";
                } elseif($this->type=="article_reply"){
                    $message = "<a href='comment_reply.php?c_id=".$this->post_id."&ntf_clr=".$this->post_id."&cmt=ArticleComment&rpl=ArticleCommentReply'>".$user." replied to your article comment.</a>";
                } elseif($this->type=="chain_comment"){
                    $message = "<a href='post.php?post=".$this->post_id."&ntf_clr=".$this->post_id."'>".$user." also commented to a post.</a>";
                } elseif($this->type=="chain_reply"){
                    $message = "<a href='comment_reply.php?c_id=".$this->post_id."&ntf_clr=".$this->post_id."&cmt=Comment&rpl=CommentReply'>".$user." also replied to a comment.</a>";
                } 
            }
            $this->message = $message_array[] = $message;
        } if(isset($message_array)){return $message_array;}         
        } else{
            return false;
        }
        }   
        }

        public static function mark_as_seen($notif_id){
            global $database;
            global $session;
            $me = $session->user_id;
            $sql = "UPDATE `notifier` SET read_or_not=1 WHERE owner_id = {$me} AND post_id={$notif_id}";
            $clear_notification = $database->query($sql);
        }

        public static function make_notification($post_id, $owner_id, $user_id2, $type="comment"){
            $notif = new Notification();
            $notif->type=$type;
            $notif->post_id=$post_id;
            $notif->owner_id=$owner_id;
            $notif->user_id2=$user_id2;
            $notif->date_done=strftime("%Y-%m-%d %H:%M:%S", time());
            $notif->read_or_not=0;
                $notif->create();
        }


        public static function notif_count($id){
        global $database;
        $notif_cnt = $database->query("SELECT COUNT(*) as notifs FROM notifier WHERE owner_id={$id} AND read_or_not = 0");
        $data=$database->fetch_array($notif_cnt);
        return $data['notifs'];
        }        

        public static function notify_chain($post_id, $user_id2, $type="chain_comment"){
        global $database;
        global $session;
            $sql = "SELECT DISTINCT user_id2 ";
            $sql .= "FROM notifier ";
            $sql .= "WHERE post_id = {$post_id} ";
            $sql .= "AND user_id2 != {$session->user_id} ";
            $chain = $database->query($sql);
        while ($users = $database->fetch_assoc($chain)){
            $list = "";
            foreach ($users as $user){
                Notification::make_notification($post_id, $user, $user_id2, $type="chain_comment");
                $list = $user;
            }
            $list_array[] = $list;
        } return $list_array;

        }

}

$notif = new Notification();

?>

I hope that helps. check out maestrojosiah PG at facebook if you have any problems.

The code you've shared is highly vulnerable for SQL injections and XSS attacks and should never be used in any application like that

Collectives™ on Stack Overflow

How to implement comment notification?

3 Answers 3

Comments

4 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

1 Comment

Related