Page 1 of 1

Multiple inserts, drop down menu not showing for each row

Posted: Thu Mar 11, 2010 2:06 pm
by Eiolon
I am working on a script to insert multiple rows into a table. The the front end, I have a drop down menu called "CODE". It shows for the first row but not for the other rows:

Screenshot: http://www.intravising.com/code.jpg

How do I make it so the CODE is displayed for each row?

Thanks!

Here is my PHP/HTML:

Code: Select all

 
$query_codes = "SELECT id, code FROM codes ORDER BY code ASC";
$codes = mysql_query($query_codes) OR die ('Cannot retrieve a list of codes.');
 
<table border="0" cellpadding="2" cellspacing="2" width="100%">
  <tr>
    <th width="50">CODE</th>
    <th width="100">CALL NUMBER</th>
    <th>DESCRIPTION</th>
    <th width="75">ACQUIRED</th>
  </tr>
  <tr>
    <td width="50"><div align="center"><select name="code_id[]"><?php while($row_codes = mysql_fetch_array($codes)) { print '<option value="' . $row_codes['id'] . '">' . $row_codes['code'] . '</option>';}?></select></div></td>
    <td width="100"><div align="center"><input name="call_number[]" type="text" style="width:100px" maxlength="50" /></div></td>
    <td><div align="center"><input name="description[]" type="text" style="width:650px" /></div></td>
    <td width="75"><div align="center"><input name="acquired[]" type="text" style="width:100px" maxlength="10" /></div></td>
</tr>
  <tr>
    <td width="50"><div align="center"><select name="code_id[]"><?php while($row_codes = mysql_fetch_array($codes)) { print '<option value="' . $row_codes['id'] . '">' . $row_codes['code'] . '</option>';}?></select></div></td>
    <td width="100"><div align="center"><input name="call_number[]" type="text" style="width:100px" maxlength="50" /></div></td>
    <td><div align="center"><input name="description[]" type="text" style="width:650px" /></div></td>
    <td width="75"><div align="center"><input name="acquired[]" type="text" style="width:100px" maxlength="10" /></div></td>
</tr>
  <tr>
    <td width="50"><div align="center"><select name="code_id[]"><?php while($row_codes = mysql_fetch_array($codes)) { print '<option value="' . $row_codes['id'] . '">' . $row_codes['code'] . '</option>';}?></select></div></td>
    <td width="100"><div align="center"><input name="call_number[]" type="text" style="width:100px" maxlength="50" /></div></td>
    <td><div align="center"><input name="description[]" type="text" style="width:650px" /></div></td>
    <td width="75"><div align="center"><input name="acquired[]" type="text" style="width:100px" maxlength="10" /></div></td>
</tr>
  <tr>
    <td width="50"><div align="center"><select name="code_id[]"><?php while($row_codes = mysql_fetch_array($codes)) { print '<option value="' . $row_codes['id'] . '">' . $row_codes['code'] . '</option>';}?></select></div></td>
    <td width="100"><div align="center"><input name="call_number[]" type="text" style="width:100px" maxlength="50" /></div></td>
    <td><div align="center"><input name="description[]" type="text" style="width:650px" /></div></td>
    <td width="75"><div align="center"><input name="acquired[]" type="text" style="width:100px" maxlength="10" /></div></td>
</tr>
  <tr>
    <td width="50"><div align="center"><select name="code_id[]"><?php while($row_codes = mysql_fetch_array($codes)) { print '<option value="' . $row_codes['id'] . '">' . $row_codes['code'] . '</option>';}?></select></div></td>
    <td width="100"><div align="center"><input name="call_number[]" type="text" style="width:100px" maxlength="50" /></div></td>
    <td><div align="center"><input name="description[]" type="text" style="width:650px" /></div></td>
    <td width="75"><div align="center"><input name="acquired[]" type="text" style="width:100px" maxlength="10" /></div></td>
</tr>
</table> 
 

Re: Multiple inserts, drop down menu not showing for each row

Posted: Thu Mar 11, 2010 2:34 pm
by AbraCadaver
You can only fetch from the result set once, so do it at the beginning and assign the rows to an array. Then when you need them in the select just loop over the array.

Code: Select all

<?php
$query_codes = "SELECT id, code FROM codes ORDER BY code ASC";
$codes = mysql_query($query_codes) OR die ('Cannot retrieve a list of codes.');
while($row[] = mysql_fetch_array($codes)) {}
?>
// some html
<select name="code_id[]">
<?php
foreach($row as $row_codes) {
    print '<option value="' . $row_codes['id'] . '">' . $row_codes['code'] . '</option>';
}
?>
</select>
// more html
You might also want to build a function for this if you do it often.

Re: Multiple inserts, drop down menu not showing for each row

Posted: Thu Mar 11, 2010 2:50 pm
by Eiolon
Thanks very much!

It works but it does one thing. It adds a blank option in the drop down menu.

http://www.intravising.com/code2.jpg

Any idea on how to get rid of that?

Thanks again!

Re: Multiple inserts, drop down menu not showing for each row

Posted: Thu Mar 11, 2010 3:01 pm
by AbraCadaver
Yeah sorry... Wasn't thinking. Here is the fix:

Code: Select all

<?php
$query_codes = "SELECT id, code FROM codes ORDER BY code ASC";
$codes = mysql_query($query_codes) OR die ('Cannot retrieve a list of codes.');
while($row = mysql_fetch_array($codes)) {
   //put array inside loop
   $rows[] = $row;
}
?>
// some html
<select name="code_id[]">
<?php
//change $row to $rows
foreach($rows as $row_codes) {
    print '<option value="' . $row_codes['id'] . '">' . $row_codes['code'] . '</option>';
}
?>
</select>
// more html
Also, I was wrong, you CAN reuse the result set using mysql_data_seek(), so, your original would be:

Code: Select all

<select name="code_id[]">
<?php
mysql_data_seek($codes, 0);
 
while($row_codes = mysql_fetch_array($codes)) {
   print '<option value="' . $row_codes['id'] . '">' . $row_codes['code'] . '</option>';
}
?>
</select>

Re: Multiple inserts, drop down menu not showing for each row

Posted: Thu Mar 11, 2010 3:13 pm
by Eiolon
Perfect, thanks!