Insert Multiple Rows On One Page

[BTW8892]

So i have a system, and i was creating a page to input player stats. However there are 5 rows that have be inserted per game from a single form. I have the code for the form and the insert code. I was just wondering if someone could help me so it will insert all 5 rows in the form into a desired table when entered through the form. Thanks

Form: http://pastebin.com/C3pv2uAj
Insert Code: http://pastebin.com/NDZryVZc

[plantf7**344]

First, each set of Player form fields will must have unique names. You can't have "playerid" for five different form fields on the same form. So you will need something like "playerid_1", "playerid_2", etc. Each of the fields for each player must be named in the same fashion, having a suffix of "_X" at the end. This is how your script will identify the fields for each individual player.

Then, the simplest thing to do is a single insert for each set of fields – five inserts. You're done.

We have also written scripts that can identify an infinite number of such fields by evaluating the field names (the key) to find out how many [players] have been submitted, then looping through each one and doing an insert. This takes more effort to work out, but has advantages. In your case, if you only plan to insert five players' info at a time, you can either do five independent inserts or a simple loop to insert five.

[BTW8892]

First, each set of Player form fields will must have unique names. You can't have "playerid" for five different form fields on the same form. So you will need something like "playerid_1", "playerid_2", etc. Each of the fields for each player must be named in the same fashion, having a suffix of "_X" at the end. This is how your script will identify the fields for each individual player.

Then, the simplest thing to do is a single insert for each set of fields – five inserts. You're done.

We have also written scripts that can identify an infinite number of such fields by evaluating the field names (the key) to find out how many [players] have been submitted, then looping through each one and doing an insert. This takes more effort to work out, but has advantages. In your case, if you only plan to insert five players' info at a time, you can either do five independent inserts or a simple loop to insert five.

Now for the five inserts, would i need to insert the desired name like playerid_1 or would i just input playerid when writing the code to insert it.

[plantf7**344]

Your inserts would each look virtually identical to your existing insert, except that the values for each would reflect the appropriate form field names, ie "VALUES ('$_POST[playerid_1]','$_POST[game_id_1]',..." for the first player, and "VALUES ('$_POST[playerid_2]','$_POST[game_id_2]',..." for the second player, etc.

[BTW8892]

Thanks a ton! Very helpful, easy to understand and was very informative!

[shadiadiph]

Something along these lines should work

$num_queries = count($_POST['field1']);
for($i=0;$i<$num_queries;$i++) {
	$field1 = mysql_real_escape_string($_POST['field1'][$i]);
	$field2 = mysql_real_escape_string($_POST['field2'][$i]);
	$field3 = mysql_real_escape_string($_POST['field3'][$i]);
	$field4 = mysql_real_escape_string($_POST['field4'][$i]);
	$field5 = mysql_real_escape_string($_POST['field5'][$i]);
	$sql ="INSERT INTO `tblname` (`col1`, `col2` `col3`, `col4`, `col5`) values ('$field1', '$field2', '$field3', '$field4', '$field5')";
	$resultsql = mysql_query($sql);
}

//Your form fields on the form need to be something like this

<input type="text" name="field1[]" value="" />

//Or if you are not echoing your fields something like this

<input type="text" name="field1[0]" value="" />
<input type="text" name="field1[1]" value="" />

[BTW8892]

hey coppercup,

I did what you said, and i got the following error after.

[img]http://img811.imageshack.us/img811/3119/testf.png[/img]

[plantf7**344]

That's because playerid_1 is not a column in your table. (In this case, the error actually says what the problem is.) You need to insert $_POST (the value for the current player) into the column named playerid, so your query should look like this:

$playerstats1="INSERT INTO player_stats (playerid, game_id, gamertag, goals, assists, plus_minus, sog, ppg, shg, pim, hits) VALUES ('$_POST[playerid_1]','$_POST[game_id_1]','$_POST[gamertag_1]','$_POST[goals_1]','$_POST[assists_1]','$_POST[plus_minus_1]','$_POST[sog_1]','$_POST[ppg_1]','$_POST[shg_1]','$_POST[pim_1]','$_POST[hits_1]')";

Note that the column names (inside the first set of parenthesis) remain as they were before, because those are the names of the columns in your table. You are only electing to insert different sets of values into those columns (the variables referenced inside the second set of parenthesis).