How to insert array data to mysql table

Question

I have two tables called op_group and options .These two tables are filled by array values. For example, I want to insert pizza details into my tables.

It look like this:

1. Cheese pizza and Sausage pizza will get inserted into the op_group table.
2. Other data will get inserted into the options table. These options should be inserted based on the relevant foreign key op_group id.

This is what i have tried. All details are inserted but not for the relevant op_group.

$opg_name=$_POST['opg_name'];
$price=$_POST['price'];
        
$itemCountz = count($opg_name);
$itemValues=0;
$queryValue1 = "";
                
for($i=0; $i<$itemCountz; $i++) {
  $itemValues++;
  if($queryValue1!="") {
    $queryValue1 .= ",";
  }
  $queryValue1 = "INSERT INTO op_group  
                  (opg_id,ml_id,cat_id,res_id,res_name,op_grp)                 
                 VALUES(NULL,'".$ml_id."','".$cat_id."','".$res_id."','".$res_name."','".$_POST["opg_name"][$i]."')";
    
  $result1= mysql_query($queryValue1)or die(mysql_error());                                 
  
  $query6= "SELECT  * FROM op_group ORDER BY opg_id DESC LIMIT 1" ;
  $result6= mysql_query($query6);
        
  while($row6 = mysql_fetch_assoc($result6)){
    $opg_id=$row6['opg_id'];
  }
                                                            
  $itemCount2 = count($price);
  $itemValues1 = 0;
                
  $queryValue2 = "";
  for($j=0;$j<$itemCount2;$j++) {
    if(!empty($_POST["op_name"][$j])||!empty($_POST["price"][$j])) {
      $itemValues1++;
      if($queryValue2!="") {
        $queryValue2 .= ",";
      }
                        
      $queryValue2 = "INSERT INTO options (op_id,opg_id,ml_id,cat_id,res_id,res_name,opt,price) VALUES (NULL,'".$opg_id."','".$ml_id."','".$cat_id."','".$res_id."','".$res_name."','".$_POST["op_name"][$j]."','".$_POST["price"][$j]."')";
    }           
  }
  
  $result2=mysql_query($queryValue2)or die(mysql_error());
}

This code give result like this

options table inserted data looks like this

how to solve this?

Answer 1

The reason why you are seeing options being duplicated for each option group is because your $queryValue2 is being repeatedly executed for each option group inside your outer most for($i = 0; $i < $itemCountz; $i++) loop.

As it stands, your current way of organizing the $_POST inputs are quite messy (and so are your duplicated row attributes). I will suggest you group your HTML inputs fields by each option group. You can try something like this:

<div>
  <input type="text" id="first_order" name="orders[0][name]" /><br />
  <input type="text" name="orders[0][options][0][price]" /><br />
  <input type="text" name="orders[0][options][0][size]" /><br />   
  <input type="text" name="orders[0][options][1][price]" /><br />
  <input type="text" name="orders[0][options][1][size]" /><br /> 
</div>

<div>
  <input type="text" id="second_order" name="orders[1][name]" /><br />
  <input type="text" name="orders[1][options][0][price]" /><br />
  <input type="text" name="orders[1][options][0][size]" /><br />   
  <input type="text" name="orders[1][options][1][price]" /><br />
  <input type="text" name="orders[1][options][1][size]" /><br /> 
</div>

Notice how I use the HTML name attribute to group the food options by the food. Then if you submit this form, you will see that the $_POST array appears to look something like this:

Array
(
    [orders] => Array
    (
        [0] => Array
        (
            [name] => "Cheese Pizza"
            [options] => [0] => Array([size] => "8'" 
                                      [price] => "12")
                         [1] => Array([size] => "12'" 
                                      [price] => "14")
        )

        [1] => Array
        (
            [name] => "Sausage Pizza"
            [options] => [0] => Array([size] => "8'" 
                                      [price] => "13")
                         [1] => Array([size] => "12'" 
                                      [price] => "16")
        )
    )
)

Now you can tidy up your backend PHP codes with something like this:

foreach ($_POST['orders'] as $orders) {

  // .. some codes

  foreach($orders as $order) {
    // .. add codes to insert into op_groups where $order['name'] will give you the pizza name

    foreach($order['options'] as $details) {
      // .. add codes to insert into options where $details['size'] and $details['price'] will give you the corresponding values
    }
  }
}

Also, you can use mysqli.insert-id to get the row ID of the last inserted row. Then you can get rid of that silly $query6 and its while loop.

Of course, don't forget to sanitize your $_POST inputs before using them!

I am getting a bit tired, if there are mistakes, I will fix them up tomorrow. Hope it helps!