Looping an Insert Statement

[Tangent12]

Hi all, I have been using these forums for about 6 months now and up to now I've been able to find an existing thread to help with any problems. This particular problem does not seem to have come up though so I'm hoping for some help.

Background.

I am building a small flash movie that allows the user to create invoices and write the details to the database. Multiple items can be added to the invoice and I need to populate an items table with the item details the user chooses, however many there is.

I have used arrays in Flash to group the data and the variables output like this:

itmSupName0
itmItemName0
itmPurchCost0
itmQuoteCost0
itmDescription0
itmQuantity0

itmSupName1
itmItemName1
itmPurchCost1
itmQuoteCost1
itmDescription1
itmQuantity1

and so on depending on how many items there is. I also have the total item count being brought in on a variable called "itmTotalCount".

I have tested these incoming variables by just enetering one item into an invoice and submitting so I only use the variables with the "0" suffix and used the following script:

<?php
/**
 * file : saveQuote.php
 * Save one client from the clients section.
 */
include_once("settings.inc.php");
include_once("functions.inc.php");

$query = "INSERT INTO items (suppName, title, purchCost, quoteCost, description, quantity) VALUES ('" . $_POST['itmSupName0'] ."',";
$query .= " '" . $_POST['itmItemName0'] ."', '" . $_POST['itmPurchCost0'] ."',";
$query .= " '" . $_POST['itmQuoteCost0'] ."', '" . $_POST['itmDescription0'] ."',";
$query .= " '" . $_POST['itmQuantity0'] ."')";
$result = @mysql_query($query);
if($result){
    echo "status=ok";
}else{
    fail("There was an error inserting the client.", mysql_error());
}
?>

The Problem

I need to be able to send multipe items to the items table as seperate rows. So if there is five items in the invoice, the script will loop 5 times and insert each item as it goes.

My first attempt is like this:

for($i = 0; $i < $itmC; $i++){
		$itemA = "itmSupName";
		$itemB = "itmItemName";
		$itemC = "itmDescription" ;
		$itemD = "itmPurchCost";
		$itemE = "itmQuoteCost";
		$q = "query";
		$r = "result";
		$itemTxtA = $itemA.$i;
		$itemTxtB = $itemB.$i;
		$itemTxtC = $itemC.$i;
		$itemTxtD = $itemD.$i;
		$itemTxtE = $itemE.$i;
		$query = $q.$i;
		$result = $r.$i;
        $query = "INSERT INTO items (suppName, title, description, purchCost, quoteCost) VALUES ('" . $_POST['$itemTxtA'] ."',";
		$query .= " '" . $_POST['$itemTxtB'] ."', '" . $_POST['$itemTxtC'] ."', '" . $_POST['$itemTxtD'] ."',";
		$query .= " '" . $_POST['$itemTxtE'] ."')";
		$result = @mysql_query($query);

	if($result){
		echo "status=ok";
	}else{
		fail("There was an error inserting the client.", mysql_error());
}

}

$itmC is the total count figure I have which sets the loop upper limit. You can see that I try to build the incoming variable name by using the static name and adding the value or i. This works because I have echoed them out in another script.

Is theere a fundamental problem with the way I am doing this? Is my syntax wrong? Am I declaring the variabes wrong? Is it that you just can't include the INSERT statement on a loop?

Any help would be much appreciated.

Thanks in advance

T12

[Azala]

Well you haven't actually said what went wrong.

but...

$itemA = array($_POST['itmSupName']); // assuming the post data has the right syntax for constructing an array in php
for (...)
{
query(INSERT (bla bla) VALUES ($itemA[$i]))
...
}

If you can't build the array directly with the post data you could build the array by exploding the post data on the used delimiter.

$itemA = explode("*",$_POST['itmSupName']); // swap out the * with whatever you've used
for (...)
{
query(INSERT (bla bla) VALUES ($itemA[$i]))
...
}

Also, instead of submitting the number of items so you know how many times to loop in the for loop I suggest you use the count function to avoid errors

$numberofitemsinarray = count($itemA);