MySQL查询与族层次结构/布局相关

Question

I have some data arranged in this format.

SR | NAME            | FATHER NAME       | ADDRESS    | GROUP
1  | SAM FISHER      | MR. KINGSLEY      | PARK VIEW  | USER   
2  | JOHN FISHER     | MR. KINGSLEY      | PARK VIEW  | USER 
3  | KADINAL         | MR. BEN           | PARK VIEW  | USER

Here 1 & 2 are children of Mr. Kingsley have same address and group. But 3rd one is not a children of Mr. Kingsley but have same address and group.

I need such query so that I can get sibling report for such data.

Output should be like this

Sibling Report

SR | NAME            | FATHER NAME       | ADDRESS    | GROUP
1  | SAM FISHER      | MR. KINGSLEY      | PARK VIEW  | USER  
     JOHN FISHER
-------------------------------------------------------------
2  | KADINAL         | MR. BEN           | PARK VIEW  | USER

Right now I am using mysql query

SELECT id, fat_name, sec, address, class, mobile,
  GROUP_CONCAT(DISTINCT stu_name order by class ASC SEPARATOR ', ' ) stu_name,
  GROUP_CONCAT(DISTINCT class order by class ASC SEPARATOR '<br>' ) class,
  GROUP_CONCAT(DISTINCT sec order by class ASC SEPARATOR '<br>' ) sec,
  GROUP_CONCAT(DISTINCT address order by class ASC SEPARATOR '<br>' ) address,
  GROUP_CONCAT(DISTINCT adm_no order by class ASC SEPARATOR '<br>' ) adm_no
FROM student
where class <> 'OUT' and session='".$session."'
GROUP BY fat_name
order by class DESC

PHP:

<?php
echo "<table id='testTable'><tr>";
$count = 1;

while ($row = mysql_fetch_array($sql, MYSQL_ASSOC))
    {
    extract($row);
    $stu_name = $row['stu_name'];
    $fat_name = $row['fat_name'];
    $address = $row['address'];
    echo "<td style='width:33%;'><br/>" . $stu_name . "<br/>" . $fat_name . "<br/>" . $address . "<br/><br‌​/></td>";
    if ($count++ % 3 == 0)
        {
        echo "</tr><tr>";
        }
    }

echo "</tr></table><br/><br/><br/>";

It also has a condition that if John Fisher is included with Sam Fisher on the top then John will not be repeated at the bottom.

Answer 1

Try (untested):

echo "<table id='testTable'><tr>";
$count = 1;
$keep_track = array(); //array to keep track of father names already displayed
while ($row = mysql_fetch_array($sql, MYSQL_ASSOC))
{
    extract($row);
    $stu_name = $row['stu_name'];
    $fat_name = $row['fat_name'];
    $address = $row['address'];

    //over here we check if the father name has been displayed already,
    //initially it will be false
    if(array_key_exists($fat_name,$keep_track)){
        //if it has been mentioned, skip all fields and display only 'stu_name'
        echo "<td style='width:33%;'><br/>" . $stu_name . "<br/><br/><br/><br‌​/></td>";
    }
    else
    {
        //if it hasn't been displayed, show all fields
        echo "<td style='width:33%;'><br/>" . $stu_name . "<br/>" . $fat_name . "<br/>" . $address . "<br/><br‌​/></td>";
        //record father name in array so that in next loop
        //it is not displayed again
        $keep_track[] = $fat_name;
    }
    if ($count++ % 3 == 0)
    {
        echo "</tr><tr>";
    }

}

echo "</tr></table><br/><br/><br/>";