foreach inside a foreach

[searls03]

I don't know if this is possible, but what I am trying to do, is run a couple of queries for one form submission.  This form creates products and ordering options for the products and which cities they can be sold in.  1st, I run a foreach loop to insert the products into the table, lets say I selected three cities, so it would insert 3 times with appropriate city names.  this table would look like this: id, product, category, subcategory, price, orderable, city. so there would be three entries for the "test" product looking like this:

id  |    Product  |  Category  |    Subcategory  |  price  |  orderable  |  City

-----------------------------------------------------------------------------------------------

1    |  test1      |    test        |        none        |  10.00  |      no        |    Las Vegas

-----------------------------------------------------------------------------------------------

2    |  Test        |    test        |        none        |  10.00  |      yes        |    San Fran

-----------------------------------------------------------------------------------------------

3    |  Test        |    test        |        none        |  10.00  |      yes        |    Miami

-----------------------------------------------------------------------------------------------

4    |  Test        |    test        |        none        |  10.00  |      yes        |    New York

-----------------------------------------------------------------------------------------------

 

Then foreach time it inserts a city, it will insert into another table called labels that has the structure of: id, label, item_id, google

the table would look like this when completed

id  |    label  |  item_id  |    google 

---------------------------------------------

1    |  Test    |    2          |    Test

---------------------------------------------

2    |  Test    |    3          |    Test

---------------------------------------------

3    |  Test    |    4          |    Test

---------------------------------------------

The only reason I included the test1 product in the first table was to show that the id will not always match the item_id in the labels table.

 

Now there are multiple options for each product that can be used in the table options: id, item_id, option, label_id. 

id  |    item_id  |  option  |    label_id 

---------------------------------------------

1    |  2            |    Red      |    1

---------------------------------------------

2    |  2            |    Blue      |    1

---------------------------------------------

3    |  2            |    Green  |    1

---------------------------------------------

4    |  3            |    Red      |    2

---------------------------------------------

5    |  3            |    Blue      |    2

---------------------------------------------

6    |  3            |    Green  |    2

---------------------------------------------

7    |  4            |    Red      |    3

---------------------------------------------

8    |  4            |    Blue      |    3

---------------------------------------------

9    |  4            |    Green  |    3

---------------------------------------------

 

so what it is basically doing if foreach city, it will insert the product, so in this case, it was 4 cities so 4 times.  Then it inserted labels for all products that were marked as orderable, so there were 1 orderable in 3 cities, so it inserted it 3 times into labels.  then for each label and each product/city, they had 3 options, red, blue, green.  So foreach label, so id of 1, it inserted 3 options into my options table with ids 1,2,3.  it did this for each label then.  does this kinda make sense as to what I would like it to do?  I need some starts with the code for this.  this is what I have so far:

foreach ($_POST['city'] as $val) {
$city = $val;
if(isset($_POST['yes'])){
$orderable="yes";}else{$orderable="";}$product = $_POST['product'];
$product = str_replace("'","'",$product);
$product = str_replace('"',""",$product);
$price1 = $_POST['price'];
$price2 = $_POST['price2'];
$price = "".$price1.".".$price2."";
$category = $_POST['category1'];
$sub = $_POST['sub1'];
$sql = "INSERT INTO products (category, subcategory, product, price, orderable, city) 
VALUES('$category', '$sub', '$product', '$price', '$orderable', '$city' )";
$rs = mysql_query($sql) or die ("Problem with the query: $sql <br />" . mysql_error());
$id1 = $_POST['id1'];
$id12 = $_POST['id1'];

$label = $_POST['label12345'];

$sql = "INSERT INTO labels (`item_id`, `label`, `google`) VALUES ('$id12', '".$_POST['label12345']."', '".$_POST['label']."'  )";
$rs = mysql_query($sql) or die ("Problem with the query: {$sql} <br />" . mysql_error());
echo $id45;
$id45 = mysql_insert_id();

$users = $_POST['user'];
foreach ($users as $key => $val) {


$sql = "INSERT INTO options (`item_id`, `option`, label_id) VALUES ('{$id1}', '{$val}', '$id45' )";
$rs = mysql_query($sql) or die ("Problem with the query: {$sql} <br />" . mysql_error());
}


$sql = "INSERT INTO products (category, subcategory, product, price, orderable, city) 





VALUES('$category', '$sub', '$product', '$price', '$orderable', '$city' )";
$rs = mysql_query($sql) or die ("Problem with the query: $sql <br />" . mysql_error());




}
}

 

 

 

[xyph]

Please, indent your code properly.

 

I'd totally change the way you're storing this information... is this possible?

[searls03]

The thing is, I already have code set up to work with these tables, because I previously had manually entered things.  So changing the structure isn't really the best choice for this one.

[Jessica]

Better to take time now and fix it then forever have to deal with it.

[searls03]

can people not listen!  I just said I do not want to change my code!  There are like over 100 pages that rely on these tables.  I know what I want to accomplish is possible, I just can't quite figure out the loops! 

[Jessica]

No, what you said was "changing the structure isn't really the best choice for this one.". I counter that it probably is. If you have 100 pages of an application that would all have to be updated, you're doing it wrong.

[xyph]

Could you please clean up your code first though, by indenting it properly?

 

Why does the 'labels' table even exist? Why don't you just have a 'google' column in your product table?

 

The fact that you have 3 separate unique IDs for the same product is scary. This is terrible database design, and it's really worth starting over.

 

It's nice that you don't want to change your code, but we aren't here to help you finish your application faster. This is an educational forum, and we are making very valid points. If you don't want to learn how to do this right, why are you here?

[searls03]

I am trying to keep it so that there arent blank columns and such, just makes it easier to organize for me.  please help me with my code.

 

hopefully this is a little bit better:

if($_POST['submit'])

{
foreach ($_POST['city'] as $val) {
$city = $val;
if(isset($_POST['yes'])){
$orderable="yes";
}else{
$orderable="";
}
$product = $_POST['product'];
$product = str_replace("'","'",$product);
$product = str_replace('"',""",$product);
$price1 = $_POST['price'];
$price2 = $_POST['price2'];
$price = "".$price1.".".$price2."";
$category = $_POST['category1'];
$sub = $_POST['sub1'];
$sql = "INSERT INTO products (category, subcategory, product, price, orderable, city) 
VALUES('$category', '$sub', '$product', '$price', '$orderable', '$city' )";
$rs = mysql_query($sql) or die ("Problem with the query: $sql <br />" . mysql_error());
$id1 = $_POST['id1'];
$id12 = $_POST['id1'];
$label = $_POST['label12345'];
$sql = "INSERT INTO labels (`item_id`, `label`, `google`) 
VALUES ('$id12', '".$_POST['label12345']."', '".$_POST['label']."')";
$rs = mysql_query($sql) or die ("Problem with the query: {$sql} <br />" . mysql_error());
echo $id45;
$id45 = mysql_insert_id();
$users = $_POST['user'];
foreach ($users as $key => $val) {
$sql = "INSERT INTO options (`item_id`, `option`, label_id) VALUES ('{$id1}', '{$val}', '$id45' )";
$rs = mysql_query($sql) or die ("Problem with the query: {$sql} <br />" . mysql_error());
}
$sql = "INSERT INTO products (category, subcategory, product, price, orderable, city) 
VALUES('$category', '$sub', '$product', '$price', '$orderable', '$city' )";
$rs = mysql_query($sql) or die ("Problem with the query: $sql <br />" . mysql_error());
}
}

I never really do any indenting, I usually just search for what I want.  sorry about this.

[searls03]

it is also because items can have more than one label, by label, this is basically the type of option it is.  so one could have a color/size.

[searls03]

maybe this is more correct? i am not for sure:

if($_POST['submit'])
{
foreach ($_POST['city'] as $val) {
$city = $val;
if(isset($_POST['yes'])){
$orderable="yes";
}else{
$orderable="";
}
$product = $_POST['product'];
$product = str_replace("'","'",$product);
$product = str_replace('"',""",$product);
$price1 = $_POST['price'];
$price2 = $_POST['price2'];
$price = "".$price1.".".$price2."";
$category = $_POST['category1'];
$sub = $_POST['sub1'];
$sql = "INSERT INTO products (category, subcategory, product, price, orderable, city) 
VALUES('$category', '$sub', '$product', '$price', '$orderable', '$city' )";
$rs = mysql_query($sql) or die ("Problem with the query: $sql <br />" . mysql_error());
$id1 = $_POST['id1'];
$id12 = $_POST['id1'];
$label = $_POST['label12345'];
$sql = "INSERT INTO labels (`item_id`, `label`, `google`) VALUES ('$id12', '".$_POST['label12345']."', '".$_POST['label']."'  )";
$rs = mysql_query($sql) or die ("Problem with the query: {$sql} <br />" . mysql_error());
echo $id45;
$id45 = mysql_insert_id();
$users = $_POST['user'];
foreach ($users as $key => $val) {
$sql = "INSERT INTO options (`item_id`, `option`, label_id) VALUES ('{$id1}', '{$val}', '$id45' )";
$rs = mysql_query($sql) or die ("Problem with the query: {$sql} <br />" . mysql_error());
}
$sql = "INSERT INTO products (category, subcategory, product, price, orderable, city) 
VALUES('$category', '$sub', '$product', '$price', '$orderable', '$city' )";
$rs = mysql_query($sql) or die ("Problem with the query: $sql <br />" . mysql_error());
}
}

[Pikachu2000]

You should not be running those queries in loops at all.

[searls03]

well then how should I be running them?  I thought that when you want to insert multiple things, you got to run a loop?

[xyph]

Multiple rows in a single query

http://www.electrictoolbox.com/mysql-insert-multiple-records/

 

The way you have it set up makes it nearly impossible for you to do this without loops though. As it is, simply decoding your logic to provide you with a solution is time consuming. It looks like you repeat the first query again at the end of the outer foreach.

 

I'd be willing to help if you'd be willing to start again and do it right.

[searls03]

As long as I don't have to change the tables or structures, I am willing to start the code again.

[searls03]

ok, I do understand how to do multiple inserts, but I need it dynamically is the thing.

[xyph]

That's the fundamental issue though. Your data is stored in an extremely inflexible manner. You have multiple unique IDs for a single product... don't you see an issue with this? You don't reference a product by any sort of unique value... or at least an enforcible unique value.

 

You have no sure way to check if a product name already exists in your database, because at any given point you have multiple rows with the same product name by design.

 

ok, I do understand how to do multiple inserts, but I need it dynamically is the thing.

 

Variables contain dynamic data, so I don't see the issue. As I said, because each product has multiple unique IDs, I'm not sure if it's possible to do this without looping queries. You have to somehow generate and store each product-city's unique ID before you can generate the other queries.