我正在编写一个私人消息脚本。但是,我在MySQL端遇到了一些问题。我已经让它部分工作了。
举个例子:
messageid | parentid | subject | flags
1 NULL 'Foobar' 2 //has been read
2 1 'Foobar' 2 //has been read
3 1 'Foobar' 1 //has not been read
消息 ID:消息
的自动递增父级 ID:线程
中第一条消息的消息 ID主题:话题
的主题(或标题)标志:按位标志(1 = 未读;2 = 已读)
问题(希望有人可以帮助我解决)
1.我现在的设置方式,我的脚本将在未读消息页面和已读消息页面中显示消息线程。我的目标是让它只显示在未读消息页面中,直到它被阅读。
这是我的查询
//$_GET['node'] is allowed to be: unread, read, or sent
$wftype = $_GET['node'] == 'sent' ? 'sender' : 'recipient';
$filter = $_GET['node'] == 'sent' ? '' : ' AND (`flags` & '.$message_flags[$_GET['node']].') != 0';
//$filter = ($_GET['node'] == 'unread' || $_GET['node'] == 'read') ? ($_GET['node'] == 'read' ? ' AND (`flags` & '.$message_flags['read'].') != 0' : ' AND (`flags` & '.$message_flags['unread'].') != 0') : '';
$result = $sql->query('SELECT `messageid`, `parent`, `senderid`, `sender`, `subject`, MAX(`sendtime`) AS `sendtime` FROM `memberpostbox` WHERE `'.$wftype.'id` = '.$_SESSION['client']['number'].$filter.' AND (`flags` & '.$message_flags[$wftype.'_deleted'].') = 0 GROUP BY `parent` ORDER BY `sendtime` DESC');
我是否必须重组我的表格或完全改变我的工作方式?或者,用我现在拥有的东西可以做到这一点吗?
其他信息
表结构
CREATE TABLE IF NOT EXISTS `memberpostbox` (
`messageid` bigint(20) unsigned NOT NULL auto_increment,
`parentid` int(10) unsigned default NULL,
`senderid` varchar(255) collate utf8_unicode_ci NOT NULL,
`sender` varchar(255) collate utf8_unicode_ci NOT NULL,
`recipientid` varchar(255) collate utf8_unicode_ci NOT NULL,
`recipient` varchar(255) collate utf8_unicode_ci NOT NULL,
`subject` varchar(255) collate utf8_unicode_ci default NULL,
`message` longtext collate utf8_unicode_ci NOT NULL,
`sendtime` int(10) unsigned NOT NULL,
`flags` tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (`messageid`),
KEY `groupid` (`parentid`),
FULLTEXT KEY `search` (`subject`,`message`)
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=22 ;
对于未读邮件
'SELECT .... FROM memberpostbox WHERE flag=1'
对于已读邮件
'SELECT .... FROM memberpostbox WHERE flag=2'
只有标志确定消息是否被读取,因此这将是显示正确列表的唯一部分.....当然,您必须在其中添加其余代码。
// read
$result = $sql->query(
'SELECT
`messageid`,
`parent`,
`senderid`,
`sender`,
`subject`,
MAX(`sendtime`) AS `sendtime`
FROM `memberpostbox`
WHERE `recipientid` = '.$_SESSION['client']['number'].$filter.'
AND `flags` = 2
AND '.$message_flags[$wftype.'_deleted'].' = 0
GROUP BY `parent`
ORDER BY `sendtime` DESC'
);