Iterating twice over same mysql query in PHP

Question

I have a MySQL table of posts that looks like this:

| id | post                                 | parent |
+----+--------------------------------------+--------+
|  1 |    Hello world                       |      0 |
|  2 |    response to 1                     |      1 |
|  3 |    response to 1                     |      1 |
|  4 |    new opening post                  |      0 |

If a post is an opening post, the parent is set to 0. If it's a reply to an opening post, its parent is a value of an ID. I'm trying to fetch the table, print each opening post, then print responses underneath them indented. This is what I have so far, but it doesn't work:

while ($row = mysqli_fetch_assoc($result)) {
        if ($row["parent"] == 0) {
        echo "" . $row["id"] . " | " . $row["post"] . "<br>"; }
                while ($row2 = mysqli_fetch_assoc($result)) {
                        if ($row["id"] == $row2["parent"]) {
                                echo ">" . $row2["id"] . " | " . $row2["post"] . "<br>"; }}}

I've been looking around on the internet for a solution for a while now to no avail; I've tried putting the mysql result into an array, but that didn't work either. I would really appreciate if someone could point me in the right direction. Thanks in advance!

This isn't a duplicate of How to go through mysql result twice? as I want keep going through the table instead of resetting after every OP.

Possible duplicate of [How to go through mysql result twice?](https://stackoverflow.com/questions/6439230/how-to-go-through-mysql-result-twice) — Daniel, Dec 23 '17 at 21:03
Could you put a few more data points in your table? The two rows I see right now don't quite make sense. If testmessage had `id` = `2`, it would make sense. But with `id` = `0`, I'm not sure that your data is structured correctly. — Tim Morton, Dec 23 '17 at 21:14
That's far to simplistic an approach for adjacency list. http://www.mysqltutorial.org/mysql-adjacency-list-tree/ — ArtisticPhoenix, Dec 23 '17 at 21:16
@bugsy Is one of the issues that `$row['post']` isnt displaying? — The Codesee, Dec 23 '17 at 21:26

score -1 · Accepted Answer · answered Dec 23 '17 at 21:36

With one statment create three array for id , post and parent then add each row data to these arrays and use to for loop to create the display result somthing like this code may be work.

$id=array();
$post=array();
$parent=array();
while ($row = mysqli_fetch_assoc($result)) {
$id[]=$row["id"];
$post[]=$row["post"];
$parent[]=$row["parent"];
}
for($i=0;$i<count($id);$i++){
if($parent[$i]==0){
echo $parent[$i];
for($j=0;$j<count($parent);$j++){
if($parent[$j]==$id[$i]){
echo $post[$j];
}
}
}

Got it working. Didn't think to create a separate array for each. Thank you! — bugsy, Dec 23 '17 at 21:46

Iterating twice over same mysql query in PHP

1 Answers1