Solved

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

Posted on 2011-09-19
6
339 Views
Last Modified: 2013-12-13
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;
}
0
Comment
Question by:evibesmusic
  • 3
  • 2
6 Comments
 
LVL 8

Accepted Solution

by:
PranjalShah earned 500 total points
ID: 36563511
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
 
LVL 1

Expert Comment

by:jastacdoss
ID: 36563520
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
 

Author Comment

by:evibesmusic
ID: 36563917
@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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 8

Expert Comment

by:PranjalShah
ID: 36563950
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
 

Author Comment

by:evibesmusic
ID: 36564275
@PranjalShah:

Thank you very much.  Your solution worked.  Here come the points.
0
 

Author Closing Comment

by:evibesmusic
ID: 36564276
Thanks!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now