[SOLVED] db content pull and output

[sasori]

hello I have a problem pulling something from database in output it..before i ask my question let me show you the structure and content of my db and my code.

//subjects table
,'id','menu_name','position','visible',,,
,'1','About Me','1',1',,,
,'2','Portfolios','2','1',,,
,'3','Services','3','1',,,,

//pages table
,'id','subject_id','menu_name','position','visible',,,
,'1','1','Educational Background','1','1',,,
,'2','1','Work Experience','2','1',,,
,'3','1','Skills','3','1',,,
,'4','2','test.com','1','1',,,

 

and here's my code to pull and output

 

                <?php
                    $result = mysql_query("SELECT * FROM subjects ORDER BY position ASC",$connection);
                    if(!$result){
                    die("subject query failed: ". mysql_error());
                    }
                    while($row = mysql_fetch_array($result)){
                        echo "<li>{$row["menu_name"]}</li>";
                        $result = mysql_query("SELECT * FROM pages WHERE subject_id={$row["id"]}
                        ORDER BY position ASC",$connection);
                        echo "<ul>";
                        while($row = mysql_fetch_array($result)){
                          echo "<li>{$row["menu_name"]}</li>";
                        }
                        echo "</ul>";
                    }
                ?>

 

here's the screenshot of the output

why does my code doesn't output the other 2 subjects

and the 4th test.com under the portfolios subject?

 

 

[AndyB]

Try this:

 

<?php
                    $result = mysql_query("SELECT * FROM subjects ORDER BY position ASC",$connection);
                    if(!$result){
                    die("subject query failed: ". mysql_error());
                    }
                    while($row = mysql_fetch_array($result)){
                        echo "<li>{$row["menu_name"]}</li>";
                        $result2 = mysql_query("SELECT * FROM pages WHERE subject_id={$row["id"]}
                        ORDER BY position ASC",$connection);
                        echo "<ul>";
                        while($row2 = mysql_fetch_array($result2)){
                          echo "<li>{$row2["menu_name"]}</li>";
                        }
                        echo "</ul>";
                    }
                ?>

 

Then $result and $row will no longer be confused in the two queries.

[exally]

because you are whiping the result out when you return the result of the second query