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.
Code:
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.
Code:
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          -             1
should output something like...
Code:
    userid        ownerCommentCount    notOwnerCommentCount
    1                 0                        3
    100               1                        1
    ABC4EF            3                        4
    SDF5gH            0                        0