Query is updating my DB using only the last row of data in a dynamically created form?

Experts,

I have a dynamically created form which contains a table.  The rows of this table contain information pulled from the database.  Each row of the table has been given a unique id using the 'ID' filed contained within the DB.  Additionally, each row of the table contains two dropdown menus which I would like my users to be able to update.

The problem is that when processing this form via a foreach loop, the database is only updated using the very last row of data and whatever has been selected using this row's dropdown menus.

Therefore, changes that I may make to row 5 are overwritten by the changes in the last row, row 7 upon form processing.

Here is my code which identifies via a foreach loop, the ID of each row, and attempts to process the dropdown data from each row.

Any suggestions on how I can straighten this out?  Cheers!

This is an abbreviate version of my form:

echo'<td>';
echo'<select name="expected" id="expected" style="font-size:10px">';
echo'<option value="'.$result3['expected'].'">'.$result3['expected'].'</option>';
echo'<option value="Yet to Discuss">Yet to Discuss</option>';
echo'<option value="Undecided">Undecided</option>';
echo'<option value="Plans to Work">Plans to Work</option>';
echo'<option value="Will Not be Working">Will Not be Working</option>';
echo'</select>';
echo'</td>';
echo'<td>';
echo'<select name="coding" id="coding" style="font-size:10px">';
echo'<option value="'.$result3['coding'].'">'.$result3['coding'].'</option>';
echo'<option value="LOA">LOA</option>';
echo'<option value="Sick">Sick</option>';
echo'<option value="Sick - CESLA">Sick - CESLA</option>';
echo'<option value="Sick - FMLA">Sick - FMLA</option>';
echo'<option value="No Call No Show">No Call No Show</option>';
echo'<option value="Worked">Worked</option>';
echo'</select>';
echo'</td>';
echo'<td><textarea name="notes" id="notes" cols="15" rows="1" style="font-size:10px;">'.$result3['notes'].'</textarea></td>';
echo'<input name="row_number[]" type="hidden" value="'.$result3['ID'].'" />';


This is the portion of my form processing script which should save the data to each row of the database based on the unique ID for each row of the table:

//THE NAME OF DROPDOWN 1
$expected = $_POST['expected'];

//THE NAME OF DROPDOWN 2
$coding = $_POST['coding'];

//THE NAME OF TEXTFIELD 1
$notes = $_POST['notes'];

//THE UNIQUE ID OF EACH TABLE ROW
$row_number = $_POST[row_number];

foreach($_POST[row_number] as $row_number){
      $update_row      = "UPDATE schedule SET expected='$expected', coding='$coding', notes='$notes' WHERE ID='$row_number'";
      $update_row_query = mysql_query($update_row) or die ("Could not run query: ".$update_row."<br />\n".mysql_error());
      echo $update_row;
}
evibesmusicAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
PranjalShahConnect With a Mentor Commented:
First suggestion I would make looking at your code is try to write the html tags only in html rather than using php echo. It will be difficult for you to debug.

I believe your dropdown code is under the while loop of your query. Here, you need to have a unique name for each of the drop downs for each of the row. So what you can do is change your dropdown names to,

<select name="expected<? echo $result3['ID'];?>" id="expected<? echo $result3['ID'];?>" style="font-size:10px">

And now in your foreach loop change your query to,

$update_row = "UPDATE schedule SET expected='".$_post['expected'.$row_number]."' WHERE ID='".$row_number."'"

Do the same thing for other dropdowns
0
 
jastacdossCommented:
In short your foreach is only looping once because $_POST[row_number] is not an array. You also need to identify each field using a unique id...
$row_count = 0;

// LOOP THROUGH RESULTS AND PRINT THE FORM
echo '<td><textarea name=" . $row_count . '|notes'" cols="15"></td>';
echo '<input name="' . $row_count  . '|row_number" type="hidden" value="' . $result3['ID'] . '">';

// COUNT NUMBER OF ROWS FOR THE LOOP
$row_count++; 

// OUTSIDE OF YOUR LOOP ADD THIS INPUT
echo '<input name="row_count" type="hidden" value="' . $row_count . '">';

Open in new window


then your for loop
for ($i = 0; $i < $_POST['row_count']; $i++) {

// SET YOUR VARIABLES
$expected = $i . "|expected";
$id = $i . "|row_number";

// ABBREVIATED NEW UPDATE STATEMENT
$update_row  = "UPDATE schedule SET expected='$_POST[$expected]' WHERE ID='$_POST[$id]'";
}

Open in new window

0
 
evibesmusicAuthor Commented:
@PranjalShah:

If the following: $update_row = "UPDATE schedule SET expected='".$_post['expected'.$row_number]."' WHERE ID='".$row_number."'"

Supposed to be: $update_row = "UPDATE schedule SET expected='".$_POST['expected'.$row_number]."' WHERE ID='".$row_number."'"
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
PranjalShahCommented:
yes, the $_POST should be in uppercase. And try echoing your queries before executing it so that you get the idea of what is being posted.
0
 
evibesmusicAuthor Commented:
@PranjalShah:

Thank you very much.  Your solution worked.  Here come the points.
0
 
evibesmusicAuthor Commented:
Thanks!
0
All Courses

From novice to tech pro — start learning today.