Inserting multiple rows with foreach

PHP
1

I am trying to insert multiple rows using foreach, with no luck. I am able to insert the rows i want, however all the rows have the same value (that of the last row). This is the script i’m using:

foreach($_POST as $key=>$val) {

$contaminant = $_POST['contaminant'];
//$gac_value   = $_POST['gac_value'];
$ms_conc0 = $_POST['ms_conc0']; 
$ms_conc1 = $_POST['ms_conc1'];
$ms_conc2 = $_POST['ms_conc2'];
$ms_conc3 = $_POST['ms_conc3'];
$ms_conc4 = $_POST['ms_conc4']; 

$query = "INSERT INTO qgra (id, contaminant, gac_value, ms_conc0, ms_conc1, ms_conc2, ms_conc3, ms_conc4) 
            VALUES ('','$contaminant', '$gac_value', '$ms_conc0', '$ms_conc1', '$ms_conc2', '$ms_conc3', '$ms_conc4') "; 
                      
$result = mysql_query($query) or  die(mysql_error());  }

echo "<br><br> Worked?! ";

Tried using

foreach($_POST['submit'] as $key=>$val) { }

as well but was that wayyyy off.

And here is the form im using:

<form name="gac" id="gac" action="action.php" method="POST" > 

<table class="qra"> 
<tr>
<td class="row2" >&nbsp;</td>
<?php

$query = "SELECT * FROM sample ORDER BY id ASC LIMIT 5"; 
$result = mysql_query($query) or die(mysql_error());        
    
while ($row = mysql_fetch_array($result)) {
extract($row);  

echo "<td class=\\"row2\\" ><label> <span> $sample_name</span> </label></td> " ; 

} ?> 

</tr>

<tr class="row3">
        <td>

     <?php 
    $query3 = "SELECT contaminant, gac_value FROM gac_ea WHERE land_use = 'Allotment' ORDER BY id";
    $result3 = mysql_query($query3) or die(mysql_error());   
    echo "<select name=\\"contaminant\\" id=\\"contaminant\\" value=''></option>";
    echo "<option>Select </option>";
    while($row=mysql_fetch_array($result3)){     
        echo "<option value=$row[contaminant]> $row[contaminant] </option>"; } // End contaminant loop 
        echo "</select>"; ?>
    </td>

<td class="row3"><input type="text3" name="ms_conc0" id="ms_conc0" /></td>
<td class="row3"><input type="text3" name="ms_conc1" id="ms_conc1" /></td>
<td class="row3"><input type="text3" name="ms_conc2" id="ms_conc2" /></td>
<td class="row3"><input type="text3" name="ms_conc3" id="ms_conc3" /></td>
<td class="row3"><input type="text3" name="ms_conc4" id="ms_conc4" /></td>
</tr> 

<tr class="row3">
        <td>

     <?php 
    $query3 = "SELECT contaminant, gac_value FROM gac_ea WHERE land_use = 'Allotment' ORDER BY id";
    $result3 = mysql_query($query3) or die(mysql_error());   
    echo "<select name=\\"contaminant\\" id=\\"contaminant\\" value=''></option>";
    echo "<option>Select </option>";
    while($row=mysql_fetch_array($result3)){     
        echo "<option value=$row[contaminant]> $row[contaminant] </option>"; } // End contaminant loop 
        echo "</select>"; ?>
    </td>

<td class="row3"><input type="text3" name="ms_conc0" id="ms_conc0" /></td>
<td class="row3"><input type="text3" name="ms_conc1" id="ms_conc1" /></td>
<td class="row3"><input type="text3" name="ms_conc2" id="ms_conc2" /></td>
<td class="row3"><input type="text3" name="ms_conc3" id="ms_conc3" /></td>
<td class="row3"><input type="text3" name="ms_conc4" id="ms_conc4" /></td>
</tr>  

<tr class="row3">
        <td>

     <?php 
    $query3 = "SELECT contaminant, gac_value FROM gac_ea WHERE land_use = 'Allotment' ORDER BY id";
    $result3 = mysql_query($query3) or die(mysql_error());   
    echo "<select name=\\"contaminant\\" id=\\"contaminant\\" value=''></option>";
    echo "<option>Select </option>";
    while($row=mysql_fetch_array($result3)){      
        echo "<option value=$row[contaminant]> $row[contaminant] </option>"; } // End contaminant loop 
        echo "</select>"; ?>
    </td>

<td class="row3"><input type="text3" name="ms_conc0" id="ms_conc0" /></td>
<td class="row3"><input type="text3" name="ms_conc1" id="ms_conc1" /></td>
<td class="row3"><input type="text3" name="ms_conc2" id="ms_conc2" /></td>
<td class="row3"><input type="text3" name="ms_conc3" id="ms_conc3" /></td>
<td class="row3"><input type="text3" name="ms_conc4" id="ms_conc4" /></td>
</tr> 

<tr>
<td colspan="6" class="button" >
<input type="reset" value="Clear input"  />
<input type="submit" name="submit" value="Save" /></td>
</tr>
</table> 

</form>

Any hints of where im going wrong will be greatly appreciated. Thank you.

2

Okay… you got the right idea… not quite the execution…

When defining an array, you dont need the . The array() function/keyword does that for you. so $selValues = array(); works. (If you stick on the $selvalues, you actually create an array inside the next available element of the $selvalues array - a multidimensional array)
The while… you’ve got the interior backwards, kinda. You want to store the row you just pulled inside the selValues array.

[fphp]foreach[/fphp] do you mean that looks like swahili? or that you just dont know what i meant by foreach.

3

As far as the SELECT… pull the select, and loop through it once, storing the results into an array. Then loop through the array multiple times. It’ll save your database from being queried more often than it needs to…

(Hint: Use a WHILE for the results to store it in an array like $selvalues. Use a FOREACH on $selvalues to walk through the array whenever you need to.)

As far as the Insert, as Oddz said, there is the multiple insert structure. Construct the string by concatenating them together, and then sending the whole query to the database in one shot.

(and yes, i’m intentionally not giving the code. Learning is doing! :D)

4

I tried giving each field a unique name and creating columns for that but it didnt work.

Isn’t the foreach loop supposed to insert different rows (which is what i want) rather than different columns?

I am using the same as above right now, but it only inserts the last row :S. Help please!

5

Thank you. I thought as much. Then giving all the inputs different names, would need creating corresponding columns in the database table.

6

For each set of form values after you have done the query to get the data you aree using the same names for the inputs. Each input name should be unique

7

Thanks guys, you have no idea how useful this has been.

LOL @ StarLion: i love learning this way!

I think i got the saving the result in an array bit:

$query = "SELECT gac_value
FROM gac_ea, sample
WHERE land_use = '$land_use'
AND contaminant = $contaminant'
AND sample.site_id =1";

$result = mysql_query($query) or die(mysql_error());

$selValues [] = array();

while ($row = mysql_fetch_array($result)) {
// echo $row['gac_value']; 

$gac_value   = $selValues ['gac_value'];   
// echo count($result); 

 } //End while

Use a FOREACH on $selvalues to walk through the array whenever you need to.)

However the FOREACH bit is Swahili to me :frowning:

8

Thank you! Thank you! Thank you!

I’m not sure what is worse - not being able to do it or not being able to explain what i want properly :frowning:

9

INSERT INTO table (x,y,z) VALUES (‘’,‘’,‘’),(‘’,‘’,‘’),(‘’,‘’,‘’),(‘’,‘’,‘’),(‘’,‘’,‘’)

10

That worked great! Thank you, thank you, thank you!!!

. Well, except the gac_value for each contaminant is not inserted from the SELECT query above.

PS - all in one query? The SELECT and the INSERT??? How?

11

Rename your ms_concX fields as ms_concX. (so ms_conc0 , ms_conc1… etc), as well as on your contaminant field. (contaminant)
Then run your foreach over $_POST[‘contaminant’] AS $key => $value … and reference your other values by $_POST[‘fieldname’][$key] .
(PS: Try doing it all in 1 query!)

12

Hi thanks again,

No you said call the array when even i want to use its stored values, and to use FOREACH on the array?

foreach($_POST[‘contaminant’] AS $key => $value) {

$contaminant = $_POST[‘contaminant’][$key];
etc

$query
$result }

The only value i want is the gac_value from the array - i don’t get how i can call it inside the foreach loop …

13

Okay, I’m starting to see what you’re looking for.

Why not try…


echo "<select name=\\"contaminant\\" id=\\"contaminant\\" value=''></option>"; 
    echo "<option>Select </option>"; 
    while($row=mysql_fetch_array($result3)){      
        echo "<option value=$row[gac_value]> $row[contaminant] </option>"; }

Then the value of $_POST[‘contaminant’][$key] IS the gac_value of that array element, and you wouldnt need to look them up again when trying to insert.
Nothing in the HTML code requires the value to equal the display label.