How to insert array data into mysql table

Question

I have two tables named op_group and options . These two tables are populated with array values. For example, I want to insert pizza details into my tables.

It looks like this:

• Pizza with cheese and pizza sausages will be inserted into the op_group table.
• Other data will be inserted into the options table. These parameters should be inserted based on the corresponding op_group foreign key.

This is what I tried. All data is inserted, but not for the corresponding 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 gives a result similar to this

The table parameters inserted into the table are as follows:

how to solve it?

Answer 1

The reason you see duplicates for each parameter group is because your $queryValue2 is repeated for each parameter group inside your outer for($i = 0; $i < $itemCountz; $i++) loop for($i = 0; $i < $itemCountz; $i++) .

In its current form, your current way of organizing $_POST inputs is pretty dirty (and your duplicate string attributes). I suggest you group the HTML input fields for each parameter 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 food parameters. Then, if you submit this form, you will see that the $_POST array looks 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 clean your internal 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 } } } 

Alternatively, you can use mysqli.insert-id to get the row id of the last row inserted. Then you can get rid of this stupid $query6 and its while .

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

I get a little tired, if there are errors, I will fix them tomorrow. Hope this helps!