I realise that what I was looking for is not practical or very easy to do. So will have to do this in two separate queries.
CREATE TABLE IF NOT EXISTS `itemComments` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`dateAdded` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`itemid` text NOT NULL,
`userid` text NOT NULL COMMENT 'comment author',
`comment` text NOT NULL,
`ownerRead` tinyint(1) NOT NULL DEFAULT '0',
`emailNotified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`reportedby` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=90 ;
INSERT INTO `itemComments` (`id`, `dateAdded`, `itemid`, `userid`, `comment`, `ownerRead`, `emailNotified`, `reportedby`) VALUES
(74, '2012-11-16 23:39:22', '383', 'ABC4EF', 'Great for parties and disco\\''s too!!!', 0, '0000-00-00 00:00:00', ''),
(88, '2012-12-08 09:46:41', '383', '1', '1', 0, '0000-00-00 00:00:00', ''),
(89, '2012-12-08 09:47:48', '383', '100', '2', 0, '0000-00-00 00:00:00', '');
What I wish to now have in the results is the userid's that own the items and the total number of unread comments for all of their items, in the first query.
Then in the second I would like returned userid's of all that have made comments and the total number of comments made after the posters last message. so if userA posts a message for item1 and userB posts a message for item1 then userA would have one message to view. if userC posts another message for item1 then userA would have two messages to view and userB would have one message to view, userC would have none for item1 as they posted last.
I think thats what I am after. but think this may be so complex if not impossible to query?
Can anyone suggest a better method/logic to do this in.
I am wanting to have a cronjob run every hour and if there are messages to view then each user will get an email to state this and a field in the users table called `commentEmailNotify` will be set to the last time() email sent. My code will then check before sending the email if they have had one already sent in the last 24 hours. no need to remind them of each message every hour!
Its just that query to get the userid and the total number of messages for each item they have commented in and if there are any replies made after their last message.
The total count is not really that important, but would be great to have, but even if i just get the user id to notify of replies that is a start.