While Loop to list each Item and it's Comments

[jimmyoneshot]

At the moment I have an sql query which retrieves an item and it's latest comments and it works well but I am now struggling to get it to output properly. My query is as follows:-

 

SELECT DISTINCT 

@rowtype:='1' AS rowType, //Type of row post or comment
s.id AS rowId, //used as the 'grouper' to group posts and their comments

s.id,
s.body, //text of the post
s.date,
s.aboutItemId, //id of the attached item

u1.id AS u1_id,
u1.username AS u1_username,
u1.name AS u1_name,
u1.surname AS u1_surname

FROM z_posts AS s

LEFT JOIN z_users AS u1 ON s.id1 = u1.id

UNION

SELECT

@rowtype:='2' AS rowType,
l.id2 AS rowId,
  
l.id, 
l.body, 
l.date,
l.id1 AS u1_id,
NULL AS u1_username,
NULL AS u1_name,
NULL AS u1_surname

FROM    (
        SELECT  id2,
                COALESCE(
                (
                SELECT  id
                FROM    z_postComments li
                WHERE   li.id2 = dlo.id2
                ORDER BY
                        li.id2, li.id
                LIMIT 14, 1
                ), CAST(0xFFFFFFFF AS DECIMAL)) AS mid
        FROM    (
                SELECT  DISTINCT id2
                FROM    z_postComments dl
                ) dlo
        ) lo, z_postComments l
WHERE   l.id2 >= lo.id2
        AND l.id2 <= lo.id2
        AND l.id <= lo.mid

ORDER BY rowId DESC, rowType ASC, date DESC

 

What I want is to output the posts as lis in a ul with the comments for each item in their own ul within each post li so it looks like this:-

 

<ul>

  <li>Post2

      <ul>

        <li>Comment 2 on post 2</li>

        <li>Comment 1 on post 2</li>

      </ul>

  </li>

  <li>Post1

      <ul>

        <li>Comment 3 on post 1</li>

        <li>Comment 2 on post 1</li>

        <li>Comment 1 on post 1</li>

      </ul>

  </li>

</ul>

 

How can I create this this via php?

[kicken]

Keep track of the last post heading and when it changes output a new header

 

Something like this.  Just typed off the top of my head.

$lastPostId = null;
echo '<ul>';
while ($row=$res->fetch()){
    if ($lastPostId != $row['rowId']){
        if ($lastPostId !== null){
          echo '</ul></li>'; //close previous
        }
        echo '<li>'.$row['rowId'].'</li><ul>';
        $lastPostId = $row['rowId'];
    }
    echo '<li>'.$row['comment'].'</li>';
}
echo '</ul></li></ul>';

       

[ocpaul20]

or in pseudo-code something like this (also off the top of my head)

 

output <ul>

 

1. is this a new topic?

if this first time through? N=>output </ul>

Y =>output <li>topic line</li> + <ul>

N=><li>output message line</li>

is this the end? N=>goto step 1

 

output </ul>

[jimmyoneshot]

Cheers for the suggestions guys. It's one of those things which I thought would be easy but now seems complex mainly due to the fact that comments need to be grouped inside their items in the output but they are obviously not this way inside the query result. Good idea about checking the previous id aswell.

[jimmyoneshot]

Starting to realize that this is hard as hell 

[jimmyoneshot]

I have this at the moment:-

 

<?php $postId=0;?>
<?php while($row = mysql_fetch_assoc($result)){?>

<?php

if($row['rowId']!=$postId && $row['rowType']==1){
if ($postId !== 0){
	echo 'POST END</br>';
    }	
echo 'POST START</br>';	
echo $row['body'].'</br>';
$postId = $row['rowId'];	
}else if($row['rowId']==$postId && $row['rowType']==2){
echo $row['body'].'</br>';
}

?>

<?php }echo 'POST END</br>'//while($row = mysql_fetch_assoc($result))?>