PHP MySQL, Nested/threaded comments with pages, LIMIT 10 OFFSET ect

Question

I've been searching for awhile for an answer and cant seem to figure one out so I'm hoping someone here can help.. I'm just trying to figure out a way to only show XX comments on ones page, allowing the process of nested/threaded comments, and not having to show all the comments on the one page when the user might have way more than 1,000s of comments.. I'm trying to do this with one mysql query, instead of 2, if I have to do it that way I can.

Heres my MySQL table (its a new table not released yet so I can change if needed)

CREATE TABLE IF NOT EXISTS `comments_threaded` (
`id` bigint(255) NOT NULL AUTO_INCREMENT,
`toUid` bigint(255) NOT NULL,
`fromUid` bigint(255) NOT NULL,
`Pid` bigint(255) NOT NULL,
`Puid` bigint(255) NOT NULL,
`Pseen` int(2) NOT NULL,
`seen` int(2) NOT NULL,
`comment` text NOT NULL,
`tim` int(20) NOT NULL,
`Pip` varchar(30) NOT NULL,
PRIMARY KEY (`id`),
KEY `toUid` (`toUid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

id = the id of comment auto increased
toUid = is for what profile/user its on
fromUid = who the comment is from
Pid = the id of the parent id..
Puid = the Users id number of the parent id
Pseen = if the parent user has seen the comment
seen = if the toUid has seen the comment
comment = the comment left by fromUid
tim = the time() it was left
Pid = the persons ip

anways, I'm trying to have comments on the users profile, where they're nested with replies to the previous/parent comment if they'd like to reply to them.
I'd like to limit the ammount of comments on the page as well to 10, or 20, whatever I feel would fit, and this number would also include the nested comments..

for example if i wanted 10 comments on the page 1

comment 1
--comment 2 replied to comment 1
--comment 3 replied to comment 1
----comment 4 replied to comment 3
------comment 5 replied to comment 4
comment 6
--comment 7 replied to comment 6
--comment 8 replied to comment 6
comment 9
--comment 10 replied to comment 9

and then on page 2, if the first comment would be a reply to another comment it would start with that original parent comment they're replied to, if at all be able to still only keep 10 comments on that page, if not it would be fine to have the extra comments at the top shown, or possibly the extra comments on page 1 at the bottom being shown.. I'm not sure which would be easier.

comment 9
--comment 10 replied to comment 9
----comment 11 replied to comment 10
----comment 12 replied to comment 10
--comment 13 replied to comment 12
comment 13
comment 14
--comment 15 replied to comment 14
--comment 16 replied to comment 14
----comment 17 replied to comment 16
----comment 18 replied to comment 17
--comment 19 replied to comment 17
comment 20

I was thinking a MySQL IN clause would do this but I'm getting an error.. " tried -

SELECT id, fromUid, Pseen, seen, comment, tim 
FROM comments_threaded 
WHERE toUid = '".mysql_real_escape_string($toUid)."' 
    OR Pid IN (
        SELECT id, fromUid, Pseen, seen, comment, tim 
        FROM comments_threaded 
        WHERE toUid = '".mysql_real_escape_string($toUid)."'
    ) 
LIMIT 10

and been getting

Operand should contain 1 column(s)

which I've never seen before..

thanks for looking and helping if you can!

score 2 · Answer 1 · answered Jun 07 '12 at 06:45

2

If you use a subquery in IN(), that subquery must select 1 and only 1 column. Yours is selecting 6.

Also, for this kind of nested content, you may want to consider using the nested set model.

answered Jun 07 '12 at 06:45

Okonomiyaki3000

3,628
23
23

thanks, I knew the IN() thing was suppose to select 1 column, I was having a brain fart there, its been awhile since I used IN(). and I was looking at the nested set model and think I may run into some problems with it bogging down the server once we have thousands of rows needing to be updated.. I'm thinking there would be another way around it, so I'm gonna keep studying.. – Yohn Jun 07 '12 at 17:00
Probably what you need to do is show initially only top level comments then allow your users to see nested comments by clicking on something which will trigger an AJAX request to get that extra data. This way, you do not need nested sets, only a `parent_id` column. Also, it makes your interface better. Your interface, as I understand it, will actually have some serious problems. Imagine that your first post has 20 responses. Then the first response has 20 responses. Your plan doesn't seem to handle this very well. – Okonomiyaki3000 Jun 08 '12 at 00:12

PHP MySQL, Nested/threaded comments with pages, LIMIT 10 OFFSET ect

1 Answers1