I have been playing around with code and queries and now think i have the writeup that will help explain what i was after.
// return userid of all users
first check for comments which the itemOwner will be notified of.
// count all comments for all of the itemOwner items, but only count comments after their own comment or reply, if any, for each item they own. if no comment by the itemOwner then count all messages. Add this count to the ownerCommentCount.
// count all comments where a user, (NOT itemOwner), has taken part in any items comment conversation, but only count comments after their own reply for all items that they have taken part in. Add this count to the array for that notOwnerCommentCount.
// echo results to make sure they are correct.
the items table has these fields…
id, itemid, userid
lets say this is the table of comments…
userid here is the author of the comment.
id dateAdded itemid userid comment
73 2012-11-15 09:47:48 383 100 owner emma(ABC3EF) comment by jason(100)
88 2012-12-08 09:46:41 383 1 owner emma(ABC3EF) comment by peter(1)
90 2012-12-09 09:38:52 1637ebf4b8ad591226e830c932a2a1a9 SDF5gH owner emma(ABC3EF) comment by simon(SDF5gH)
91 2012-12-09 09:50:26 3efcefcc77242c439591d4d2f6ce9168 ABC4EF owner jason(100) comment by emma(ABC3EF)
92 2012-12-09 09:59:26 3efcefcc77242c439591d4d2f6ce9168 1 owner jason(100) comment by peter(1)
93 2012-12-09 10:00:37 3efcefcc77242c439591d4d2f6ce9168 100 owner jason(100) comment by jason(100)
94 2012-12-09 10:20:50 3efcefcc77242c439591d4d2f6ce9168 100 owner jason(100) comment by jason(100)
96 2012-12-09 11:00:50 3efcefcc77242c439591d4d2f6ce9168 SDF5gH owner jason(100) comment by simon(SDF5gH)the userid and which message they would get counted in to be told of.
‘nc’ means that that comment is not counted as the itemOwner made the comment, so the owner will not get notified of their own comments.
commentid emma jason simon peter
73 1 - - -
88 1 1 - -
90 1 1 - -
91 - nc - -
92 1 nc - -
93 1 nc - 1
94 1 nc - 1
96 1 1 - 1should output something like…
userid ownerCommentCount notOwnerCommentCount
1 0 3
100 1 1
ABC4EF 3 4
SDF5gH 0 0