Count Unread Message - Part 2

[jason310771]

I have tried to do this myself yesterday and again this morning but still unable to get the right results returned.

 

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 ;

--
-- Dumping data for table `itemComments`
--

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', '');

 

 

this is what i came up with yesterday..

SELECT `ic`.`id`, `ic`.`userid`, `io`.`fullname` AS `toFullname`, `io`.`email` AS `toEmail`, COUNT( * ) AS `totalCount` FROM `itemComments` AS `ic` LEFT JOIN `users` AS `io` ON `io`.`userid` = `ic`.`userid` WHERE `ic`.`ownerRead` =0 GROUP BY `ic`.`userid`

 

and this morning..

SELECT `ic`.`id`, `ic`.`userid`,
                                     `io`.`fullname` AS `toFullname`, `io`.`email` AS `toEmail`,
                                   COUNT( * ) AS `totalCount`
                               FROM `itemComments` AS `ic`
                               LEFT JOIN `users` AS `io` ON `io`.`userid` = `ic`.`userid`
                               LEFT JOIN `items` AS `i` ON `i`.`itemid` = `ic`.`itemid`
                               WHERE `ic`.`ownerRead` = 0 AND `ic`.`userid` != `i`.`userid`
                               GROUP BY `ic`.`userid`

 

but it is not showing the results for the comments added by other users for a users items.

 

And I just tried this, which seems to show the correct number of comments for that user, but it is not showing the other users that is part of the conversation.

SELECT `ic`.`id`, `ic`.`userid` AS `commentOwner`, `i`.`userid` AS `itemOwner`,
`io`.`email` AS `itemOwnerEmail`, `io`.`fullname` AS `itemOwnerFullname`, `io`.`shopName` AS `itemOwnerShopName`,
COUNT( * ) AS `totalCount`
FROM `itemComments` AS `ic`
LEFT JOIN `items` AS `i` ON `i`.`itemid` = `ic`.`itemid`
LEFT JOIN `users` AS `io` ON `i`.`userid` = `io`.`userid`
WHERE `ic`.`ownerRead` = 0 AND `ic`.`userid` != `io`.`userid`
GROUP BY `i`.`userid`

 

basically what i am after is to have results showing each userid, their email and shopName, and how many comments in total for all of their items. but not where they have commented on their own item.

[NomadicJosh]

How about changing the count in your query to this:

 

COUNT(ownerRead)

 

And if you are bring back an array:

 

echo $row['COUNT(ownerRead)'];

[DavidAM]

You seem to be asking TWO different questions:

 

basically what i am after is to have results showing each userid, their email and shopName, and how many comments in total for all of their items. but not where they have commented on their own item.

 

To me, this says I want:

UserA UserA@Address MyShop 123
UserB UserB@Address AShop  124

One row per user with the total number of comments across all items belonging to that user (not including comments from that user)

 

 

is not showing the other users that is part of the conversation.

 

While this implies you were looking for

UserA UserA@Address MyShop UserB 23
UserA UserA@Address MyShop UserC 77
UserA UserA@Address MyShop UserD 23

 

Multiple rows for each SELLER with a row for each user who has commented on the seller's items, showing the number of comments for each user

 

Those are two distinctly different queries.

[jason310771]

I am still lost with how to ask what I am after.

 

But giving you more information about what I will do with the results may help out.

 

I wish to have an email sent out each day to notify each user (seller or not) of any comments made.

 

So if userA adds an item (item1) and then userB asks a question in the comments, then userA has a message waiting, if userA has not read this yet and userC asks another question then userA has two waiting for them, and one message for userB.

 

When the seller opens the comments it sets the `read` to '1' so we know he has read it!

 

The basic results I would like is the userid of each user that has replies to read, and the total number of replies from other users.

 

EDIT:

thinking about it, there is not an easy way to find out if the other users have read their own messages, so think i will leave this part out of the confusion.

 

all that is really needed is the userid of all users that have messages (read or not) and the total number of messages. nut not the messages that they posted themselves.

Edited December 8, 2012 by Realistic Hostings

[Barand]

try

SELECT io.userid AS itemOwner, io.email AS itemOwnerEmail, io.fullname AS itemOwnerFullname,
   io.shopName AS itemOwnerShopName,
   COUNT( * ) AS totalCount
FROM itemComments AS ic
INNER JOIN items AS i ON i.itemid = ic.itemid
INNER JOIN users AS io ON i.userid = io.userid
WHERE ic.ownerRead = 0 AND ic.userid != io.userid
GROUP BY io.userid

[jason310771]

this is a complex one.

 

ok i have been playing around all day! I have now got this, which returns part of the results needed apart from it returning all of the messages in the 'count'

SELECT `b`.`ownerRecentCommentDate`,
`ic`.`itemid`, `ic`.`userid` AS `poster`, `ic`.`emailNotified`,
`i`.`userid` AS `itemOwner`,
`u`.`fullname` AS `ownerName`,
COUNT(`ic`.`id`) AS `totalCount`

FROM `itemComments2` AS `ic`

LEFT JOIN `items` AS `i` ON `i`.`itemid` = `ic`.`itemid`

LEFT JOIN `users` AS `u` ON `u`.`userid` = `i`.`userid`

   LEFT JOIN ( SELECT
   `a`.`itemid`, `a`.`userid`, MAX(`a`.`dateAdded`) AS `ownerRecentCommentDate`
   FROM `itemComments2` AS `a`
   GROUP BY `itemid` ) AS `b`
   ON `b`.`itemid` = `ic`.`itemid`

WHERE `ic`.`userid` != `i`.`userid`

GROUP BY `itemOwner`

 

my way of thinking is to get the most recent date that the 'owner' of the of the item made a comment and only counting the comments after this date is any. then this will return all comments that have been made after the owner made a comment on their items. but my query so far returns the date of the most recent comment no matter who made it. how do i get the most recent date the owner made a comment in that item. then use this date to only count the messages made after it.

[jason310771]

WOW I have almost done it....

 

SELECT `b`.`ownerRecentCommentDate`,
`ic`.`itemid`, `ic`.`userid` AS `poster`, `ic`.`emailNotified`,
`i`.`userid` AS `itemOwner`,
`u`.`fullname` AS `ownerName`,
COUNT(`ic`.`id`) AS `totalCount`

FROM `itemComments2` AS `ic`

LEFT JOIN `items` AS `i` ON `i`.`itemid` = `ic`.`itemid`

LEFT JOIN `users` AS `u` ON `u`.`userid` = `i`.`userid`

   LEFT JOIN ( SELECT
               `a`.`itemid`, `a`.`userid`, MAX(`a`.`dateAdded`) AS `ownerRecentCommentDate`
               FROM `itemComments2` AS `a`
               LEFT JOIN `items` AS `b` ON `b`.`itemid` = `a`.`itemid`
               WHERE `a`.`userid` = `b`.`userid`
               GROUP BY `a`.`itemid` ) AS `b`
   ON `b`.`itemid` = `ic`.`itemid`

WHERE `ic`.`userid` != `i`.`userid`

GROUP BY `itemOwner`

 

the only thing is that it is still counting all comments for the item and not those posted after the owner have last posted.

 

will keep on it, unless someone can spot what i have missed.

[jason310771]

SELECT `b`.`ownerRecentCommentDate`,
`ic`.`itemid`, `ic`.`userid` AS `poster`, `ic`.`emailNotified`,
`i`.`userid` AS `itemOwner`,
`u`.`fullname` AS `ownerName`,
COUNT(`ic`.`id`) AS `totalCount`

FROM `itemComments2` AS `ic`

LEFT JOIN `items` AS `i` ON `i`.`itemid` = `ic`.`itemid`

LEFT JOIN `users` AS `u` ON `u`.`userid` = `i`.`userid`

   LEFT JOIN ( SELECT
               `a`.`itemid`, `a`.`userid`, MAX(`a`.`dateAdded`) AS `ownerRecentCommentDate`
               FROM `itemComments2` AS `a`
               LEFT JOIN `items` AS `b` ON `b`.`itemid` = `a`.`itemid`
               WHERE `a`.`userid` = `b`.`userid`
               GROUP BY `a`.`itemid` ) AS `b`
   ON `b`.`itemid` = `ic`.`itemid`

WHERE `ic`.`userid` != `i`.`userid` AND `ic`.`dateAdded` > `b`.`ownerRecentCommentDate`

GROUP BY `itemOwner`

 

please can this be verified that I have it correct, I believe I have done it. It is returning the result for the only item owner that has a comment. I think getting the item owner query done first then having a separate one for other users that are part of the conversation would be easier.