Populating Multiple drop down list

srpayne

Hello,

I am trying to create two drop down lists which will be populated by my MySQL database. I want these lists to be identical. I can get the first list populated but the second is left empty. Here is my code:

$connection = mysql_connect("localhost","root","") or die ("Couldnt Connect to MySQL server");

$db = mysql_select_db("ward",$connection) or die ("Couldnt Select Database!");

$sql = "select first_name, last_name, home_phone, id from members where gender=\"M\" order by last_name asc";

$sql_result = mysql_query($sql,$connection) or die ("Couldnt Execute SQL query");

$row = mysql_fetch_array($sql_result);

echo "

<head>
<title>Untitled Document</title>
<meta http-equiv=\"Content-Type\" content=\"text/html; charset=iso-8859-1\">
</head>

<h1>Home Teaching Assignments</h1>

<select name=\"companion1\">
<option value=\"\">Select First Companion</option>
";

	while ($row = mysql_fetch_array($sql_result)) {
	$first_name = $row["first_name"];
	$last_name = $row["last_name"];
	$id = $row["id"];
	echo "
	<option value=\"$id\">$last_name, $first_name</option>
	";

}

echo "
</select>
<br>
<select name =\"companion2\">
<option value=\"\">Select Second Companion</option>
";

	while ($row = mysql_fetch_array($sql_result)) {
	$first_name = $row["first_name"];
	$last_name = $row["last_name"];
	$id = $row["id"];
	echo"
	<option value=\"id\">$last_name, $first_name</>
	";

}

echo "
</form>

</body>

Any help anyone could provide would be great! Oh, and if you want to see this file in use go to http://12.23.79.52/ht_assign.php

cordex

Reset the internal pointer on $row with reset($row); before you populate your second drop-down box.

Anon

actually, I think you can't use reset because $row is an object, not an array (I think!). So, istead you use:

mysql_data_seek ($row, 0);

Anon

it might be more efficient to read the data from the array, and build the select list into a string... and then you can print the string twice

Anon

Hello,

I tried

reset($row);

and also:

mysql_data_seek($row, 0);

Neither of which worked 🙁

Interestinly, I did find a work around. I created two more variables :

$sql2 = "select first_name, last_name, home_phone, id from members where gender=\"M\" order by last_name asc";

$sql_result2 = mysql_query($sql2,$connection) or die ("Couldnt Execute SQL query");

which are identical to $sql and $sql_result respectively.

So, my first while loop looks like this:

	while ($row = mysql_fetch_array($sql_result)) {
	$first_name = $row["first_name"];
	$last_name = $row["last_name"];
	$id = $row["id"];
	echo "
	<option value=\"$id\">$last_name, $first_name</option>
	";

}

and the second looks like this:

while ($row = mysql_fetch_array($sql_result)) {
$first_name = $row["first_name"];
$last_name = $row["last_name"];
$id = $row["id"];
echo"
<option value=\"$id\">$last_name, $first_name</>
";

While this does work, I get the feeling it isnt the "right" way to do it. Especially since in the future i'm going to need 6 or 7 identical lists... i'll be creating redundant variables all day long 🙂

Any other suggestions would be very much appreciated. Also, i'm very new to PHP so any code examples would be cool too 🙂

Thanks!

cordex

Right and wrong. Right command, wrong variable.

$row is an array (though, why he mysql_fetch_array()'s into it before he gets into his while loop I don't know). I didn't look at his program closely and I just saw where he first defined it.
Use:
mysql_data_seek ($sql_result, 0);
before each while ($row = mysql_fetch_array($sql_result)) {

Anon

Great! This worked. Thank you VERY much for you help.