Member Avatar for codewalkz

It's like a tree pyramid. I want to get the members sponsored by the current user. The result will be used to get its sponsored members and so on until everyone is read. How can I achieve this? I currently have this code but I can only go to level 8 since its a lot of work. I thought of recursion but I have no idea how to do it. I tried to study it but it's quite hard. can anyone please help me on this? thanks

$parent = $_GET['cued'];
$result = mysql_query("SELECT id FROM users WHERE agent = '$parent'");
  	$output = array();
 	while ($row = mysql_fetch_array($result)) {
    $output[] = "sponsor = '".$row['id']."' ";
	}
	$sponsored = implode(' or ', $output);

// then right here, I use $sponsored to query the members sponsored by the result above..
Edited by codewalkz because: n/a
  • 2 Contributors
  • 5 Replies
  • 138 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by cwarn23

Dani AI

Generated

Short summary and practical fixes based on the thread: is trying to walk a sponsor/child tree by issuing a query per level and building long "OR" clauses; tried looping but still ran into long runtimes. Two modern, reliable approaches follow — pick the one that fits the stack and data size.

If your database supports recursive CTEs (MySQL 8+, MariaDB 10.2+), get every subordinate in one query. This offloads recursion to the database, avoids many round trips, and is fast when sponsor is indexed:

WITH RECURSIVE descendants AS (
  SELECT id FROM users WHERE id = ?
  UNION ALL
  SELECT u.id FROM users u
  JOIN descendants d ON u.sponsor = d.id
)
SELECT id FROM descendants WHERE id != ?;

If you cannot use a CTE, traverse in PHP but do it safely and efficiently: use PDO or mysqli prepared statements, an explicit stack or queue (so you avoid PHP stack overflow), and a visited map to prevent cycles. Do not build huge OR strings or use the deprecated mysql_* extension.

function getDescendants(PDO $pdo, $rootId) {
  $stmt = $pdo->prepare('SELECT id FROM users WHERE sponsor = ?');
  $seen = [];
  $stack = [$rootId];
  $result = [];
  while ($stack) {
    $parent = array_pop($stack);
    if (isset($seen[$parent])) continue;
    $seen[$parent] = true;
    $stmt->execute([$parent]);
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
      $child = (int)$row['id'];
      if (!isset($seen[$child])) {
        $result[] = $child;
        $stack[] = $child;
      }
    }
  }
  return $result;
}

Practical notes: always use prepared statements and index sponsor. Guard against cycles and set sensible time/memory limits for very large trees. For heavy read loads consider a closure table or materialized path so reads become trivial. For the simplest fix to the code shown in the thread: stop building OR chains, switch to one of the approaches above, and replace mysql_* calls with PDO/mysqli.

Recommended Answers

All 5 Replies

Member Avatar for cwarn23

Could you please add comments into your code so I can see how your code relates to your context. As it is your code looks totally different to what you are describing so please add comments into the code to make it more clear then I may be able to help.

Member Avatar for codewalkz 
// This is the Number 1 Member at the top of the Pyramid

$id = $_GET['cued'];

// Right here, I want to get the members who were sponsored by the Number 1 using his ID

$result = mysql_query("SELECT id FROM users WHERE sponsor = '$id'");
  	$output = array();
 	while ($row = mysql_fetch_array($result)) {
    $output[] = "sponsor = '".$row['id']."' ";
	}
	$sponsored = implode(' or ', $output);

// The $sponsored will look like this "sponsor = '2' or sponsor = '3' or sponsor = '4' and so on...
// I will now use $sponsored in my WHERE statement

$result = mysql_query("SELECT id FROM users WHERE ($sponsored)");
  	$output = array();
 	while ($row = mysql_fetch_array($result)) {
    $output[] = "sponsor = '".$row['id']."' ";
	}
	$sponsored = implode(' or ', $output);

// Then the result of the 2nd Query will be used to the 3rd query  and so on...

Basically, I am using the result of each query to do another query until all the sponsored members are exhausted. ( It is by level )

Thanks a lot!

Member Avatar for cwarn23

Try the following

<?php
// This is the Number 1 Member at the top of the Pyramid
 
$id = mysql_real_escape_string($_GET['cued']);
 
// Right here, I want to get the members who were sponsored by the Number 1 using his ID
 
$r = mysql_query("SELECT `id` FROM `users` WHERE `sponsor` = '$id'");
  	$output = array();
 	while ($row = mysql_fetch_array($r)) {
    $output[] = 'sponsor = "'.mysql_real_escape_string($row['id']).'" ';
	}
	$sponsored = implode(' or ', $output);
$re=mysql_query('SELECT * FROM `users`');
$count=mysql_num_rows($re);
// The $sponsored will look like this "sponsor = '2' or sponsor = '3' or sponsor = '4' and so on...
// I will now use $sponsored in my WHERE statement

while(mysql_num_rows($r)<$count){
$r = mysql_query("SELECT `id` FROM users WHERE ($sponsored)");
  	$output = array();
 	while ($row = mysql_fetch_array($result)) {
    $output[] = '`sponsor` = "'.mysql_real_escape_string($row['id']).'" ';
	}
	$sponsored = implode(' or ', $output);
 }
// Above loops through the 2nd, 3rd, 4th etc Query until all rows in the table are matched.
Member Avatar for codewalkz

It seems that the query took so long and recurred so many times.

Member Avatar for cwarn23

It seems that the query took so long and recurred so many times.

Not much you can do about that especially if its a large table.
But you could always try removing line 21 of my code to increase the speed ( $output=array() ).

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.