multiple row inserts into mysql

[artie2004]

Hi. I have a form that when posted will insert multiple rows of records into a mysql table. Here is part of the code of my form:

<form name="" method="POST" action="price_schedule.php">

<tr><td nowrap align=center><input class="input_text" type="text" name="medid" size="5" value=""></td><td nowrap align=center><input class="input_text" type="text" name="medname" size="30" value=""></td><td nowrap align=center><input class="input_text" type="text" name="qty" size="7" value=""></td><td nowrap align=center><input class="input_text" type="text" name="pilltype" size="10" value=""></td><td nowrap align=center>
<input class="input_text" type="text" name="priceperpill" size="11" value=""></td><td nowrap align=center><input class="input_text" type="text" name="price" size="11" value=""></td><td nowrap align=center><input class="input_text" type="text" name="instructions" size="30" value=""></td></tr>

<tr><td nowrap align=center><input class="input_text" type="text" name="medid" size="5" value=""></td><td nowrap align=center><input class="input_text" type="text" name="medname" size="30" value=""></td><td nowrap align=center><input class="input_text" type="text" name="qty" size="7" value=""></td><td nowrap align=center><input class="input_text" type="text" name="pilltype" size="10" value=""></td><td nowrap align=center>
<input class="input_text" type="text" name="priceperpill" size="11" value=""></td><td nowrap align=center><input class="input_text" type="text" name="price" size="11" value=""></td><td nowrap align=center><input class="input_text" type="text" name="instructions" size="30" value=""></td></tr>

........

<tr><td colspan=7><input class="input_button" type="submit" name="submit" value="submit</td></tr>
</form>

My question is how do i do a multiple insert? I know i have to use an array but i am not sure how to do this? Thanks.

[grandpa]

Hi artie2004.

In price_schedule.php, you'll need something like this to loop thru the POST array, and write to the db during each loop.

foreach ($_POST as $key => $val)
{
echo "<br>$key $val";
// I echoed the values here, you'll actually
// want to add your INSERTS here
}

You can see what's getting posted with the foreach loop above, or with a statement like this one:
print_r($_POST);

It is usually easier for me to see the results before proceeding. Sometimes when my database results aren't working as expected it's because I'm not posting what I think I should be posting. Make sense?

[artie2004]

Hey Grandpa.

I see what you mean. But how would i do it with multiple arrays. Would i have to do it like this?

foreach ($_POST['medid'] as $key => $val1)
{

foreach ($_POST['medname'] as $key => $val2)
{

foreach ($_POST['qty'] as $key => $val3)
{

.....

.....

}

}

}

[grandpa]

Just one foreach loop. Take a look at the array you have posted. Using your foreach loop:
foreach ($_POST as $key => $val)
{
echo "<br>$key $val";
}

medid [someid1]
medname [somename1]
qty [someqty1]
pilltype [sometype1]
priceperpill [astronomical1]
price [gasp1]
instructions[takeoften1]
medid [someid2]
medname [somename2]
qty [someqty2]
pilltype [sometype2]
priceperpill [astronomical2]
price [gasp2]
instructions[takeoften2]

The loop will process everything in the array. What you need, then, is not more foreach (not just yet, anyway) but a way to know when to do the INSERT. There are 7 elements in your array so the easiest method - I find - is to use a simple counter, $a, initialized to zero. There are bound to be more efficient methods, but here goes..

$a=0;
$sql = "INSERT INTO table (medid,medname,qty,pilltype,priceperpill,price,instruction)VALUES( ";
foreach ($_POST as $key => $val)
{
if ($a==0) $sql .= "'" . $val . "'";
if ($a==1) $sql .= "'" . $val . "'";
if ($a==2) $sql .= "'" . $val . "'";
if ($a==3) $sql .= "'" . $val . "'";
if ($a==4) $sql .="'" . $val . "'";
if ($a==5) $sql .= "'" . $val . "'";
if ($a==6)
{
$sql .= "'" . $val . ")\"";
$result = mysql_query($sql);
} // endIF a = 6
if ($a == 6)
{
$a = 0;
}
else
{
$a ++;
}
} // endFOREACH

As you loop through each item of the array, the counter $a will keep track of the array position. As $a is incremented, the sql statement gets built, one item at a time. On the final element, number 6 since we started counting at zero, the INSERT is performed and the counter is reset to zero.

This method assumes a lot - mostly that you are 100% certain of your data. I believe it's fundamentally sound, and easy to understand - and if you aren't processing large volumes of data it should work fine. In order to test this before you update data, just echo the $sql statement to verify it, and comment the mysql_query:
echo "<br>$sql";
//$result = mysql_query($sql);

[grandpa]

I knew there was something still wrong with this... you will need a comma after each of the items, as you are building the sql statement.

if ($a==n) $sql .= "'" . $val . "'";
becomes
if ($a==n) $sql .= " ' " . $val . " ', ";

except for the last item.

[dmmh]

my approach would be slightly different. not better per se, but different. I usually count the number of elements submitted first.

I assume at least one or more of those text fields have to be filled out, so not including form validation you could do something like this (assuming here 'medid' text field must be filled out):

$size_array = count($_POST['medid']);//count the nr of times this field occurs in the array, so we can use this to process with a loop
//now INSERT
for ($i=0; $i<$size_array; $i++){
$query = 'INSERT into table'.
" values ('', '".mysql_real_escape_string($_POST['medid'][$i])."', '".mysql_real_escape_string($_POST['medname'][$i])."', '".mysql_real_escape_string($_POST['qty'][$i])."', '".mysql_real_escape_string($_POST['pilltype'][$i])."', '".mysql_real_escape_string($_POST['priceperpill'][$i])."', '".mysql_real_escape_string($_POST['price'][$i])."', '".mysql_real_escape_string($_POST['instructions'][$i])."')";
$result = mysql_query($query) or die ("Error in query: $query");
}

[grandpa]

$size_array = count($_POST['medid']);//count the nr of times this field occurs in the array, so we can use this to process with a loop

I like that you take the number of times an associative array name is posted. My approach has been to provide unique names, so that medid would actually become medid1, medid2, etc. Even when coding a ten line display this becomes real cumbersome, real fast. Optionally, I'll provide an array with numeric index.
Filed under: Old dog learns new trick!

[dmmh]

lol :)
you never stop learning, each day brings a new trick :D

[artie2004]

I got it. Thanks you guys. Have a nice day. :)

[poobah]

I have a similar problem to Artie2004... I've been trying to work this out for the last 11 hours straight!

I have a form, that is generated from a separate table. It lists a set of values with a checkbox next to everyone of them and that has a unique value:

This is the code:

<form name="form1" method="post" action="clientdestsprocess.php">
<p>Client: <?php echo $_GET['clientco'];?></p>
<p>
<table>
<?php do {?>
<tr>
<td><input type="checkbox" name="destid" value="<?php echo $row_destinations['id'];?>">
</td>
<td><?php echo $row_destinations['town_name'];?></td>
</tr>
<?php } while ($row_destinations = mysql_fetch_assoc($destinations));?>
</table>
</p>
<p>
<input type="hidden" name="clientid" value="<?php echo $_GET['clientid'];?>">
<input type="submit" name="Submit" value="Submit">
</p>
</form>

And, this is the resultant html when the php is run:

<form name="form1" method="post" action="clientdestsprocess.php">
<p>Client: Pixelfury</p>
<p>
<table>
<tr>
<td><input type="checkbox" name="destid" value="3">
</td>
<td>Birmingham</td>
</tr>
<tr>
<td><input type="checkbox" name="destid" value="1">
</td>
<td>Brighton</td>
</tr>
<tr>
<td><input type="checkbox" name="destid" value="4">
</td>
<td>Edinburgh</td>
</tr>
<tr>
<td><input type="checkbox" name="destid" value="2">
</td>
<td>London</td>
</tr>
<tr>
<td><input type="checkbox" name="destid" value="5">
</td>
<td>Newcastle</td>
</tr>
</table>
</p>
<p>
<input type="hidden" name="clientid" value="4">
<input type="submit" name="Submit" value="Submit">
</p>
</form>

I've followed the code that grandpa posted and have a separate page (clientdestsprocess.php) that assembles a valid sql insert statement that is built from the foreach example - using the basic counter method.

Now, the problem is, the form will only post one set of results. It doesn't matter how many checkboxes are checked, only one set of results are sent to the form processing page.

How can I make the original form (as above) send the full set of results so that the 'foreach code' can do it's work and insert multiple records based on the number of boxes checked?

If anyone can help I'd be seriously grateful! - fingers x'd...

[wrightee]

<input type=checkbox name=destid[] value='1'>

etc

PHP needs to have the names set as array elements, not like Perl if my memory serves me right which will create arrays from multiple elements of the same name.

[poobah]

interesting... now i get this as the sql result:

INSERT INTO destslist (destid, clientid) VALUES ( ' Array ', ' Array ' )

----

still no multiple entries (despite three checkboxes being checked) though... and, this is the form code now:

<form name="form1" method="post" action="clientdestsprocess.php">
<?php do {?>
<?php echo $row_clientdests['town_name'];?><input name="destid[]" type="hidden" id="destid[]" value="<?php echo $row_clientdests['id'];?>">
<input name="checked[]" type="checkbox" id="checked[]" value="Y">
<br>
<?php } while ($row_clientdests = mysql_fetch_assoc($clientdests));?>
<input name="clientid[]" type="hidden" id="clientid[]" value="<?php echo $_GET['clientid'];?>">
<input type="submit" name="Submit" value="Submit">
</form>

----

my apologies if I'm being a dunce here - i've been at this for so long i can't see the obvious stuff i reckon... :( :( :(

help!?

[poobah]

whoops. just noticed the name and id fields had a '[]' - i've removed the id[] field but still no change... :(

[dmmh]

dood, you need to look into your HTML first before you want try to understand PHP. Dont wanne sound cocky, but the answer was given to you on the previous page

[poobah]

that helps, thanks...

[dmmh]

the only thing you need to the brackets behind, is the HTML elements name, so that the script knows it is an array of recurring elements with the same name.
so leave out any []'s everywhere else.
If you dont do that, you arent telling the page the element (in this case the element 'destid') may occur more then once, so it gets confused if it does indeed occur more then once

if you have only once:
<input name="destid[]" type="hidden" value="<?php echo $row_clientdests['id'];?>">, this is basically the same as <input name="destid[0]" type="hidden" value="<?php echo $row_clientdests['id'];?>">

if you have it 3 times you could write it like this:
<input name="destid[]" type="hidden" value="<?php echo $row_clientdests['id'];?>">
<input name="destid[]" type="hidden" value="<?php echo $row_clientdests['id'];?>">
<input name="destid[]" type="hidden" value="<?php echo $row_clientdests['id'];?>">

or you could write it like this (hardcode it, (DONT DO IT!))
<input name="destid[0]" type="hidden" value="<?php echo $row_clientdests['id'];?>">
<input name="destid[1]" type="hidden" value="<?php echo $row_clientdests['id'];?>">
<input name="destid[2]" type="hidden" value="<?php echo $row_clientdests['id'];?>">

its basically the same
so, if you will not know in advance how many times a checkbox, or another element for that matter, is going to occur in a posted script, you need to count the amount of times it occurs. PHP has an built-in function for this, count() (just look it up)

so, im adapting my script which handles this all on the previous page to your values and names
If you dont know how loops work, this wont make sense at all, but youre on your own from now on....

$size_array = count($_POST['destid']);//count the nr of times this field occurs in the array, so we can use this to process with a loop (im assuming destid and clientid will always occur the same amount of times here, sue me)
//now INSERT
for ($i=0; $i<$size_array; $i++){
$query = 'INSERT INTO destslist'.
" VALUES ('', '".mysql_real_escape_string($_POST['destid'][$i])."', '".mysql_real_escape_string($_POST['clientid'][$i])."')";
$result = mysql_query($query) or die ("Error in query: $query");
}

[dmmh]

k, back to coding my site :p

[poobah]

dude, thanks for that. I have to confess that I've only ever used basic forms for php inserts. I've never had to do multiple inserts and encounter all the arrays that go with it etc. your time on this is much appreciated...

as a result, I have it working - just. but in order to make the generated sql query work I've had to bodge it a little. I'm going to work on this more tomorrow and I'll post my solutions here etc...

thanks again... :)

[tnhwyman]

Greetings all,

I keep trying to learn as much as I can about php, as well as mysql. Most times over I fail to achieve the results I try to get, and settle ending up with form entries appended to a txt file. Here is Artie2004's form:

<tr><td nowrap align=center><input class="input_text" type="text" name="medid" size="5" value=""></td><td nowrap align=center><input class="input_text" type="text" name="medname" size="30" value=""></td><td nowrap align=center><input class="input_text" type="text" name="qty" size="7" value=""></td><td nowrap align=center><input class="input_text" type="text" name="pilltype" size="10" value=""></td><td nowrap align=center> 
<input class="input_text" type="text" name="priceperpill" size="11" value=""></td><td nowrap align=center><input class="input_text" type="text" name="price" size="11" value=""></td><td nowrap align=center><input class="input_text" type="text" name="instructions" size="30" value=""></td></tr>

<tr><td nowrap align=center><input class="input_text" type="text" name="medid" size="5" value=""></td><td nowrap align=center><input class="input_text" type="text" name="medname" size="30" value=""></td><td nowrap align=center><input class="input_text" type="text" name="qty" size="7" value=""></td><td nowrap align=center><input class="input_text" type="text" name="pilltype" size="10" value=""></td><td nowrap align=center> 
<input class="input_text" type="text" name="priceperpill" size="11" value=""></td><td nowrap align=center><input class="input_text" type="text" name="price" size="11" value=""></td><td nowrap align=center><input class="input_text" type="text" name="instructions" size="30" value=""></td></tr>

Each and every input field is the same in both sets. 2 medid , 2 medname , etc...etc. When I test the resulting response with Granpa's:

foreach ($_POST as $key => $val) 
{ 
echo "<br>$key $val"; 
}

I only get the last form set of entries. Now if I name the form fields without any duplicate field names, then I get all the form entries posting. What am I missing here? Before I go toward the next step of making the form's field data INSERT into the mysql db, I need to learn what it is that I am not understanding here. Lets say that I use different form field names like this: medid, medid1, medid2 and so on with each row in my form, will this also work as with Artie2004's form field name row sets? Thankyou, Artie2004, Grandpa, Dmmh, and Poobah, for helping this tenderfoot along.