CODEWITHSUNDEEP
phpmysqlsqldatabaserelational-database
Dodo Dodo
Dodo Dodo

Reputation: 43

Retrieve New Data Only From Database

I am developing an android Application that stimulates a poll notification feature. Therefore, I Created a service that keeps asking the server for the new data. However, I've used a table in my database called Seen. This table is used to be asked by the server for a specific user_id and if the news_id didn't exists in that table it will produce a notification.

The problem is when i launch the application for the first time. It retrieves all the data from the database because the server considering the users didn't see the news.

If Anyone Can Help me?

I Thought to solve it by this Idea: when I launch the application for the first time. Insert in seen table all of the news with that user_id in order to get 0 new messages. But i think it will be Not efficient.

This is my Database and my PHP script

Users table

User_ID | User_Name
--------------------
1       | John
2       | Carl
3       | Tomas
4       | Adam
5       | Nancy

News Table

News_ID | News_Text      | news_date          
---------------------------
1       | Hello World    | CURRENTDATE()
2       | This is My car | CURRENTDATE()
3       | I had Ate pizza| CURRENTDATE()
4       | Leave Me Alone | CURRENTDATE()
5       | C++ Programming| CURRENTDATE()

Seen Table

ID   | User_Id  | News_Id
---------------------------
1    |  1       | 2
2    |  1       | 3
3    |  4       | 1
4    |  5       | 3
5    |  1       | 4

This is my PHP Code and it also showing my Query to get the news that didn't show in the Seen_news Table :

<?php

require('config.php');
$conn = mysqli_connect($servername, $username, $password, $db);
$query="SELECT * FROM news WHERE news_id NOT IN (SELECT news_id FROM news_seen WHERE user_id = '".$_GET['id']."')";

$result = mysqli_query($conn,$query);
$rows = array();
echo mysqli_error($conn);

while($row = mysqli_fetch_assoc($result)) { 
    $rows[] = $row;
}

echo json_encode($rows);
?>

Supposing that i am sending the User_Id to the PHP script and based on the result Query will show json file.

Upvotes: 0

Views: 458

Answers (1)

gmfm
gmfm

Reputation: 669

If you can add a create_date column to your Users table, you can select where Seen news_date is greater than User create_date.

Something like:

"SELECT * FROM news WHERE news_id NOT IN (SELECT news_id FROM news_seen WHERE user_id = '".$_GET['id']."') AND news_date > (SELECT create_date FROM user WHERE user_id = '".$_GET['id']."')"

Upvotes: 1

Related Questions