Geek Answers Handbook

MY SQL Query for incoming messages and sending messages

I am trying to create a thread messaging system in PHP and mySQL. My messaging table is as follows.

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`

In this table

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).

Please refer to this image to understand what I want to archive.

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

In the figure above, a combination of these 4 messages creates one thread.

What request should be for incoming and sent items. Now I use this request for incoming messages, but each message is separate.

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

Also see this image to see what is displayed right now and what I want.

: , . , .

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 .

| | | TimeStamp

, , "threadid" 0, , . "threadid" , , . , , "", , .

.

+4
1

Fiddle: 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 , .

, . , .

+4

More articles:


All Articles