php / sql select list help please

[seandon01]

The objective of this is to create a dynamic select list...
My Current Code:
[code]<?
$chptrfile_query = mysql_query("SELECT DISTINCT file_name,chapter_id FROM video_file WHERE listing_id=1");
              $myresults = mysql_fetch_assoc($chptrfile_query);
              
              $chptrtitle_query = mysql_query("SELECT DISTINCT chapter_title,id FROM chapters WHERE id=$myresults[chapter_id]");
            $myresults2 = mysql_fetch_assoc($chptrtitle_query);
             echo  $myresults['file_name'];
             echo "<br>";
             echo $myresults2['chapter_title'];
?> [/code]

This Returns:

losfelizvillas (which i want to be the value of the select list)
Full Trailer (which i want to be the display name of the select list)


My issue is.. Looping through the results so all the results are in the select list rather then just the first.... I hope this make sense and that someone can guide me to my solution. Thank you so much in advance

[sanfly]

try putting $myresults2 into a while loop

eg:
[code]while($myresults2 = mysql_fetch_assoc($chptrtitle_query)){
             echo  $myresults['file_name'];
             echo "<br>";
             echo $myresults2['chapter_title'];
             // your option tags would go here, rather than the echos above
}[/code]

[seandon01]

thanks for the quick reply... unfortunately. that didnt do the trick. Returned the same results as the original code..

[sanfly]

Try this. How many results does it say are returned?

[code]<?    $chptrfile_query = mysql_query("SELECT DISTINCT file_name,chapter_id FROM video_file WHERE listing_id=1");
        $myresults = mysql_fetch_assoc($chptrfile_query);
              
        $chptrtitle_query = mysql_query("SELECT DISTINCT chapter_title,id FROM chapters WHERE id=$myresults[chapter_id]");
            $num = mysql_num_rows($chptrtitle_query);
            echo "$num results returned<br><br>";
            while($myresults2 = mysql_fetch_array($chptrtitle_query)){
                echo  $myresults['file_name'];
                 echo "<br>";
                echo $myresults2['chapter_title'];
                echo "<br><br>";
            }    ?>[/code]

[seandon01]

[!--quoteo(post=383696:date=Jun 14 2006, 02:50 AM:name=sanfly)--][div class=\'quotetop\']QUOTE(sanfly @ Jun 14 2006, 02:50 AM) [snapback]383696[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Try this. How many results does it say are returned?

[code]<?    $chptrfile_query = mysql_query("SELECT DISTINCT file_name,chapter_id FROM video_file WHERE listing_id=1");
        $myresults = mysql_fetch_assoc($chptrfile_query);
              
        $chptrtitle_query = mysql_query("SELECT DISTINCT chapter_title,id FROM chapters WHERE id=$myresults[chapter_id]");
            $num = mysql_num_rows($chptrtitle_query);
            echo "$num results returned<br><br>";
            while($myresults2 = mysql_fetch_array($chptrtitle_query)){
                echo  $myresults['file_name'];
                 echo "<br>";
                echo $myresults2['chapter_title'];
                echo "<br><br>";
            }    ?>[/code]
[/quote]
It says 1 result returned

[sanfly]

So are you wanting to loop through the results of the first query as well?

If so, try this:

[code]<?    $chptrfile_query = mysql_query("SELECT DISTINCT file_name,chapter_id FROM video_file WHERE listing_id=1");
        while($myresults = mysql_fetch_assoc($chptrfile_query)){
            $chptrtitle_query = mysql_query("SELECT DISTINCT chapter_title,id FROM chapters WHERE id=$myresults[chapter_id]");
            while($myresults2 = mysql_fetch_array($chptrtitle_query)){
                echo  $myresults['file_name'];
                 echo "<br>";
                echo $myresults2['chapter_title'];
                echo "<br>";
            }
        }    ?>[/code]

[seandon01]

[!--quoteo(post=383701:date=Jun 14 2006, 03:06 AM:name=sanfly)--][div class=\'quotetop\']QUOTE(sanfly @ Jun 14 2006, 03:06 AM) [snapback]383701[/snapback][/div][div class=\'quotemain\'][!--quotec--]
So are you wanting to loop through the results of the first query as well?

If so, try this:

[code]<?    $chptrfile_query = mysql_query("SELECT DISTINCT file_name,chapter_id FROM video_file WHERE listing_id=1");
        while($myresults = mysql_fetch_assoc($chptrfile_query)){
            $chptrtitle_query = mysql_query("SELECT DISTINCT chapter_title,id FROM chapters WHERE id=$myresults[chapter_id]");
            while($myresults2 = mysql_fetch_array($chptrtitle_query)){
                echo  $myresults['file_name'];
                 echo "<br>";
                echo $myresults2['chapter_title'];
                echo "<br>";
            }
        }    ?>[/code]
[/quote]
JACKPOT!!!!!! WOW.. I CANNOT THANK YOU ENOUGH!!!! THANK YOU THANK YOU THANK YOU!!!!
As you can tell i am fairly new with php and sql, didnt even think to my 2nd select statement inside of a loop.. Didnt even know that it was possible actually.. Thanks again...