How to verify of notifications data before showing them to user PHP MySQL-CodePudding

Frist I have table of general notifications this table have three field ID Body Date and from my app flutter any message send to users save this message in this table. Now the problem is, the message goes to all users, so I'm trying to make a way to find out which users have seen this message from others. Now I make another table to save userID and notificationID.So if Userid and NotificationsID in IsWatchedNotifications table this means message has been viewed and if not have data then this means message not viewed to now.

Now I have problem with that query how I can write it to work like that? this is my query line to now:

$sql = "SELECT * FROM Notifications 

LEFT JOIN IsWatchedNotifications ON Notifications.NotificationsID = IsWatchedNotifications.NotificationsID

where  ";

Full code:

<?php



$UserID= $_GET['UserID'];

$sql = "SELECT * FROM Notifications 

LEFT JOIN IsWatchedNotifications ON Notifications.NotificationsID = IsWatchedNotifications.NotificationsID

where  ";


$stmt = $con->prepare($sql); 

$stmt->bind_param("s",$UserID);

$stmt->execute();

$result = $stmt->get_result();
 
if ($result) {
 
 
     while($row[] = $result->fetch_assoc()) {
     
     $item = $row;
     
     $json = json_encode($item, JSON_NUMERIC_CHECK);
     
     }
 
} else {

}
 echo $json;
$con->close();

?>

Anyone can help me Thank you

CodePudding user response：

assumeing that your column name is user_id

You need only to add the condition with placeholder

$sql = "SELECT * FROM Notifications 
        LEFT JOIN IsWatchedNotifications ON Notifications.NotificationsID = IsWatchedNotifications.NotificationsID
        where user_id = ?";