Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Oct 2009
    Posts
    432
    Thanks
    6
    Thanked 3 Times in 3 Posts

    how best to count comments on items.

    I have been playing around with code and queries and now think i have the writeup that will help explain what i was after. I have previously posted and it is not getting replies due to the complex replies i was giving. this post should explain exactly what i am 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
    Last edited by needsomehelp; 12-14-2012 at 10:27 AM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,155
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Your comments table is not complete.

    You have a line like this:
    Code:
    73     2012-11-15 09:47:48     383    100       owner emma(ABC3EF) comment by jason(100)
    But WHERE in that data does it say the Emma (or userid ABC3EF) is the owner of that itemid???

    In your prior posts, the "owner" of an itemid could be determined. Can't be done with this limited data.

    Back to the drawing board.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,155
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Actually, if I go look at your prior post, I guess the problem is just that you forgot to put in the ORIGINAL post for each itemid in this forum thread.

    I've added on comment per itemid, to establish an original post. I also shortened your crazy itemid values to 3 characters for readability.

    And got rid of your totally useless commentid field (at least I sure can't find any use for it).

    SO now I have this:
    Code:
    mysql> select * from comments order by itemid,dateAdded;
    +--------+---------------------+--------+---------------------------------------------+
    | itemid | dateAdded           | userid | comment                                     |
    +--------+---------------------+--------+---------------------------------------------+
    | 163    | 2012-12-09 01:00:00 | ABC3EF | originsl comment so owner is emma           |
    | 163    | 2012-12-09 09:38:52 | SDF5gH | owner emma(ABC3EF) comment by simon(SDF5gH) |
    | 383    | 2012-11-10 00:00:00 | ABC3EF | original comment so owner is emma           |
    | 383    | 2012-11-15 09:47:48 | 100    | owner emma(ABC3EF) comment by jason(100)    |
    | 383    | 2012-12-08 09:46:41 | 1      | owner emma(ABC3EF) comment by peter(1)      |
    | 3ef    | 2012-01-01 00:00:00 | 100    | original comment so ownere is jason         |
    | 3ef    | 2012-12-09 09:50:26 | ABC4EF | owner jason(100) comment by emma(ABC3EF)    |
    | 3ef    | 2012-12-09 09:59:26 | 1      | owner jason(100) comment by peter(1)        |
    | 3ef    | 2012-12-09 10:00:37 | 100    | owner jason(100) comment by jason(100)      |
    | 3ef    | 2012-12-09 10:20:50 | 100    | owner jason(100) comment by jason(100)      |
    | 3ef    | 2012-12-09 11:00:50 | SDF5gH | owner jason(100) comment by simon(SDF5gH)   |
    +--------+---------------------+--------+---------------------------------------------+
    Will this work?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,155
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    So the first task is to find the OWNER of each itemid.

    This is easy enough:
    Code:
    SELECT C.itemid, C.userid 
    FROM comments AS C,
         ( SELECT itemid, MIN(dateAdded) AS firstpost FROM comments GROUP BY itemid ) AS M
    WHERE C.itemid = M.itemid
    AND C.dateAdded = M.firstpost;
    Which gives us:
    Code:
    +--------+--------+
    | itemid | userid |
    +--------+--------+
    | 383    | ABC3EF |
    | 163    | ABC3EF |
    | 3ef    | 100    |
    +--------+--------+
    You might even want to turn that into a pair of views:
    Code:
    mysql> CREATE VIEW firstPosts AS
        -> SELECT itemid, MIN(dateAdded) AS firstpost FROM comments GROUP BY itemid;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> CREATE VIEW getItemOwners AS
        -> SELECT C.itemid, C.userid
        -> FROM comments AS C, firstPosts AS F
        -> WHERE C.itemid = F.itemid
        -> AND C.dateAdded = F.firstPost
        -> ;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from getItemOwners;
    +--------+--------+
    | itemid | userid |
    +--------+--------+
    | 383    | ABC3EF |
    | 163    | ABC3EF |
    | 3ef    | 100    |
    +--------+--------+
    But this is just a start.

    You now have to *ALSO* find the *LAST* POST by each userid for each itemid. Because, of course, you only want to count comments that occur *AFTER* the last post by each user.

    Truthfully, part of the problem is that your database organization is probably not ideal for something like this.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    Oct 2009
    Posts
    432
    Thanks
    6
    Thanked 3 Times in 3 Posts
    i have even tried to scrap the query only method and try a basic counter system using PHP and simple-ish queries.

    but again just trying to understand the right way to do this is very difficult.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,155
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Well, for starters, you could add a column to your table that holds the "owner" of the thread. Presumably, the first person to post in the thread.

    That gets rid of the need for the code I had to write in post #4, above.

    As I noted in your prior thread, doing all this for a *single* userid seems possible. In fact, I think I had the answer for same in that other thread.

    Doing it for all users is not so easy.

    Where is your priority, here?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •