0

I've created a notifications system / alert system for my website where as an admin i can send notifications to my users.

When i had started it, i created a column called comment_seen and when I inserted a new comment / alert and it was 0, i'd use ajax / jquery to check and then it would notify the users they had a new notification, when they click on it, it would then change the '0' to a '1', i then noticed that if one user looks at it, globally no one else would then know they have a notification as i was just using one table.

I then created a new table called alertsseen, here when a user views their notifications it now adds to the table the alertID and the userID.

My code at the moment that checks for new notifications is 

$status_query = "SELECT * FROM comments WHERE comment_status=0";
$result_query = mysqli_query($con, $status_query);
$count = mysqli_num_rows($result_query);

$data = array(
'notification' => $output,
'unseen_notification' => $count
);

I am wondering if there is a way / query to first check the comments table and then check the alertseen table, and if the id of the comment / alert is not in the alertsseen table for that user, it will show it as a new notification in $count.

Any help appreciated, hopefully this made sense.

John Martyn
  • 61
  • 6
  • Couldn't you just make use of two separate queries? First query the comment ID, then run a second query to check if that ID is in the `alertseen` table. The final component is just a simple PHP conditional. – Obsidian Age Nov 22 '17 at 19:58
  • I would ditch the alertseen table and just add a new column to the users table. Store the ID of the newest notification each user has seen, then query the notifications table for any with ID > newest notification that user has seen – miknik Nov 22 '17 at 20:43

0 Answers0