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 13 of 13
  1. #1
    Regular Coder
    Join Date
    Oct 2009
    Posts
    445
    Thanks
    7
    Thanked 3 Times in 3 Posts

    how and where do i add a 'totalCount' results returned

    I have the following code that gets all the comments made and lists them in date order where a member either started or has replied to a comment which someone else started.

    What I would like to have in the results is a totalCount' in each row that tells me how many results are related to the `itemid`. I know that each result for an item will have the same totalCount number, but this is the results I am after.

    Any help on this would be much appreciated.

    Code:
    SELECT `c`.`id`, `c`.`dateAdded`, `c`.`itemid`, `c`.`comment`, `c`.`reportedby`,
    `c`.`userid` AS `posterId`, `i`.`title`,
    `poster`.`fullname` AS `posterName`,`poster`.`shopName` AS `posterShopName`
    FROM `itemComments` as `c`
    INNER JOIN (
    SELECT `itemid`, MAX(dateAdded) as dateAdded
    FROM `itemComments`
    WHERE `itemid` IN
             (
             SELECT `itemid` FROM `itemComments`
             WHERE `userid` = '219'
             )
    GROUP BY `itemid`
    ) as X USING (itemid, dateAdded)
    LEFT JOIN `users` as `poster` ON `poster`.`userid` = `c`.`userid`
    LEFT JOIN `items` as `i` ON `c`.`itemid` = `i`.`itemid`
    ORDER BY `c`.`dateAdded` DESC

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,185
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    Well, I would start by getting rid of that innermost separate SELECT (where userid = 219...and why are there apostrophes around a *NUMBER*???) and then just JOIN to yet another separate counting SELECT:

    Code:
    SELECT c.id, c.dateAdded, c.itemid, c.comment, c.reportedby, c.userid AS posterId, 
           i.title,
           P.fullname AS posterName,P.shopName AS posterShopName,
           CT.itemCount
    FROM itemComments as C
    INNER JOIN (
        SELECT itemid, MAX(dateAdded) as dateAdded
        FROM itemComments WHERE userid = 219
        GROUP BY itemid
        ) as X 
    USING (itemid, dateAdded)
    INNER JOIN (
        SELECT itemid, COUNT(*) AS itemCount
        FROM itemComments GROUP BY itemid
    ) AS CT
    LEFT JOIN users as P ON P.userid = C.userid
    LEFT JOIN items as i ON c.itemid = i.itemid
    ORDER BY c.dateAdded DESC
    Oh...and you will note that I removed every one of your completely unneeded back ticks.
    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
    26,185
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    I should note that the query does *NOT* do what you said it does:
    gets all the comments made and lists them in date order where a member either started or has replied to a comment which someone else started.
    That is *NOT* true. It only gets the MOST RECENT comment for each given itemid. It most certainly does NOT get "all the comments".

    That's because your INNER JOIN that joins C and X ensures that only the records in C that match the MAX(dateadded) specified by X.dateadded will be found.
    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
    Regular Coder
    Join Date
    Oct 2009
    Posts
    445
    Thanks
    7
    Thanked 3 Times in 3 Posts
    I have just noticed that I have not read the last message that was posted, as I had seen the previous one and used the code, but had not seen other messages until now.

    Yes you are right it does not get all of the messages.

    here is what I currently have.

    the $_SESSION['user']['userid'] is the logged in user.
    Code:
    SELECT `c`.`id`, `c`.`dateAdded`, `c`.`itemid`, `c`.`comment`, `c`.`reportedby`,
    `c`.`userid` AS `posterId`, `i`.`title`,
    `poster`.`fullname` AS `posterName`,`poster`.`shopName` AS `posterShopName`, `totalCount`
    FROM `itemComments` as `c`
    INNER JOIN (
    SELECT `itemid`, MAX(dateAdded) as dateAdded, COUNT(*) AS `totalCount`
    FROM `itemComments`
    WHERE `itemid` IN
             (
             SELECT `itemid` FROM `itemComments`
             WHERE `userid` = '" . $mysqli->real_escape_string($_SESSION['user']['userid']) . "'
             )
    GROUP BY `itemid`
    ) as X USING (itemid, dateAdded)
    LEFT JOIN `users` as `poster` ON `poster`.`userid` = `c`.`userid`
    LEFT JOIN `items` as `i` ON `c`.`itemid` = `i`.`itemid`
    ORDER BY `c`.`dateAdded` DESC
    I am looking to get the most recent comment for each item that is owned by the user logged in.
    and the most recent comment that was made on an item where the logged in user is part of the conversation. (but not necessarily the owner)
    Last edited by needsomehelp; 08-09-2013 at 01:12 PM.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,185
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    Surely the field userid in your database is a NUMBER? No???
    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.

  • #6
    Regular Coder
    Join Date
    Oct 2009
    Posts
    445
    Thanks
    7
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by Old Pedant View Post
    Surely the field userid in your database is a NUMBER? No???
    Yes it was once...

    Then I had a stack of users from same IP signup and send junk to everyone using some system to resend a form over and over with a different userid number! took ages to remove them all.

    Not had a single spam message since changing it to a unique random hash ;-)

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,185
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    Oh, okay.

    Let me look at it, then.

    Back later.
    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.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,185
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    This part of that query is silly:
    Code:
        SELECT itemid, MAX(dateAdded) as dateAdded, COUNT(*) AS totalCount
        FROM itemComments
        WHERE itemid IN
             (
             SELECT itemid FROM itemComments
             WHERE userid = '$id'
             )
        GROUP BY itemid
    Why would you do it like that, instead of just doing
    Code:
        SELECT itemid, MAX(dateAdded) as dateAdded, COUNT(*) AS totalCount
        FROM itemComments
        WHERE userid = '$id'
        GROUP BY itemid
    ????

    But your name totalCount is misleading. It's actually itemIdCount, as it is counting the number of records with each qualifying itemid. There's no "totalCount" of anything, that I can see.
    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.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,185
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    And in any case your totalCount is a completely different thing than the itemCount of my long ago prior post.

    But in any case, I don't see how I can help you without knowing all the fields in your tables.

    For example, I have no idea what field(s) to use to determine " the most recent comment that was made on an item where the logged in user is part of the conversation (but not necessarily the owner)".

    I see nothing at all in your query that gives me the field names needed to determine if a given user is part of a "conversation".
    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.

  • #10
    Regular Coder
    Join Date
    Oct 2009
    Posts
    445
    Thanks
    7
    Thanked 3 Times in 3 Posts
    Thank you for your time on this. Here are my tables. I have removed a lot of the unneeded fields.

    Code:
    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=115 ;
    
    
    
    
    CREATE TABLE IF NOT EXISTS `items` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'just a dummy ID so we know how many items have been uploaded',
      `itemid` text NOT NULL,
      `userid` text NOT NULL,
      `status` text NOT NULL,
      `listType` text NOT NULL,
      `subCategory` bigint(20) NOT NULL,
      `title` text NOT NULL,
      `description` text NOT NULL,
      `price` decimal(10,2) NOT NULL DEFAULT '0.00',
      `photos` text NOT NULL,
      `approved` text NOT NULL,
      `reportedby` text NOT NULL,
      `views` bigint(20) NOT NULL DEFAULT '0',
      `rssViews` bigint(20) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=650 ;
    
    
    
    
    
    CREATE TABLE IF NOT EXISTS `users` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `userid` text NOT NULL,
      `account_type` varchar(20) NOT NULL DEFAULT 'user',
      `verified` varchar(3) NOT NULL DEFAULT 'No',
      `banned` char(3) NOT NULL DEFAULT 'No',
      `suspended` varchar(3) NOT NULL DEFAULT 'No',
      `shopName` varchar(200) NOT NULL,
      `fullname` varchar(150) NOT NULL,
      `email` varchar(150) NOT NULL,
      `dateCreated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=600 ;

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,185
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    Do you understand what the text data type *MEANS* in MySQL???
    http://dev.mysql.com/doc/refman/5.5/en/blob.html

    You should *NEVER* make ANY field be a TEXT field unless you MUST.
    All TEXT fields are stored as just a pointer in the record with the actual data of the TEXT field stored separately. So all queries that need to retrieve values from TEXT fields must make *TWO* disk reads: One to read the main record, another to go read the part of the disk where the TEXT data resides. And when you have *EIGHT* TEXT fields, as you do in your comments table, that means AS MANY AS NINE DISK READS. (Probably not that many, as it is likely that multiple TEXT fields for the same record will be stored adjacent, but there is no guarantee of that!! It *can* be nine disk reads!)

    And it's not such an egregious mistake, but using BIGINT is also almost always a waste of disk space and memory (and, to a minor degree, performance). Consider: an ordinary INT field can hold a maximum positive number of over TWO BILLION. Do you *REALLY* think you are going to have over 2 billion items? or itemcomments?

    You truly should rethink your table designs. Only use fields as big as you need to, not monstrous fields that do nothing but waste disk space, memory, and performance.
    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.

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,185
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    Okay, let's look at your requirements:
    I am looking to get the most recent comment for each item that is owned by the user logged in and the most recent comment that was made on an item where the logged in user is part of the conversation (but not necessarily the owner).
    So first question: *CAN* we do those both at once?

    If we separately queried for comments on items owned and on comments in conversations, we might get the same information twice.

    *IF* we truly get *EXACTLY* the same information twice, then we can simply use SELECT DISTINCT to avoid duplicates.

    But if there is any info at all that is different, depending on the two "paths" taken, then SELECT DISTINCT won't help.

    So let's look at the fields you are retrieving:
    Code:
    SELECT c.id, c.dateAdded, c.itemid, c.comment, c.reportedby,
           c.userid AS posterId, i.title,
           poster.fullname AS posterName,poster.shopName AS posterShopName, totalCount
    Where c is the comment and i is the item.

    And it looks to me like it doesn't matter. If we do get duplicates, I think they will be true duplicates.

    Oh...but we still have to define what totalCount means. Is that the total number of comments on a given item? Or the total number of comments on a given item by a given user?

    And final question, for now: Why were you doing LEFT JOINs???? You would only do that if you had a comment with a userid that matched *NO* users or an itemid that match *NO* items. Meaning you have bad data in your database. (Which could be avoided by using INNODB tables, instead of MYISAM, and enforcing referential integrity. Is there any reason you opted to go with MYISAM tables?)
    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.

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,185
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    Okay, one more:
    Code:
      `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'just a dummy ID so we know how many items have been uploaded',
    My head hurts. This is silly.

    SELECT COUNT(*) tells you how many have been uploaded.
    Why would you make the database go to the expense of maintaining an extra auto_increment just for keeping count? Especially since any auto_increment table has to be indexed, adding a useless index to your database.
    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
    •