CODEWITHSUNDEEP
phpmysql
Ari
Ari

Reputation: 11

How can I get nested results from a MySQL table AND link to another table?

I have a table of comments:

comment_id int(11) No auto_increment
profile_id int(11) No
type char(1) latin1_swedish_ci No
parent_id int(11) No
text mediumtext latin1_swedish_ci No
datetime datetime No
vote_score int(11)

Where "type" can be "a", a comment on an article, or "c", a comment on a comment (a reply)

"parent_id" says whether the comment is a child of article_id=1 or comment_id=1

It's linked to table "profiles" where comments.profile_id=profile.profile_id (so i can get profile.first_name, for example)

THE CHALLENGE:

I'd like to get a SQL query that gives me the comments in order as they'd be displayed:

comment 1 [type=a]

(reply to comment 1) [type=c]

(reply to comment 2) [type=c]

comment 2 [type=a]

What SQL query do I need to do this? (The objective is to return the results in correct order so I can have a SIMPLE php WHILE loop print the results.)

Thanks!

Upvotes: 1

Views: 199

Answers (2)

Ari
Ari

Reputation: 11

One way to do this:

SELECT pr.name_f,c1.comment_id AS comment_1, c1.profile_id AS profile_id_1, c1.type AS type_1, c1.parent_id AS parent_id_1, c1.text AS text_1, c1.datetime AS datetime_1, c1.vote_score AS score_1, c2.comment_id AS comment_2, c2.profile_id AS profile_id_2, c2.type AS type_2, c2.parent_id AS parent_id_2, c2.text AS text_2, c2.datetime AS datetime_2, c2.vote_score AS score_2 FROM profiles AS pr, comments AS c1 

LEFT JOIN mcomments AS c2 ON c2.parent_id=c1.comment_id AND c2.comment_idc1.comment_id 

WHERE c1.article_id = '1' AND (pr.profile_id=c1.profile_id OR pr.profile_id=c2.profile_id)

Then simple PHP arrays of comment_id's can prevent duplicate/sub comments from being posted.

I'm sure there's a better way?

Upvotes: 0

greg0ire
greg0ire

Reputation: 23255

I think you might be interested in reading this article about managing Hierarchical data in MySQL. Using this model, queries to do simple things like in your question are simple too. With this model, your table structure would be this following:

comment_id int(11) No auto_increment
article_id int(11)
lft int NOT NULL
rgt int NOT NULL
profile_id int(11) No
text mediumtext No
datetime datetime No
vote_score int(11)

your example data would look like this

comment_id article_id lft rgt profile_id text              datetime vote_score
------------------------------------------------------------------------------
     1         1       1   6      ?      comment 1            ?        ****
     2         1       2   3      ?      reply to comment1    ?         **
     3         1       4   5      ?      reply to comment2    ?        ****
     4         2       1   2      ?      comment2             ?         *

Now with this model, getting the comments in the right order is really easy :

ORDER BY article_id, lft

Switching to this model will spare you a lot of recursion headaches I think. Have fun!

P.S: Are you swedish? O:-)

Upvotes: 1

Related Questions