Duplicate number of times an item is inserted to MySQL based on a multiplier

[jaxdevil]

I have a form that displays entries and allows you to write down the quantity. For example:

 

Dogs: 3

Cats: 2

Birds: 5

 

When I submit the form I have an update query that will update a database with those entries, but I want to insert one row for each item i.e. like the above example if dogs have three then I want to insert 'dogs' three times to the database, and cats twice, and birds five times, so the database would have 10 new rows, 5 saying dogs, 2 saying cats, and 3 saying birds

 

How do you go about doing this? The way I would like to do it is have a signle insert query for each entry but multiply it based on the number of entries. Somehow dynamically have the script see 'there are 3 entries so lets duplicate the query three times'.

 

Any ideas?

 

Thanks in advance,

SK

 

[Slip]

Easily done splitting the 'Dog' and '3' into their own variables and using loops to update the database but my question is why? Ar you trying to count something? Maybe there is a simpler way to achieve what you want...

[trecool999]

What's wrong with just using a row in the database of each and then updating the quantity?

[jaxdevil]

Thanks for the quick response.

 

There isn't a simple way to do it other than that. Its actually geometrically more complex than the dog cat thing, but I just broke it down into basics as I can build from that. There is a database that stores orders made, and one that stores inventory, when an item is received that was ordered it will display on the form and the user can select if all items arrived, or just some and change the quantity, in either case when they post the receipt of items it needs to update the inventory with the product data and it needs each item to be on its own row in the database so when it is bought or moved it can be marked that way, among other things, like serial numbers and the like, so they each need to submit to their own row, the only way I have so far is to make a query, and use an if statement 100x for each possible line item (max 20 on an order) so it will do the $result 1 time if ==1, 2 times if ==2, etc. But that ends up being a big bit of code. if you figure that for the if 1 makes 1 result row, and if 2 makes 2, by the time you get to 100 (which would have 100 result rows) its like 100+99+98+97+96+95, etc. And thats done 20 times (1 for each line  item) so its pretty cumbersome. It would better if it could say see that quantity is 8 so run the result 8 times (which would in effect run the submit query 8 times). I would post the code up here but there is too much notation to go with it as it is heavily using ajax, javascript, cgi, and php so even posting the one file would need to be explained by seeing the other 7 files linked to it.

 

I can easily expand the functionality if we could figure out how to change posted variables of this:

 

$1_row_item = "Dog";
$1_row_quantity = "3";
$2_row_item = "Cat";
$2_row_quantity = "2";
$3_row_item = "Bird";
$3_row_quantity = "1";

 

To an insert query of that works like the below but doesn't require all the repetitious entries:

$query="INSERT INTO inventory (`Item_Name`) VALUES ('$1_row_item')";
if ($1_quantity == '1') {
$result=mysql_query($query) or die ("MySQL Error: " . mysql_error());
}
if ($1_quantity == '2') {
$result=mysql_query($query) or die ("MySQL Error: " . mysql_error());
$result=mysql_query($query) or die ("MySQL Error: " . mysql_error());
}
if ($1_quantity == '3') {
$result=mysql_query($query) or die ("MySQL Error: " . mysql_error());
$result=mysql_query($query) or die ("MySQL Error: " . mysql_error());
$result=mysql_query($query) or die ("MySQL Error: " . mysql_error());
}
$query="INSERT INTO inventory (`Item_Name`) VALUES ('$2_row_item')";
if ($2_quantity == '1') {
$result=mysql_query($query) or die ("MySQL Error: " . mysql_error());
}
if ($2_quantity == '2') {
$result=mysql_query($query) or die ("MySQL Error: " . mysql_error());
$result=mysql_query($query) or die ("MySQL Error: " . mysql_error());
}
if ($2_quantity == '3') {
$result=mysql_query($query) or die ("MySQL Error: " . mysql_error());
$result=mysql_query($query) or die ("MySQL Error: " . mysql_error());
$result=mysql_query($query) or die ("MySQL Error: " . mysql_error());
}
$query="INSERT INTO inventory (`Item_Name`) VALUES ('$3_row_item')";
if ($3_quantity == '1') {
$result=mysql_query($query) or die ("MySQL Error: " . mysql_error());
}
if ($3_quantity == '2') {
$result=mysql_query($query) or die ("MySQL Error: " . mysql_error());
$result=mysql_query($query) or die ("MySQL Error: " . mysql_error());
}
if ($3_quantity == '3') {
$result=mysql_query($query) or die ("MySQL Error: " . mysql_error());
$result=mysql_query($query) or die ("MySQL Error: " . mysql_error());
$result=mysql_query($query) or die ("MySQL Error: " . mysql_error());
}

 

Any ideas?