Populate A Drop Down Box With Values From Database!

[murtz]

Hey all.

 

Im trying to fetch values from the database and have them viewable via a list box.

 

Im using the code below..

 

<?php

	include("connect.php");

 	$result = mysql_query("SELECT * FROM module WHERE study_year = '1'");

?>
<table border="2" align="center" cellpadding="10" cellspacing="2" bordercolor="#D6D6D6">
 <tr bgcolor="#AFEBEF">
   <td height="30" bordercolor="#D6D6D6"><p class="style19" style="text-align: left;"> Modules</p></td>
   </tr>
   <?php			
while($row = mysql_fetch_array($result))
		{ 
?>
 <tr>
   <td><select name="readinglist">
       <option><?php echo $row["module_name"]; ?></option>

     </select></td>
   </tr>
   <?php }?>
</table>

At the moment.. Im getting the results in separate list boxes (1 per box). shown below..

 

 

Obviously.. I want all the results to show up in 1 list box only!.. Please help me guys!

[Stephen]

<?php

	include("connect.php");

 	$result = mysql_query("SELECT * FROM module WHERE study_year = '1'");

?>
<table border="2" align="center" cellpadding="10" cellspacing="2" bordercolor="#D6D6D6">
  <tr bgcolor="#AFEBEF">
    <td height="30" bordercolor="#D6D6D6"><p class="style19" style="text-align: left;"> Modules</p></td>
    </tr>
  <tr>
    <td><select name="readinglist">
    <?php			
while($row = mysql_fetch_array($result))
		{ 
?>
        <option><?php echo $row["module_name"]; ?></option>

    <?php }?>
      </select></td>
    </tr>
</table>

Try that now. I moved some things from in between the while loop.

[murtz]

It works!.. Thankyou SO much for such a quick reply!

[murtz]

Oh god Im stuck again! Now that I have the list box populated.. I want to use the value that I select as the value that goes in the WHERE clause in my select statement that gets all the books that belong to that module.

 

Im trying to do this..

 

$subject=$_POST['dropdownbox'];

$result = mysql_query("SELECT module_book.book_title, module_book.book_author, module_book.ISBN FROM module, module_book, reading_list WHERE module.module_name ='$subject' and module.module_code = reading_list.module_code");

 

It brings me back with nothing. If I replace the $subject in the query with the actual name of the module.. then I get back a result.

 

Am I right in assuming that data from the database in the dropdown box is not the same as text data that I put in myself?

 

Please help! 

[.josh]

if everything works when you hardcode it but not when you use the variable, then the problem is the variable.  Did you name your input 'dropdownbox' (spelled right?) in your form? did you use method = 'post' in your form tag?

[realjumper]

If you just do a straight echo of '$subject', what do you get?

[murtz]

Ok I sorted it!.. Just re did the code and it ended up working  ???

 

And once again.. (as usual).. once I've solved 1 problem.. I get to another annoying problem!

 

Im trying to add a new book to the reading list. Here are the tables I'm using..

 

module (module_code module_name, Study_year)

 

module_book(module_book_id, book_title, book_author, ISBN)

 

reading_list(reading_list_id, module_code, module_book_id)

 

 

The form to enter the book looks like this..

 

 

I need to enter the book title, author and isbn into the MODULE_BOOK table. Thats all straight forward.

 

The tricky part for me is to get the module code that matches with the Module_name that is selected, and post that into the Reading_List table!.

 

Im using the code below.. but i keep getting the value 'resource id #4' being inputted into 'module_code' in reading_list!

 

$title=mysql_real_escape_string(trim($_POST['book_title']));
$author=mysql_real_escape_string(trim($_POST['book_author']));
$module_name=mysql_real_escape_string(trim($_POST['module_name']));
$ISBN=mysql_real_escape_string(trim($_POST['ISBN']));
$require=mysql_real_escape_string(trim($_POST['require']));
$module_code = mysql_query("SELECT module_code FROM module WHERE module_name = 'data mining'");

$addbook = "INSERT INTO module_book (book_title, book_author, ISBN)
	VALUES('$title','$author','$ISBN')";

mysql_query($addbook)
or die('Error1:' . mysql_error());

// Check if the form has been submitted.
if($addbook) {$book_id = mysql_insert_id($connect);
    $addbook2 = "INSERT INTO reading_list (module_code, module_book_id, required)
	VALUES('$module_code','".$book_id."','$require')";
    $result2 = mysql_query($addbook2) or die('Error2:' . mysql_error());

    if($result2) echo("$module_code");
             }

 

as you can see.. Im trying to get the value of module code by making a query and getting the module_code that matches the module_name. Hopefully all of this makes sense!

 

By the way.. when I replace $module_code with $module_name.. the correct name value gets inserted!

 

Soooo confused!