Looping code for multple row form insert

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
gomac
Forum Newbie
Posts: 11
Joined: Mon Jan 12, 2004 2:55 pm
Location: Toronto

Looping code for multple row form insert

Post by gomac »

This site has been very helpful to me learning this stuff - I'll try and find a way to give back.... :P

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
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post 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
}
User avatar
gomac
Forum Newbie
Posts: 11
Joined: Mon Jan 12, 2004 2:55 pm
Location: Toronto

I'm a little slow on this stuff

Post 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) ) &#123;
$security_id = $rows&#1111;'security_id'];
$name =  $rows&#1111;'name'];
//DISPLAY FORM LOOP
echo ("
<tr>
<td>
<input type='Hidden' name='date_id&#1111;$key]' value='$date_id'>
<input type='Hidden' name='owner&#1111;$key]' value='1'>
<input type='Hidden' name='snapshot_id&#1111;$key]' value='null'>
<input type='Hidden' name='security_id&#1111;$key]' value='$security_id'>$name
</td>
<td>
<input type='Text' name='unit_value&#1111;$key]'></td>
<td>
<input type='Text' name='no_units&#1111;$key]'>
</td></tr>
");
&#125;
Line fed for legibility...
Right? Now how do I pass and intialize this array on the insert page?

Sorry if I'm clueless.
g
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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?
User avatar
gomac
Forum Newbie
Posts: 11
Joined: Mon Jan 12, 2004 2:55 pm
Location: Toronto

Post 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
User avatar
gomac
Forum Newbie
Posts: 11
Joined: Mon Jan 12, 2004 2:55 pm
Location: Toronto

SOLVED

Post 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 ) &#123;
++$a;
$sqlquery = "INSERT INTO table (snapshot_id , date_id , security_id , no_units , unit_value , owner) "
        . " VALUES ( '$snapshot_id&#1111;$a]', '$date_id&#1111;$a]', '$security_id&#1111;$a]', '$no_units&#1111;$a]', '$unit_value&#1111;$a]', '$owner&#1111;$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
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post by xisle »

nice work! I use the database id as a key generally ... how long did it take to figure out?? ;-)
User avatar
gomac
Forum Newbie
Posts: 11
Joined: Mon Jan 12, 2004 2:55 pm
Location: Toronto

Post 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.... 8)

Thanks, you were helpful....
gord
Post Reply