I want to insert multiple records into a database from one single loop....

[George Botley]

... As the title suggests, I want to insert multiple records into a database using one group of text fields that can be replicated with javascript.

 

The HTML replication is sorted, the rest of the PHP is sorted, yet its the for each loops that are puzzling me...

 

Am i doing them right, do I even need loops? - if not can you suggest a better alternative.... anyway - heres the code.

 

HTML code:

								<label>Item: </label>
                                <input type="text" name="item[]" /> 
                                                                
                                <label>Qty: </label>
                                <input type="text" name="InvoiceQty" style="width: 20px; text-align: center;" /> 
                                
                                <label>Price: </label>
                                <input type="text" name="InvoicePrice" style="width: 50px;" value="£" /> 

 

 

Heres the PHP code to deal with this request:

<?

//Configuration 
include_once "../../../includes/functions/config.php";

//Setup Variables
$CustomerName = $_POST["CustomerName"];
$CustomerEmail = $_POST["CustomerEmail"];
$invoice_for = $_POST["invoice_for"];
$status = $_POST["status"];
$due = $_POST["due"];

$item = $_POST["item"];
$qty = $_POST["InvoiceQty"];
$price = $_POST["InvoicePrice"];
$plan = $_POST["plan"];
$relation = $_POST["relation"];
$additional_comments = $_POST["additional_comments"];

foreach($item as $item) {
$query = "INSERT INTO invoice_items (item, quantity, price) VALUES ('$item', '$qty', '$price')";
$query = mysql_query($query);
}

?>

 

For some reason, the PHP code just inserts the same data for the qty and price sections, which is understandable considering the foreach is only using the $item variable.... is there anyway I can pass on the $qty and $price variables within the same loop or similar to eventually insert the data as intended...

 

The reason for this is an invoice form, I am adding invoice items into one database table, and the invoice in another.... The items will be called by the invoice number however its inserting the invoice items into the database in the first place that I'm having trouble with, any ideas?

 

Your help is appreciated..

[George Botley]

bump

[Xtremer360]

Are you talking about:

 

foreach($item as $item) {
   $query = "INSERT INTO invoice_items (item, quantity, price) VALUES ('$item', '$qty', '$price')";
   $query = mysql_query($query);
$query2= "INSERT INTO table_name (row1, row2) VALUES ('$variable1', '$variable2')";
$query2=mysql_query($query2);
}

 

I hope this is what your wanting. I'm not a pro by any means but trying to get my degree in it and I figured I've gotten my fair share of help on these boards and thought I should give some.

[nafetski]

Hey there

 

The only time I really use an array in the $_POST superglobal is when I have a checkbox field...if you have multiple arrays, you will need to map each of those arrays in your insert statement.

 

For example

$list[0]='item1';
$list[1]='item2';

$qty[0]=1;
$qty[1]=2;

// You can't just do this

foreach($list as $lists){
$sql = "INSERT into tablename (`list`,`qty`) VALUES ('$lists',$qty)";
}

 

In a foreach you are redeclaring the array as a single variable...only for that array.  If you do some validation, and KNOW that all of your arrays will have the same number of entries, you would want to use a for loop instead.

 

Ex:

$list[0]='item1';
$list[1]='item2';

$qty[0]=1;
$qty[1]=2;

$arrCount = count($list);  // this will return the size of the array, if you know that all the arrays will have the same number of values, any of them can be used

for($i=0;$i<$arrCount;$i++){

// Here we use the temporary $i variable to reference our array keys

$sql = "INSERT into tablename (`list`,`qty`) VALUES ('".$list[$i]."',$qty[$i])";


}

 

Hopefully that helps!

[sasa]

change form to

								<label>Item: </label>
                                <input type="text" name="item[]" /> 
                                                                
                                <label>Qty: </label>
                                <input type="text" name="InvoiceQty[]" style="width: 20px; text-align: center;" /> 
                                
                                <label>Price: </label>
                                <input type="text" name="InvoicePrice[]" style="width: 50px;" value="£" />  

and

foreach($item as $k => $item1) {
$query = "INSERT INTO invoice_items (item, quantity, price) VALUES ('$item1', '$qty[$k]', '$price[$k]')";
$query = mysql_query($query);

}

[George Botley]

Thanks to all of those who helped,

 

@CoolAsCarlito - that wasn't what I was looking for but thankyou.

 

@nafetski - Thank you, your solution worked, yet it was a little to complicated for me to learn from at this stage.

 

@sasa - Your solution was great, a simple adaptation; that I don't know how i missed... yet, one thing - you forget to add the [$k] to the $item variable

 

Again, thanks to all who helped.

 

MARKED AS SOLVED

 

 

[George Botley]

I tried to do that, with your solutions - it worked at first.... then I get this error on insertion into the Database.

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[0]')' at line 1

 

Why am i recieving this error...?

 

Help appreciated as soon as possible... thanks.

[George Botley]

My bad, forgot the ' on the insert.