PHP/MySQL - 列出谁招募了谁

Question

我正在开展一个社交项目，我们刚刚开始成员(member)注册。我们的成员(member)可以招募新成员(member)，因此我们有一个数据库，其中有一个字段“recruit_by”。

数据库字段如下所示:

id | name | email | code | recruit_by

我们现在想要生成一个结构列表，说明谁在各个级别上招募了谁。

我已经尝试完成这件事，但似乎我的技能不足以完成这件事。我得到了一个列表，但这完全是未排序的:-(

感谢您的帮助!

<?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>";

?>

Answer 1

首先，您选择所有注册，这也会导致所有重新招募的用户。我建议仅选择未招募的用户，假设recruit_by为NULL，当该用户未被招募时:

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

其次，最好在另一个

标签中构建第二级新兵，如下所示:

<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>

这已经为您提供了更好的结构，并且您可以轻松地在 PHP 代码中循环它(DRY - 不要重复)。

<?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>\n";
    else return "";

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

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

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

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

    }

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

}

?>