while() in while() or 1x Query?

Question

I don't know which way would be better for PHP and SQL. I design and program my own comment system and I would like id2=0 its a comment and id2>0 its sub-comment in one table. This means that if someone wrote a sub-comment in a comment with ID=1, then ID2 is responsible for assigning (sub-comment) to ID=1 (comment). I have one table comments like this:

id | id2 | smt | etc.
1 | 0 | x | x //comment with sub-comment where id=3
2 | 0 | x | x //comment
3 | 1 | x | x //that is sub-comment for comment where id=1

I'm displaying this in a while loop because I need to print all the data from comments. Like this:

$sqlkom="SELECT * FROM `comments` WHERE `id`='".$row['id']."' ORDER BY id DESC LIMIT 20";
if($resultkom = mysqli_query($con, $sqlkom)){
if(mysqli_num_rows($resultkom)){
while($rowkom = mysqli_fetch_assoc($resultkom)) {

echo HtmlFormatFunction($rowkom['id'],$rowkom['id2'],$rowkom['smt'],$rowkom['etc'])

I won't achieve this: if($rowkom['id']==$rowkom['id2']) without adding another while() loop, right? or maybe it is enough to modify the SQL query to achieve this effect? Please, help me with the right solution.

Answer 1

You can join your table with itself, something like that:

SELECT c.id, c.id2, c.smt, c.etc,
       s.id, AS s_id, s.id2 AS s_id2, s.smt AS s_smt, s.etc AS s_etc
FROM `comments` c LEFT JOIN `comments` s
ON c.id = s.id2
WHERE c.id = ?
ORDER BY c.id DESC LIMIT 20";

Then you should get minimum one line for the first comment or as much as lines for the first comment as there are sub-comments. Then the first four fields will repeat, but thats not an issue. Maybe this is not perfect. Hope you get the idea.