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:

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

Menu Details

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

Result

options table inserted data looks like this

how to solve this?

you can use PHP serialize() an array to store array values to database and upon retrieve unserialize it. OR do with json_encode() and json_decode() to store in database and retrieve as well. — Shelim
– Shelim, Commented Apr 27, 2015 at 6:02
$query6= "SELECT * FROM op_group ORDER BY opg_id DESC LIMIT 1" ; This is a problem what you do is you select back all the entries in op_group and then for each op_group you are inserting all the options without checking if it belongs to cheese pizza or Sausage pizza — Achal Saraiya
– Achal Saraiya, Commented Apr 27, 2015 at 6:05
@AchalSaraiya it select only inserted opg_id.( $opg_id=$row6['opg_id'];).theese example it echo 221 and 222.how do i check options for relevant option group? — Tje123
– Tje123, Commented Apr 27, 2015 at 6:19
You can get rid of all those ." - yuk. And don't store the data in the way you suggest. Just keep everything nice and normalised. — Strawberry
– Strawberry, Commented Apr 27, 2015 at 6:21

ivan.sim · Accepted Answer · 2015-04-27 19:08:46Z

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!

Collectives™ on Stack Overflow

How to insert array data to mysql table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related