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.
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`
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)