nesting one while loop inside another. I'm failing at it.

schwim

Hi there everyone,

I'm trying to alter a donations tracking system to retrieve the user's username, instead of the first name in the paypal transaction. I'm failing miserably. Here's my latest iteration:

$sql="SELECT user_id,mc_gross FROM donation_paypal ORDER BY id DESC LIMIT 10 ";;
$result_set=mysql_query($sql) or die (mysql_error());
$numr=mysql_num_rows($result_set);
if($numr>=1){
	while($rows=mysql_fetch_array($result_set)){

	$user_id = $rows['user_id'];

	$sql="SELECT username FROM phpbb_users WHERE user_id='$user_id'";;
	$result_set=mysql_query($sql) or die (mysql_error());
	while($rows=mysql_fetch_array($result_set)){
		$username=$row['username'];

		echo("
		<tr>
			<td align='left'>".$username."</td>
			<td align='right'>$".$rows['mc_gross']."</td>
		</tr>");
	}
}
}

Which is resulting in blank content both for username and for rows['mc_gross']. Here's the initial code(that worked for printing the first name and gross.):

$sql="SELECT first_name,mc_gross FROM donation_paypal ORDER BY id DESC LIMIT 10 ";;
$result_set=mysql_query($sql) or die (mysql_error());
$numr=mysql_num_rows($result_set);
if($numr>=1){
	while($rows=mysql_fetch_array($result_set)){

echo("
	<tr>
		<td align='left'>".$rows['first_name']."</td>
		<td align='right'>$".$rows['mc_gross']."</td>
	</tr>");

}
}

Hopefully you can see that I tried to insert another while loop inside the original to try to get the username based on the user_id gathered from the first. Echoing the sql statements results in what I thought was a valid statement, but I'm just getting empty values for both.

Could someone show me how to properly do this? I've run out of ideas.

thanks,
json

halojoy

You are using $result_set for 2 different results.
You should use maybe $result1 and $result2.
Otherwise the second will overwrite the first.

It is actually same thing why we use
$i for one outer loop and $j as counter for inner loop

schwim

Thanks very much for that help halojoy, but I'm still managing to mess it up. I made the suggested change, then also defined $mc_gross and user_id, so they wouldn't be $row[''] variables.

now, when I process the page, the first username and mc_gross is showing, but the loop is getting caught, as I have three records in the db that should be returned, but it only printed the mc_gross of the first one.

Here's my altered code:

$sql="SELECT user_id,mc_gross FROM donation_paypal ORDER BY id DESC LIMIT 10 ";;
$result1=mysql_query($sql) or die (mysql_error());
$numr=mysql_num_rows($result1);
if($numr>=1){

while($rows=mysql_fetch_array($result1)){

	$user_id = $rows['user_id'];
	$mc_gross = $rows['mc_gross'];

	$sql="SELECT username FROM phpbb_users WHERE user_id='$user_id'";;
	$result2=mysql_query($sql) or die (mysql_error());
	while($rows=mysql_fetch_array($result2)){
		$username=$row['username'];

		echo("
		<tr>
			<td align='left'>".$username."</td>
			<td align='right'>$".$mc_gross."</td>
		</tr>");
	}
}
}

Any thoughts?

thanks,
json

schwim

I fixed it by moving the echo statement up one level.

thanks very much for your help with the overall problem. It's greatly appreciated.

thanks,
json

halojoy

That was better.
I made a new version, using result1, row1, result2, row2

Here you use user_id and ORDER BY id
but I guess id and user_id are 2 different values. So it is probasbly alright.
$sql="SELECT user_id,mc_gross FROM donation_paypal ORDER BY id DESC LIMIT 10";

I removed quotes around $user_id (a number) in
$sql="SELECT username FROM phpbb_users WHERE user_id=$user_id";

$sql="SELECT user_id,mc_gross FROM donation_paypal ORDER BY id DESC LIMIT 10";
$result1=mysql_query($sql) or die (mysql_error());
$numr=mysql_num_rows($result1);
if($numr>=1){

while($row1=mysql_fetch_array($result1)){
	$user_id  = $row1['user_id'];
	$mc_gross = $row1['mc_gross'];

	$sql="SELECT username FROM phpbb_users WHERE user_id=$user_id";
	$result2=mysql_query($sql) or die (mysql_error());
	while($row2=mysql_fetch_array($result2)){
		$username=$row2['username'];
		echo("
		<tr>
			<td align='left'>".$username."</td>
			<td align='right'>".$mc_gross."</td>
		</tr>");
	}
}
}