PHP / MySQL - Nested List by recommendations / IDs

Question

We have on a social project a member database, which includes, which member recommended an other member. The fields of the database looks like this:

id | name | email | code | recruit_by

Now we want to print a nested list of the structure, who recommended whom on all deep levels.

We didn't get it running with the following code:

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

?>

The `mysql_*` functions are **no longer maintained** and shouldn't be used in any new codebase. It is being phased out in favor of newer APIs. Instead you should use [**prepared statements**](https://www.youtube.com/watch?v=nLinqtCfhKY) with either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/msqli). — tereško, Sep 21 '13 at 20:45
@tereško That's not enough. You might want to recommend him a SoC, MVC and Data Mappers — Yang, Sep 21 '13 at 21:36
@DaveJust there is a difference between basic security and concepts of application architecture. — tereško, Sep 21 '13 at 21:41

score 0 · Answer 1 · edited May 23 '17 at 12:20

Very similar to an answer i gave here: MySQL best practice: SELECT children recursive as performant as possible?

Again not the best use case for MySQL but yeah....

Dont do so many queries, do one "SELECT * FROM registration"

Go through the result to build this as a tree like...

// use a database query to get this member info, here a simplified version
$member = array();
$member[] = array('id' =>'2', 'recruit_by' =>'1');
$member[] = array('id' =>'3', 'recruit_by' =>'2');
$member[] = array('id' =>'4', 'recruit_by' =>'3');
$member[] = array('id' =>'9', 'recruit_by' =>'1');

//use php to build a array containing all the recruit-relationships as a tree
function buildtree($member) {
    $ref = array();
    foreach($member as $mem){
        $member_id = $mem['id'];
        $parent = $mem['recruit_by'];
        if(!is_array($ref[$member_id])) $ref[$member_id] = array('id' => $member_id);
        if(!is_array($ref[$parent])) $ref[$parent] = array('id' => $parent);
        $ref[$parent]['child'][] = &$ref[$member_id];
    }
    return $ref;
}

$tree = buildtree($member);

/// use a recursive function to output the tree
function echotree($tree, $parent = 0) {
    foreach ($tree as $t) {
        if($parent){
            echo "$parent recruited " . $t['id'] . '<br>';

        }
        if(is_array($t['child']) && !$parent){
            echotree($t['child'],$t['id']);
        }
    }
}

echotree($tree);

will give you:

2 recruited 3
1 recruited 2
1 recruited 9
3 recruited 4

should not be too hard to put this in whatever layout you wish.

Joschua, thanks for the fast feedback! I need this as a deeper structure and as list like this: `- User1` * User2 (Recommed by 1) * User3 (Recommed by 1) - User4 (Recommed by 3) - User5 (Recommed by 3) * User6 (Recommed by 1) - User7 (Recommed by 6) - User8 (Recommed by 7) - User9 - User10 * User11 (Recommed by 10) * User12 (Recommed by 10) ` — Mirco, Sep 21 '13 at 22:31
Hmm, didn't get it formated... hope you understand it anyway :-) Thanks a lot! — Mirco, Sep 21 '13 at 22:36

PHP / MySQL - Nested List by recommendations / IDs

1 Answers1