仅从数据库检索新数据 [英] Retrieve New Data Only From Database

问题描述

我正在开发一个Android应用程序，刺激投票通知功能。因此，我创建了一个服务，不断询问服务器的新数据。但是，我在我的数据库中使用了一个名为Seen的表。该表用于由服务器请求特定user_id，如果该表中不存在news_id，则它将产生通知。

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?

我想通过这个想法来解决它：当我第一次启动应用程序。插入在看到的表所有的新闻与该user_id为了得到0新的消息。但我认为这将是没有效率。

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.

这是我的数据库和我的PHP脚本

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_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()

查看表

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

这是我的PHP代码，它还显示我的查询获取没有显示的消息在Seen_news表中：

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);
?> 

假设我发送User_Id到PHP脚本，并基于结果Query将显示json文件。

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

推荐答案

如果您可以向用户表中添加create_date列，则可以选择新闻日期大于用户create_date的位置。

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.

如下：

"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']."')"

这篇关于仅从数据库检索新数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！