[SOLVED] Populate Drop Down List with results from Database

[jcstanley]

Hi Guys

 

I am a bit stuck!

 

What I would ideally like is to have a drop down box called 'president' which is populated by selecting 2 columns 'fname' and 'surname' from the table called 'members'.

 

Once the arrow on the drop down is clicked, it contains both the fname and surname of the members, eg:

Joe Bloggs

John Smith

etc...

 

The other feature that I would like to have is when a member is selected in the drop down, lets say Joe Bloggs, the value in the column 'president' in the 'members' table for the row Joe Bloggs is changed to '1'.

(I guess this would have to be done via a submit button)

 

Any suggestions greatly appreciated.

 

Thanks in advance 

[redarrow]

javascript/ajax sorry.

 

<A HREF="jmouse.htm" onMouseover="window.status='Hi there!'; return true">Place your mouse here!</A> 

[scottybwoy]

I have a function for that :

<?php
function selectSpecificMenu($field1, $field2, $table,  $i) {
  	$sql = mssql_query("SELECT $field1, $field2 FROM $table")
	or trigger_error("SQL", E_USER_ERROR);

echo "<select action='submit' id='$field1' name='$field1' tabindex='$i'>";
while ($row = mssql_fetch_array($sql)) { // Loop through each element
	print("<option value='" . $row[0] . "'>" . $row[1] . "</option>");
}
echo "</select>";
  }
?>

 

Then you can call that function in your html like so :

<?php
<div>
<label for='select'>Select Menu :</label>
<?php selectSpecificMenu('col1', 'col2', 'table', 'tabindex (must be an integer)'); ?>
<input type='submit' name='select' id='select' value='Select'  />
</div>
?>

Then you need a function for you form data to do the next bit, happy programming ; )

[jcstanley]

Thanks for that function - but it only shows the surnames ie. field2 in the drop down list.  Is this correct?

 

Thanks

[boo_lolly]

here's a quick example of how you populate a dropdown menu from a database query:

<?php
        $sql = "SELECT column1, column2 FROM your_table";
        $query = mysql_query($sql) OR die(mysql_error());

        echo "<select name=\"president\">\n";
        while($row = mysql_fetch_array($query)){
                echo "<option value=\"{$row['column1']}\">{$row['column2']}\n";
        }
        echo "</select>\n":
?>

 

keep in mind that you will need this dropdown menu inside an HTML form. once you get your dropdown menu functioning, you'll need to update your database with an UPDATE query.

 

Thanks for that function - but it only shows the surnames ie. field2 in the drop down list.  Is this correct?

 

Thanks

 

yes, that is correct. if you check your source code from the html document, it will show that the <i>value</i> is the number you're looking for. the description displayed for each option in the dropdown menu is just a description and nothing more. the value is what will be sent to the query.

[jcstanley]

I have managed to get it to display both the fname and surname in the drop down. 

The problem is now how do I post the 'memid' of the selected member in the drop down. I have tried this but no luck:

 

(Also I have added an IF statement to select the current president.  The IF section works but the ELSE section doesn't)

 

<?php		
echo '<form method="POST" enctype="multipart/form-data" action="set_president.php?memid='.$memid.'">';

$dbh=mysql_connect ("localhost", "username", "password") or die ('Database unavailable.  Please try again later. ' . mysql_error());

mysql_select_db ("database"); 

        $sql = "SELECT memid, fname, surname, president FROM members where president = '1'";

        $query = mysql_query($sql) OR die('Query Error');

$num_rows = mysql_num_rows($query);

echo "<select name=\"president\">\n";

if ($num_rows < 1)
{       
        	echo "<option selected>--- Select a member ---</option>";
}
else
{
	$name = $row['fname'] ." " .$row['surname'];
	echo "<option selected value=\"{$row['memid']}\">{$name}</option>";
}
mysql_close($dbh);


$dbh=mysql_connect ("localhost", "username", "password") or die ('Database unavailable.  Please try again later. ' . mysql_error());

mysql_select_db ("database"); 

        $sql = "SELECT memid, fname, surname FROM members";

        $query = mysql_query($sql) OR die('Query Error');

        while($row = mysql_fetch_array($query)){
	$name = $row['fname'] ." " .$row['surname'];
                echo "<option value=\"{$row['memid']}\">{$name}\n";
        }
        echo "</select>\n";
  echo '<input type="submit" value="Set as President" name="SavePresident">';
echo '</form>';
?>

 

Thanks