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
>Solution :
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 = ?";