There are 3 tables,

  1. Remove the last 10 comments from the database and display information about the author.
  2. Print the last 5 responses to the comment from the database and display information about the author.

Comment Table

comment_table

  1. post_id - post id
  2. post_news_id - id news where the comment is left
  3. post_autor_id - comment author id
  4. post_text - comment text
  5. post_type - comment type (1 - comment, 2 - reply to comment)
  6. post_idcom - if the post is the answer then there is an id on which the comment was made the answer

User table

user_table

  1. uid - user id
  2. nikname - nickname
  3. avarar - avatar

All I could do is output this comment and info about the comment author. SELECT * FROM comment_table LEFT JOIN user_table ON comment_table.post_autor_id=user_table.uid WHERE post_news_id = '".$_GET[news_id]."' ORDER BY post_id DESC LIMIT 80

It is necessary that all this was in 1 request - not requests in a cycle.

  • Try to describe in more detail the essence of the question: how exactly do you want to see the results? Also format the code - so you will respond faster. - Grizli

1 answer 1

There are 3 tables

comment_table

user_table

You have bent my brain ... I will try to assume that we are only interested in the 2 tables indicated. I will also assume that I understood the questions correctly (although there is no certainty, for the question is very peculiar).

"1. Display the last 10 comments from the database and display information about the author." Just the last 10 (by ID) entries from the comment_table table, which are comments and not an answer to the comment + information about the authors of these records

 SELECT ct.post_id, ct.post_news_id, ct.post_autor_id, ct.post_text, ct.post_type, ct.post_idcom, ut.uid, ut.nickname, ut.avatar FROM comment_table ct LEFT JOIN user_table ut ON ct.post_autor_id = ut.uid WHERE ct.post_news_id = '".$_GET[news_id]."' and ct.post_type = 1 ORDER BY post_id DESC LIMIT 10

"2. Display the last 5 responses to the comment from the database and display information about the author." I made an assumption that you are interested in a particular comment and you want to see the answers to this comment. The request will not change drastically, just add to the condition a specific comment ID for which we will look at the answers

 SELECT ct.post_id, ct.post_news_id, ct.post_autor_id, ct.post_text, ct.post_type, ct.post_idcom, ut.uid, ut.nickname, ut.avatar FROM comment_table ct LEFT JOIN user_table ut ON ct.post_autor_id = ut.uid WHERE ct.post_news_id = '".$_GET[news_id]."' and ct.post_type = 2 and ct.post_idcom = '".$_GET[main_post_id]."' ORDER BY post_id DESC LIMIT 5

If you are interested in a comment + replies to a comment with a single sample, then UNION ALL is in your hands.

Agree that code formatted in this way is much better read.

  • And if you need samples of the form: Comment1 -Reply1_ on_comment1 -Reply2_ on_comment1 see for 1 meeting ... -ReplyN___ for comment1 Comment2 -Reply1_ for_comment2 -Reply2_ for_comment2 - ... -ReplyM_ on_comment2 ... CommentX see if you have a look for you to see a look for some see you and see_ comment_2 -... And this is already solved taking into account the DBMS that you use. In Oracle, it will be connect by start with. In MSSQL, in my opinion, there are no such standard solutions and there it is necessary to implement recursion, But this is a completely different question .. .. short answers are not mine ... - Grizli