PHP / MySQL - 列出了谁招募的人

i'am working on a social project and we've just startet the registration for members. Our members could recriute new members, so we have a database where is a field "recruit_by".

The DB fiels looks like this:

id | name | email | code | recruit_by

We now want to generate a list of the structure, who recruited whom on all levels.

I've tried to get this done, but it seems my skills are to less to get this done. I get a list, but this is totaly unsorted :-(

Thanks for your help!

<?PHP

mysql_connect("www.mysqlserver.net", "database1", "password") or die(mysql_error());
mysql_select_db("project_db1") or die(mysql_error());

echo "<ul>";

$result = mysql_query("SELECT * FROM registration") or die(mysql_error());
while($row = mysql_fetch_array($result))
    {
    echo "<li class=\"level0\">" . $row['id'] . " - " . $row['name'] . " - " . $row['email'] . " - " . $row['recruit_by'] . "</li>";

    // 1. Level
    $result2 = mysql_query("SELECT * FROM registration WHERE recruit_by LIKE " . $row['id']) or die(mysql_error());
    while($row2 = mysql_fetch_array($result2))
        {
        echo "<li class=\"level1\">1. " . $row2['id'] . " - " . $row2['name'] . " - " . $row2['email'] . " - " . $row2['recruit_by'] . "</li>";

        // 2. Level
        $result3 = mysql_query("SELECT * FROM registration WHERE recruit_by LIKE " . $row2['id']) or die(mysql_error());
        while($row3 = mysql_fetch_array($result3))
            {
            echo "<li class=\"level2\">2. " . $row3['id'] . " - " . $row3['name'] . " - " . $row3['email'] . " - " . $row3['recruit_by'] . "</li>";

            // 3. Level
            $result4 = mysql_query("SELECT * FROM registration WHERE recruit_by LIKE " . $row3['id']) or die(mysql_error());
            while($row4 = mysql_fetch_array($result4))
                {
                echo "<li class=\"level3\">3. " . $row4['id'] . " - " . $row4['name'] . " - " . $row4['email'] . " - " . $row4['recruit_by'] . "</li>";

                // 4. Level
                $result5 = mysql_query("SELECT * FROM registration WHERE recruit_by LIKE " . $row4['id']) or die(mysql_error());
                while($row5 = mysql_fetch_array($result5))
                    {
                    echo "<li class=\"level4\">4. " . $row5['id'] . " - " . $row5['name'] . " - " . $row5['email'] . " - " . $row5['recruit_by'] . "</li>";

                    }

                }

            }

        }

    }

echo "</ul>";

?>

First, you are selecting all registrations, that results all the recuited users also. I would suggest selecting only non-recuited users, assuming recruit_by is NULL, when this user is not recruited:

$result = mysql_query("SELECT * FROM registration WHERE recruit_by IS NULL") or die(mysql_error());

Secondly it is good to structure second level of recruits in another <ul></ul> tags like this:

<ul>
    <li>User #1
        <ul>
            <li>User #103, recruited by user #1</li>
            <li>User #142, recruited by user #1</li>
            <li>User #93, recruited by user #1
                <ul>
                    <li>User #992, recruited by user #93</li>
                </ul>
            </li>
        </ul>
    </li>
</ul>

This already gives you much better structure to work with and you can easily loop it in you PHP code (DRY - don't repeat yourself).

<?php


function createTree($level = 0, $recruiter_id = 0) {

    $return = "";
    if (!$recruiter_id) {
        $results = mysql_query("SELECT * FROM registration WHERE recruit_by IS NULL") or die(mysql_error());
    } else {
        $results = mysql_query("SELECT * FROM registration WHERE recruit_by LIKE " . $recruiter_id) or die(mysql_error());
    }

    // Check if there is any recruits at all?
    if (pg_num_rows($results) > 0) $return .= "<ul>
";
    else return "";

    while ($row = mysql_fetch_array($results)) {

        $return .= "<li class=\"level".$level."\">
";
        $return .= $level.". " . $row['id'] . " - " . $row['name'] . " - " . $row['email'] . " - " . $row['recruit_by']."
";

        // Add sub-recruits
        $return .= createTree($level+1, $row['id']);

        // Finish up the <li>
        $return .= "</li>
";

    }

    $return .= "</ul>
";
    return $return;

}

?>