Nested While Loop Issue

Jako · 08-06-2008, 06:06 AM

I'm having trouble with nested while loops.

Here's what I am currently doing.

PHP Code:


			
// sql query

$sql = 'SELECT * FROM rank LEFT JOIN national on national.n_id = rank.rank_nation ORDER BY rank ASC';

$result = mysql_query($sql) or dies(mysql_error());



// get details of all nations

$sql_nation = "SELECT * FROM national ORDER BY national_name ASC";

$nation_list = mysql_query($sql_nation) or die (mysql_error());

any my tested loops look something like this.

PHP Code:


			
    <? while($row = mysql_fetch_assoc($result)) { ?>

    <tr>

        <td><? echo $row['rank']; ?></td>

        <td><input name="rank" type="text" value="<? echo $row['rank']; ?>" size="20" maxsize="100" /></td>

        <td><select name="national_id"><option selected value="<? echo $row['national_id']; ?>"><? echo $row['national_name']; ?></option>

<option>-------</option>

 <? 

 while($r = mysql_fetch_assoc($nation_list)) { ?><option value="<? echo $r['n_id']; ?>"><? echo $r['national_name']; ?></option>

<? } ?> </select></td>

        <td><input name="points" type="text" value="<? echo $row['rank_points']; ?>" size="20" maxsize="100" /></td>

    </tr>

    <? } ?>

How can I fix this? Right now the first loop is working alright, but the nested while loop only works once.

delayedinsanity · 08-06-2008, 06:31 AM

Because it only has one result set to work with. If you need it to run the loop on that second query each time the first loop runs, you need to nest the query inside the first loop so that $nation_list will repopulate itself each time - alternatively, and better yet, as it only runs the query once, you could run the nested loop outside of the first loop and save the results to a variable, then just echo that variable inside the first loop each time. Ala;

PHP Code:


			
<?php
    $sql_nation = "SELECT * FROM national ORDER BY national_name ASC";
    $nation_list = mysql_query($sql_nation) or die (mysql_error());  

    $nation_list = '';

    while($row = mysql_fetch_assoc($nation_list))
    {
        $nation_list .= '<option value="'.$row['n_id'].'">'.$row['national_name'].'</option>'."\n";
    }

    $sql = 'SELECT * FROM rank LEFT JOIN national on national.n_id = rank.rank_nation ORDER BY rank ASC';
    $result = mysql_query($sql) or die (mysql_error()); 

    while($row = mysql_fetch_assoc($result))
    {
?>
    <tr>
        <td><?php echo $row['rank']; ?></td>
        <td><input name="rank" type="text" value="<?php echo $row['rank']; ?>" size="20" maxsize="100" /></td>
        <td><select name="national_id"><option selected value="<?php echo $row['national_id']; ?>"><?php echo $row['national_name']; ?></option>
            <option>-------</option>
            <?php echo $nation_list; ?>
            </select></td>
        <td><input name="points" type="text" value="<?php echo $row['rank_points']; ?>" size="20" maxsize="100" /></td>
    </tr>
<?php } ?>

-m

Jako · 08-06-2008, 06:51 AM

Thanks m,

I'm getting this error now.

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource on line 31

Which is this line,

PHP Code:


			
while($row = mysql_fetch_assoc($nation_list))

// EDIT

Actually got it fixed by changing it to

PHP Code:


			
$nation .= '<option value="'.$row['n_id'].'">'.$row['national_name'].'</option>'."\n";

Guess since there was already a variable $nation_list it was getting screwy.

It's working great now. Thanks!

delayedinsanity · 08-06-2008, 07:13 AM

ahahah oops, I didn't realize I reused the same variable there. That's why I use hungarian notation, but I try to omit it in examples for people who don't use it.
-m