Page 1 of 1
Looping code for multple row form insert
Posted: Fri Jan 16, 2004 2:55 pm
by gomac
This site has been very helpful to me learning this stuff - I'll try and find a way to give back....
In the meantime, I have a question:
I need to update multple rows in a MySQL database from a single form submit. Each row has 6 fields. The form is dumping the fields to a validation/insertion script. I am getting the variables there with:
Code: Select all
$snapshot_id = $_POSTї'snapshot_id'];
$date_id = $_POSTї'date_id'];
$security_id = $_POSTї'security_id'];
$no_units = $_POSTї'no_units'];
$unit_value = $_POSTї'unit_value'];
$owner = $_POSTї'owner'];
How do I constuct insertion code that takes all the POST values and inserts one row after another? So far I am only able to insert one record (the first). There will be a varying number of rows in this insert.
Also, could/should I be doing something on the form page to better order the POST (se below) am I delimiting the rows wrongly?
It appears that only the first row is being passed if I echo my variables on the submit page.
The form page is submitting the values thusly - in multiple rows where $security_id delimits the row. Each row is:
Code: Select all
<input type='Hidden' name='date_id' value='$date_id'>
<input type='Hidden' name='owner' value='1'>
<input type='Hidden' name='snapshot_id' value='null'>
<input type='Hidden' name='security_id' value='$security_id'>
<input type='Text' name='unit_value'>
<input type='Text' name='no_units'>
Any ideas?
Thanks,
Gord
Posted: Fri Jan 16, 2004 3:14 pm
by xisle
sorry for the unformated quick reply
// pass arrays through the inputs with
<input type='whatever' name='snapshot_ids[$key]'>
// loop through keys on the insert script
and update each
foreach($keysarray as $key){
// insert each variable as $_POST['snapshot_ids[$key]']
// $_POST['security_ids[$key]'], etc
}
I'm a little slow on this stuff
Posted: Fri Jan 16, 2004 4:36 pm
by gomac
Thanks Xisle, you're a trooper
That makes sense, just not sure on a few issues - I need a little more hand holding on the code actually. The rows are produced on the input page from a SELECT loop. If I read you right I should assign a key to each one, the same in each row? Not sure if this has to increment on the input page becuase of the WHILE:
So here's what I've got:
Code: Select all
While( $rows = MySQL_fetch_array($result) ) {
$security_id = $rowsї'security_id'];
$name = $rowsї'name'];
//DISPLAY FORM LOOP
echo ("
<tr>
<td>
<input type='Hidden' name='date_idї$key]' value='$date_id'>
<input type='Hidden' name='ownerї$key]' value='1'>
<input type='Hidden' name='snapshot_idї$key]' value='null'>
<input type='Hidden' name='security_idї$key]' value='$security_id'>$name
</td>
<td>
<input type='Text' name='unit_valueї$key]'></td>
<td>
<input type='Text' name='no_unitsї$key]'>
</td></tr>
");
}
Line fed for legibility...
Right? Now how do I pass and intialize this array on the insert page?
Sorry if I'm clueless.
g
Posted: Sat Jan 17, 2004 12:40 am
by infolock
How do I constuct insertion code that takes all the POST values and inserts one row after another? So far I am only able to insert one record (the first). There will be a varying number of rows in this insert.
This struck me as kinda odd.. If you are inserting data from a form post, shouldn't there only be one row that's updated? Could you clarify on why you need more than one row to be sent to the database? As in, are you wanting to populate related fields with the exact same post data?
Posted: Sat Jan 17, 2004 9:52 am
by gomac
Yes Infolock, I'm trying to update related fields with one post data. It is a stock portfolio application and the form has:
Stock 1
Number of Units
Unit Value
(hidden fields are date_id, owner, security_id, snapshot_id)
Each "Stock"will be updated with unique units & unit value, but will have the same owner, and date_id. Snapshot_id is the table primary and it increments.
I incremented $key in the form so now I have stock[1], units[1] etc stock[2], units[2] - just not sure how to address them in the insert script. i.e. how to call the array and loop it for insertion.
gord
SOLVED
Posted: Sun Jan 18, 2004 8:28 pm
by gomac
I finally futzed my way through this - if anyone is interested here's what I did. Not sure if this is the most efficient way (probably not), but it works.
I iterated (++$var) each form input name with [$var] so that each "row" of the form table had the same key number - i.e item[1], price[1], item [2] price[2] ...etc
So now I had six arrays that we're each delimited identically in sequence.
I grab each of the six in the submit page with a $_POST ['item'], $_POST ['price'] ...etc and assigned those to new variable arrays. I count one to get a number of "rows" to be inserted.
Then:
Code: Select all
While( $a < $numElements ) {
++$a;
$sqlquery = "INSERT INTO table (snapshot_id , date_id , security_id , no_units , unit_value , owner) "
. " VALUES ( '$snapshot_idї$a]', '$date_idї$a]', '$security_idї$a]', '$no_unitsї$a]', '$unit_valueї$a]', '$ownerї$a]' )";
I further increment the while loop the same number of times as I have "rows" and I end up with the right number of insert clauses in the loop, and each array is called properly with its rowset.
Thanks for the help gang.
gord
Posted: Mon Jan 19, 2004 3:42 pm
by xisle
nice work! I use the database id as a key generally ... how long did it take to figure out??

Posted: Mon Jan 19, 2004 6:34 pm
by gomac
Hey,
As I'm just learning both PHP and MySQL it took me a few hours. Also my programming exp. is limited to some javascript and a bit or Perl years ago. I'm more of a page designer than coder.
Since each of these "rows" is a set of like values - there was probably a way to use the increment_id key value in the table, but I couldn't figure out how. If I do item[$key] than I end up with a mess of 6 fields x 10+ rows in one array., and damned if I could figure out what to do with that. Since the columns line up in the rows I needed, I just twigged to incrementing the variable.
Probably poor database design got me in this mess....ah well it's just a little private app...now everything is working peachy. On to my next project....I'll be back....
Thanks, you were helpful....
gord