php – MY SQL查询线程消息传递收件箱并发送

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了php – MY SQL查询线程消息传递收件箱并发送，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4676字，纯文字阅读大概需要7分钟。

内容图文

我正在尝试在PHP和mySQL中创建线程消息传递系统.我的消息传递表如下

SELECT `es_id`, `es_fid`, `es_tid`, `es_subject`, `es_message`, `es_onstamp`, `es_msg_read`, `es_f_del`, `es_t_del`, `threadid` FROM `esb2b_messages`

在这张表中

es_id = primary key for the table.
es_fid = This field store the from user id.
es_tid = This field store the to user id.
es_subject = This field store the subject of message.
es_message = This field store the body of message.
es_onstamp = This field store the time stamp.
es_msg_read = This field store if receiver open the message.
es_f_del = This field store if the from user delete this message.
es_t_del = This field store the the to user delete this message.
threadid = This field store the id of parent message(es_id of the replied message).

请参阅此图片以清楚了解我想要存档的内容.

http://oi59.tinypic.com/2wdav45.jpg

在上图中,这4条消息的组合创建了一个线程.

对于收件箱和已发送项目应该是什么查询.现在我正在使用此查询收件箱,但每条消息都是独立的.

SELECT `es_id`, `es_fid`, `es_tid`, `es_subject`, `es_message`, `es_onstamp`, `es_msg_read`, `es_f_del`, `es_t_del`, `threadid` FROM `esb2b_messages` WHERE `es_tid`= UNHEX('$Loginuser') ORDER BY `esb2b_messages`.`es_onstamp` DESC

另请参阅此图片,了解现在输出的内容和我想要的内容.

请注意：收件箱应排序并仅显示在线程中收到的最后一条消息.应该仅对通过线程发送的最后一条消息进行排序和显示相同的已发送项.

SQL

CREATE TABLE IF NOT EXISTS `esb2b_messages` (
  `es_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `es_fid` bigint(20) NOT NULL DEFAULT '0',
  `es_tid` bigint(20) NOT NULL DEFAULT '0',
  `es_subject` mediumtext NOT NULL,
  `es_message` longtext NOT NULL,
  `es_tempdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `es_onstamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `es_msg_read` varchar(10) NOT NULL DEFAULT '',
  `es_f_del` varchar(10) NOT NULL DEFAULT '',
  `es_t_del` varchar(10) NOT NULL DEFAULT '',
  `threadid` varchar(255) NOT NULL DEFAULT '0',
  PRIMARY KEY (`es_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=367 ;

--
-- Dumping data for table `esb2b_messages`
--

INSERT INTO `esb2b_messages` (`es_id`, `es_fid`, `es_tid`, `es_subject`, `es_message`, `es_tempdate`, `es_onstamp`, `es_msg_read`, `es_f_del`, `es_t_del`, `threadid`) VALUES
(361, 3, 23, ' Quotation for Embossed and watermark Certificate Printing', 'Hello, this is Bella, we specilized in providing variable data printing services for eight years and we can make it according to your detail requests. Could you please send your original artpaper sothat we can discuss it in details? Looking forward to hearing from you soon. My skype is kingwin1688.', '2014-08-23 22:40:39', '2014-08-23 21:59:55', 'Yes', 'No', 'No', '0'),
(360, 2, 23, 'test', 'asgdfgdfsgdf', '2014-08-23 19:39:11', '2014-08-19 02:35:09', 'Yes', 'No', 'No', '0'),
(363, 2, 23, 'not threaded', 'asgdfgdfsgdf', '2014-08-23 23:29:28', '2014-08-19 02:35:09', 'Yes', 'No', 'No', '0'),
(362, 23, 2, ' Quotation for Embossed and watermark Certificate Printing', 'Hello, this is Bella, we specilized in providing variable data printing services for eight years and we can make it according to your detail requests. Could you please send your original artpaper sothat we can discuss it in details? Looking forward to hearing from you soon. My skype is kingwin1688.', '2014-08-23 19:39:15', '2014-08-23 21:59:55', 'No', 'No', 'No', '0'),
(364, 23, 2, 'reply', 'my first reply', '2014-08-23 21:41:11', '2014-08-23 02:35:09', 'No', 'No', 'No', '360'),
(365, 2, 23, 'this is reply of reply', 'reply of reply', '2014-08-23 22:41:26', '2014-08-24 02:35:09', 'Yes', 'No', 'No', '360'),
(366, 23, 2, 'reply', 'my first reply', '2014-08-23 21:41:11', '2014-08-24 02:35:09', 'No', 'No', 'No', '360');

链接到sql小提琴
http://www.sqlfiddle.com/#!2/9def4/1/0

es_fid和es_tid是用户表的外键.

收件箱的输出应该是

来自|主题|消息|时间戳

在小组中

让我解释一下这个系统是如何工作的,如果’threadid’为0,这意味着该行是父级.如果行中的’threadid’不为零,则表示它是一个父节点的子节点.我希望每当用户进入收件箱时,如果退出其他显示父级,它只显示在子级中收到的最新内容.

谢谢.

解决方法:

小提琴：http://www.sqlfiddle.com/#!2/9def4/13/0

select es_id, es_fid, es_subject, es_message, es_onstamp
  from esb2b_messages x
 where threadid = 0
   and es_tid = 23
   and not exists (select 1
          from esb2b_messages y
         where y.threadid = x.es_id
           and y.es_tid = x.es_tid)
union all
select es_id, es_fid, es_subject, es_message, es_onstamp
  from esb2b_messages x
 where threadid <> 0
   and es_tid = 23
   and es_onstamp = (select max(y.es_onstamp)
                       from esb2b_messages y
                      where y.threadid = x.threadid)
 order by es_id, seq, es_onstamp

如评论中所述,360现在已缩减为最新的孩子.

其他线程没有子节点,因此显示父节点.如果他们确实有孩子,将会出现最新的孩子.

内容总结

以上是互联网集市为您收集整理的php – MY SQL查询线程消息传递收件箱并发送全部内容，希望文章能够帮你解决php – MY SQL查询线程消息传递收件箱并发送所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端