nesting while fetches

[tragikx]

can someone be so kind as to show an example for nesting 2 queries...

ie.

while ($row = mysql_fetch_assoc($query1)) {
  echo $row['test1']."<br/>";
  while ($row = mysql_fetch_assoc($query2)) {
     echo $row['test']."<br/>";
  }
}

this doesnt seem to work...i only get the first row from both queries...

[planetsim]

Do these even have anything in common to nest? Also take a close look at what you have? $row twice, maybe try a unique named variable for the second. And nested queries usually have something in common.

[tragikx]

yes..sorry should have been more clear. i used $row2 = for the nested query..

they do have a common field...

'test1' would be the header of a section, and 'test' would be the members of another table that have 'test1' in common..

i'm trying to get a membership count...basically if i have 10 spots open for an event, i want it to list all 10 slots, and enter names of registered ppl.

Event1
1. bob
2. sam
3. mike
4.
5.
6.
7.
8.
9.
10.

so the results from $query1 would be the registered ppl, and the results from $query2 would be the total available slots numbered out.

hope that makes sense =)

[edcellgavin]

where will the number of actual slots for an event come from?

i assume it'll be in the header table...

the first query will list the header section and no. of slots\
and the second will list the registered people.

while ($row = mysql_fetch_assoc($query1)) {
echo $row['event']."<br/>";
$slots = $row['slots'];
$counter = 1; //initialize counter for every event
while ($row2 = mysql_fetch_assoc($query2)) {
echo $counter . "." . $row2['reg_name']."<br/>";
$counter++;
}

// issue this loop to print numbered available slots
// e.g. 4.-10. in your example
while ($counter < slots + 1) {
echo $counter . ".<br />" ;
$counter++;
}
}

[tragikx]

thanks edcellgavin,

that is kindof what i had down...realized that i had used a reserved word as a column name in my query...'limit' ..this was causing it to mess up my output.

using your example, i dont get seperation between events..so all users for multiple events are dumped into the first one.

ie.
Event1
1. bob
2. sam
3. mike
4. tom
5. george // george should be listed under Event2

Event2

Event3

[edcellgavin]

can u post the code?

[tragikx]

while ($row = $db->sql_fetchrow($events)) {

  $eventcount = 0;
  $backupcount = 0;
  $thisevent = $row['event'];

  echo '<br><u>'.$thisevent.'</u> (Limit: '.$row['eventmax'].')<br>'; 

  while ($rowb = $db->sql_fetchrow($attendees)) {

  $newevent = $rowb['event'];

  if ($thisevent = $newevent) {
    if ($eventlimit >= $row['max']) {
      $backupcount++;
      echo 'B'.$backupcount.'. ['.$rowb[name].'] <br>'; 
    }
    else {
      $eventlimit++;
      echo $eventlimit.'. '.$rowb['name'].'<br>'; 
    }
  }

  while ($eventlimit < $row['max']) {
    $eventlimit++;
    echo $eventlimit.'. <br>';
   }
}

[edcellgavin]

sorry for the delayed response, i guess by this time, you've figured out a solution...
nway, perhaps line
if ($thisevent = $newevent) {
is the one causing u trouble, since the $newevent will be assigned to $thisevent, rather than comparing it.

Also, do you have a 'reset' porocedure for for your second query such as mysql_data_seek($result, 0) to set the record pointer to the first record for every iteration of the first loop?

Last, I think you could do a join here so that you'll only have one query. The example below is not the exact query you might be looking for, but try to manipulate it.

$query = "SELECT Table1.event, Table1.eventmax, Table2.name FROM Table1 LEFT JOIN  Table2 ON Table1.event = Table2.event ORDER BY Table1.event, Table2.name";
$result = mysql_query($query, $db);
$curEvent    = '';
$event_count = 1;
$name_count  = 1;
$ret         = '';
while (list($db_event, $db_max, $db_name) = mysql_fetch_row($result)) {
	if ($db_event == $curEvent)  {
		$ret .= "$name_count. $db_name <br />";
	}
	else {
		// new event
		if ($name_count < $db_max && $curEvent != '') {
			for($i = $name_count; $i <= $db_max; $i++) {
				$ret .= "$i. ___________________<br />";
			}
		}
		$name_count = 1;
		$ret .= "<h1>$event_count. $db_event</h1>";
		$ret .= "$name_count. $db_name<br />";
		$event_count++;
	}
	$name_count++;
	$curEvent = $db_event;
}