Run MySql loop through PHP

Hi!

Is it possible to execute a mysql loop statement with PHP? Something like:

 SET @v1=100;
 WHILE @v1 > 0 DO
    SET @v1 = @v1 - 1;
  END WHILE;

Code (markup):

How to run that? mysql_query?

 

What do you mean by mysql loop? What do you want to do in this loop?

If you just want a loop that executes 100 times, do :

for ($i=0; $i<100; $i++) { 
//..
}

PHP:

 

This is not a realistic problem, just the peace of the SQL I don't know how to solve. The real problem is this:

I have a mysql table called 'members' with cols 'userid' and 'referrerid'. This is a classical parent-children structure. I'd like to know the whole user's upline (from a user up to the top, to the user with no referrer) and the whole downline: members that are referred by the user, members that are referred by the members of that user, members... all the way to the bottom.

I know this can be done with one or two temporary tables and a loop. Do you think I have to write a stored procedure for this?

Cheers

 

It could definitely be done with a few queries and loops in the application. But, you would need a sp for it if you wanted to keep it off the application level.

 

i think you can simply do this by queries and can display by for loop, you need to show your table strucutre so ppl can better give you what you need... sp are not necessary is this regards it over loads.

 

Here is some sample data from table 'users':

userid  | username     | referrer
--------------------------------------------
1       | John         | 
2       | Brian        | John 
3       | Jane         | John 
4       | Erik         | John 
5       | Mary         | Brian
6       | James        | Brian
7       | David        | Jane
8       | Richard      | Jane
9       | Alex         | David
10      | Paul         | David
11      | Linda        | David
12      | Carol        | Paul 
13      | Steven       | Paul 

Code (markup):

The input is always 'username':

For username "David" I want to get:

• 1st query/SP - UPLINE: John, Jane
• 2nd query/SP - DOWNLINE: Alex, Paul, Linda, Carol, Steven

For user "Brian" I want to get:

• 1st query/SP - UPLINE: John
• 2nd query/SP - DOWNLINE: Mary, James

For user "Paul" I want to get:

• 1st query/SP - UPLINE: John, Jane, David
• 2nd query/SP - DOWNLINE: Carol, Steven

.
.
.
and so on.

Hope it is more clear now. Thanks.

 

anybody????

 

First, how can 1 account David has 2 uplines??

Alright, here is the php code, check if that's you need (my favorite sentence ) (untested though, remember change table name)

//mysql connection

$query="SELECT username, referrer FROM users ORDER BY userid ASC";
$result=mysql_query($query);
while ($row=[mysql_fetch_assoc($result)) {
$username=$row[username];
$upline=$row[referrer];
$html="<div>_ Username: $username <br>_ Upline: $upline <br> _ Downline: ";
$query="SELECT username FROM users WHERE (referrer='$username')";
$result=mysql_query($query);
while ($day=mysql_fetch_assoc($result)) {
$html.=$day[username]."<br>";
}
$html.="</div>";
echo $html;
}

PHP:

 

Thanks for your reply.

Cause I need the upline from the selected user to the top:
- David's referrer is Jane
- Jane's referrer is John
- John has no referrer, so we've reached the top

However I wanted to do this with one mysql block or procedure/function and I believe I need a loop inside this block, or is there an easier solution?

 

Wow, so you need to script to trace back the referrer until it's the top account. It will require 'recurring' (well, not sure I spell it right) though, maybe for downlines also.