Build a multilevel table

[The Little Guy]

So, I need to build a table based on this table:

create temporary table downline (
depth int, 
member_id int, 
full_name varchar(50), 
level int, 
referrer_id int, 
country char(2)
);

 

I need to make a table to show each members downline (the people they referred to the site), it needs to be unlimited levels deep, and we have some members that have 25+ levels in their downline, where level 20 has 15,000+ people.

 

Level 1 = your downline

Level 2 = your donwline's downline

Level 3 = your downline's downline's downline

ect.

 

I have this loop (after the temp table is built) which needs to build a table on the page.

 

 

So, I can not get this table display right for the members downline, any suggestions?

 

// Build the current users downline and add it to the temporary table
$downline = mysql_query("select * from downline order by depth");
while($row = mysql_fetch_assoc($downline)){
if($row['depth'] != $depth){
	$depth_id = $row['depth'];
	$content .= "<tr id='main_lvl_$depth_id' class='hiddenInfo' onclick='showHide(this);'><td style='text-align: center;color:#4d53c1;font-weight: bold;' class='w_border'><span class='signs_s'>".$this->dict['mLevels_Level']." $depth_id : </span><span class='signs'>".count($down)." ".$this->dict['mLevels_Members']." </span></td></tr><tr id='lvl_$depth_id' style='display:none;'><td class='w_border' style='text-align: center;'><table style='width:100%;'>";
	$i = 0;
	$content .= "<tr>
		<th>Member ID (Sponsor ID)</th>
		<th>Member Name</th>
		<th>Country</th>
		<th>Member Level</th>
	</tr>";
}
$class = ($i%2) ? 'color1NL' : 'color2NL';
$content .= "<tr id='_{$key}_' class='$class'><td class='width33'><a name='_".$row['member_id']."'></a><a href='?mid={$row['member_id']}'>#{$row['member_id']}</a> (<a href='?mid=".$row['referrer_id']."'>#".$row['referrer_id']."</a>)</td><td class='width33'>".$row['full_name']."</td><td>".$row['country']."</td><td class='width33'>".$row['level']."</td></tr>";
$i++;
if($row['depth'] != $depth){
	$content .= "</table></td></tr>";
}
$depth = $row['depth'];
}

 

The following attachment has a working copy. The reason we don't stay with this is because it doesn't work with members who have large downlines, we run out of memory in php, and this hopefully will solve this problem.

 

[attachment deleted by admin]

[Psycho]

So, if I understand you correctly, the code you have will display the content correctly but the problem you are having is that the script is timing out because of the volume of records? So, what EXACTLY is your request? If you are trying to prevent the time-out there is only so much you can do to make the code more efficient. But, I think a more important question is what is the value to the user to see 15K+ records on a single page? It would make more sense to me to show only a few levels deep on a page and showing links to display some of the deeper levels.

 

As far as your currently code, one idea to make it more efficient would be to set some of the static text as variables. You are adding a lot of text to the $content variable which is the same. Make that text a variable and add it to the $content variable so the PHP parser doesn't have to interpret the text. I'm not 100% sure that would be more efficient, but it's worth testing.

 

Also, you seem to have a lot of nested tables. That is a killer for the web browser to process. You should consider changing how you compile the HTML.

[The Little Guy]

The reason it ran out of memory before was because we were storing the data in an array, so we decided to remove the array, and use a temporary table instead.

 

basically my request is building the table, I can not get it to display properly. I want each level to be group by depth.

The first header:

	if($row['depth'] != $depth){
	$depth_id = $row['depth'];
	$content .= "<tr id='main_lvl_$depth_id' class='hiddenInfo' onclick='showHide(this);'><td style='text-align: center;color:#4d53c1;font-weight: bold;' class='w_border'><span class='signs_s'>".$this->dict['mLevels_Level']." $depth_id : </span><span class='signs'>".count($down)." ".$this->dict['mLevels_Members']." </span></td></tr><tr id='lvl_$depth_id' style='display:none;'><td class='w_border' style='text-align: center;'><table style='width:100%;'>";
	$i = 0;
	$content .= "<tr>
		<th>Member ID (Sponsor ID)</th>
		<th>Member Name</th>
		<th>Country</th>
		<th>Member Level</th>
	</tr>";
}

 

should only display one time for every level

 

This closes the block:

	if($row['depth'] != $depth){
	$content .= "</table></td></tr>";
}

 

I can not get it to put the members in the levels. the first attempt I could only get the first member,  the second they were all group individually, and the third they were all in the first group.

[Psycho]

Did you LOOK at the HTML source that was produced? You need to write your code so it is readable. I see some problems with how the HTML would be produced, but I'm not going to try and figure out exactly what it is supposed to look like. But, start by making your code readable in the PHP code and in the HTML that is output to the browser. Then work from there.

 

// Build the current users downline and add it to the temporary table
$downline = mysql_query("select * from downline order by depth");
$depth = false;
while($row = mysql_fetch_assoc($downline))
{
    if($row['depth'] !== $depth)
    {
        $depth_id = $row['depth'];
        $content .= "<tr id='main_lvl_$depth_id' class='hiddenInfo' onclick='showHide(this);'>\n";
        $content .=   "<td style='text-align: center;color:#4d53c1;font-weight: bold;' class='w_border'>\n";
        $content .=     "<span class='signs_s'>".$this->dict['mLevels_Level']." $depth_id : </span>\n";
        $content .=     "<span class='signs'>".count($down)." ".$this->dict['mLevels_Members']." </span>\n";
        $content .=   "</td>\n";
        $content .= "</tr>\n";
        $content .= "<tr id='lvl_$depth_id' style='display:none;'>\n";
        $content .= "<td class='w_border' style='text-align: center;'><table style='width:100%;'>\n";

        $i = 0;

        $content .= "<tr>
            <th>Member ID (Sponsor ID)</th>
            <th>Member Name</th>
            <th>Country</th>
            <th>Member Level</th>
            </tr>";
    }

    $class = ($depth_row%2) ? 'color1NL' : 'color2NL';
    $content .= "<tr id='_{$key}_' class='$class'>\n";
    $content .=   "<td class='width33'>\n";
    $content .=     "<a name='_".$row['member_id']."'></a>\n";
    $content .=     "<a href='?mid={$row['member_id']}'>#{$row['member_id']}</a> \n";
    $content .=     "(<a href='?mid=".$row['referrer_id']."'>#".$row['referrer_id']."</a>)\n";
    $content .=   "</td>\n";
    $content .=   "<td class='width33'>".$row['full_name']."</td>\n";
    $content .=   "<td>".$row['country']."</td>\n";
    $content .=   "<td class='width33'>".$row['level']."</td>\n";
    $content .= "</tr>\n";
    $depth_row++;

    if($row['depth'] !== $depth && $depth !== false)
    {
        $content .= "</table></td></tr>\n";
    }

    $depth = $row['depth'];
}

[The Little Guy]

I got it, I had to re-order my if statements.