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

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.

CodePudding user response：

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.