我不知道如何编写SQL语法以获取最后一条记录(根据最近的帖子并且没有回复)。
我的表格:
+-------------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-----------------------+------+-----+---------+----------------+
| notification_id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| user_id | mediumint(8) unsigned | NO | | NULL | |
| notification_msg | text | NO | | NULL | |
| notification_date | int(11) unsigned | NO | | NULL | |
| private_message | tinyint(1) unsigned | NO | | 0 | |
| has_replied | tinyint(1) unsigned | NO | | 0 | |
| reply_id | mediumint(8) unsigned | NO | | 0 | |
+-------------------+-----------------------+------+-----+---------+----------------+
基本上,对于每个线程通知,它应该获取每个通知记录的最后一条记录,并检查has_replied
是否为0
,如果为0,则应将其返回,以便PHP可以读取是否存在未回复的通知。因此,它应该像这样返回(伪代码):
+--------------+-----+-----+
| username | 1 | 4 |
| username2 | 0 | 2 |
+--------------+-----+-----+
第二列代表最后一篇帖子是否已回复。
我的当前SQL语法(可以工作但无法获取最后一条记录及其回复情况):
SELECT n.*,
m.user_id,
m.username
FROM notifications n
INNER JOIN members m ON n.user_id = m.user_id
WHERE private_message = 1
AND reply_id = 0
ORDER BY has_replied ASC,
notification_date DESC
:)
- Oscar Mederos