[SOLVED] help please....

[almystersv]

Hi Guys,

 

Is there anyway I can loop through a number of drop down lists that read from the database using just one SQL statement?!

 

At the moment only the very first drop down menu is bringing any data back from the db and the others show nothing.

 

$query2 = "select * from employee";
$result2 = mysql_query($query2, $connection) or die ("Unable to perform query $query2");

 

<?php
while($row = mysql_fetch_array($result))
{
?>
  	<tr>
    	<td height="27"><?php echo $row['taskName']?></td>
    	<td><?php echo $row['taskDescription']?></td>
	<td><?php echo $row['weekday']?></td>
	<td>
	<form>
	<select name="empName">
	<option>Select an Employee</option>
	<?php while ($row2 = mysql_fetch_array($result2))
	{ ?><option><?php echo $row2['fName']?> <?php echo $row2['sName']?></option>
	<?php } ?> </select></form></td>
  	</tr>
<?php
}
?>

 

Thank you

[PHP Monkeh]

You're missing semi-colons at the end of your echo's in the second while loop.  Also it looks a tad messy, so you could try tidying it up:

 

<?php

while($row2 = mysql_fetch_array($result2)) {

echo "<option>".$row2['fName']." ".$row2['sName'];

}

?>

 

Ok maybe that still looks as messy xD Nevermind!

[almystersv]

Hi,

Thanks for taking a look.

 

I've added semi-colons onto the end of my echo's but it still only displays the data in the first drop down menu and the rest are empty.

 

<?php
while($row = mysql_fetch_array($result))
{
?>
  	<tr>
    	<td height="27"><?php echo $row['taskName']?></td>
    	<td><?php echo $row['taskDescription']?></td>
	<td><?php echo $row['weekday']?></td>
	<td>
	<form action="taskAdminQuery.php">
	<select name="empName">
	<option>Select an Employee</option>
	<?php while ($row2 = mysql_fetch_array($result2))
	{ ?><option value="<?php echo $row['empID']?>"><?php echo $row2['fName'];?> <?php echo $row2['sName'];?></option>
	<?php } ?> </select></td>
	<td><input name="taskID" value="<?php echo $row['taskID']?>" type="hidden" /></td>
  	</tr>
<?php
}
?>

[rhodesa]

Mysql uses a cursor, that advances every time you use a mysql_fetch_* function. So, after this code is run:

<?php while ($row2 = mysql_fetch_array($result2))

{ ?><option><?php echo $row2['fName']?> <?php echo $row2['sName']?></option>

<?php } ?>

for the first item in the parent loop, the cursor is at the end, and therefore will keep returning false thereafter. Try building the select menu first like so:

 

<?php
//Build Select Menu
$employee_list = '<select name="empName"><option>Select an Employee</option>';
while ($row2 = mysql_fetch_array($result2))
  $employee_list .= '<option>'.$row2['fName'].' '.$row2['sName'].'</option>';
$employee_list .= '</select>';

while($row = mysql_fetch_array($result)) {
?>
  	<tr>
    	<td height="27"><?php echo $row['taskName']; ?></td>
    	<td><?php echo $row['taskDescription']; ?></td>
	<td><?php echo $row['weekday']; ?></td>
	<td>
	<form><?php echo $employee_list; ?></form></td>
  	</tr>
<?php
}
?>