how to match two rows of mysql same table in php

Question

I have a Table in which rows can be added if needed by add button. i have two tables in my database product_size and product_color. Product_color table has column that contains size id. I am trying to do that if two or more rows value are same in size table e.g "small" then it should insert same size_id to the color table for all small colors. Please check my code and help if anyone can.

product_color table

CODE:

 if (isset($_POST['submit']))
{   
    $con=mysqli_connect("localhost", "root", "");
    mysqli_select_db($con,"login"); 

    for ($i=0; $i<count($_POST['size']); $i++){

        $size = $_POST['size'][$i];          

        $qry1="INSERT INTO product_size (product_size) VALUES ('$size')";

        $result1=mysqli_query($con,$qry1);
        $product_size_id = mysqli_insert_id($con);                    


            $quantity = $_POST['dress_quantity'][$i];

            $color = $_POST['color'][$i];

            $qry2="INSERT INTO product_color (product_size_id, product_color, product_quantity) VALUES ('$product_size_id', '$color', '$quantity')";

            $result2=mysqli_query($con,$qry2); 
            if($result2)
            {
                echo '<script>alert("Record Added Successfully!")</script>';
                echo '<script>window.location="try.php"</script>';
            }
            else      
            {
                die("Error While Adding Stock! Please Try Again.");
            }
        }
    }

//HTML CODE:

<TABLE id="dataTable">
                  <thead>
                  <tr>
                  <th style="text-align: center;">&nbsp;Select&nbsp;</th>    
                  <th style="text-align: center;">&nbsp;<b>Size</b>&nbsp;</th>
                  <th style="text-align: center;">&nbsp;<b>Color</b>&nbsp;</th>
                  <th><b>Quantity</b></th>
                  </tr>
                  </thead>

                  <tbody>
                  <tr id='C1' class='customer'>
                  <td><input type="checkbox" name="chk"/></td>
                  <td><select  name="size[]" id="size" required="" >
                  <option value="">Select Size</option></select></td>
                  <td>
                  <select name="color[]" required="" >
                    <option value="">Select Color</option>
                    <option value="Aqua">Aqua</option>   
                    <option value="Blue">Blue</option>   
                    <option value="Black">Black</option>    
                    <option value="Green">Green</option>   
                  </select></td>

                  <td>
                  <input style="width: 120px; height: 26px; " oninput="javascript: if (this.value.length > this.maxLength) this.value = this.value.slice(0, this.maxLength);" type="number" name="dress_quantity[]" class="qty1" onchange="calculate();" min="1" max="1000" maxlength="4" placeholder="Size Quantity" value="" required="">  

                  </td>
                  </tr>
                  </tbody>
                  </TABLE>
                  <INPUT type="button" value="Add Row" onclick="addRow('dataTable')" />
                  <INPUT type="button" value="Delete Row" onclick="deleteRow('dataTable')" /> 

//HTML TABLE

Answer 1

First of you need to create a table which will have all the sizes you want. The size table should have only id for the size and a name for the size then use that table to create your size drop down.

Then do something like this on your HTML table page.

<?php
$con=mysqli_connect("localhost", "root", "");
mysqli_select_db($con,"login");

$query = mysqli_query("SELECT * FROM size");
$sizeParam = $database->query($query);
$sizeParam -> execute();
?>

then in your HTML:

<TABLE id="dataTable">
 <thead>
    <tr>
     <th style="text-align: center;">&nbsp;Select&nbsp;</th>    
     <th style="text-align: center;">&nbsp;<b>Size</b>&nbsp;</th>
     <th style="text-align: center;">&nbsp;<b>Color</b>&nbsp;</th>
     <th><b>Quantity</b></th>
    </tr>
 </thead>
 <tbody>
  <tr id='C1' class='customer'>
   <td><input type="checkbox" name="chk"/></td>
   <td>
    <select  name="size[]" id="size" required="" >
     <option value="">Select Size</option></select></td>
    <?php while($getSizeRow = $sizeParam -> fetch_assoc()){ ?>
      <option id="<?php echo $getSizwRow["sizeId"]; ?>"><?php echo $getSizwRow["sizeId"]; ?></option></select>
   </td>
   <td>
     <select name="color[]" required="" >
      <option value="">Select Color</option>
      <option value="Aqua">Aqua</option>   
      <option value="Blue">Blue</option>   
      <option value="Black">Black</option>    
      <option value="Green">Green</option>   
     </select></td>
   <td>
     <input style="width: 120px; height: 26px; " oninput="javascript: if (this.value.length > this.maxLength) this.value = this.value.slice(0, this.maxLength);" type="number" name="dress_quantity[]" class="qty1" onchange="calculate();" min="1" max="1000" maxlength="4" placeholder="Size Quantity" value="" required="">  
   </td>
  </tr>
 </tbody>
</TABLE>
 <INPUT type="button" value="Add Row" onclick="addRow('dataTable')" />
 <INPUT type="button" value="Delete Row" onclick="deleteRow('dataTable')" />

Now when you receive the data on to your processing PHP do something like this.

if (isset($_POST['submit'])){

    $con=mysqli_connect("localhost", "root", "");
    mysqli_select_db($con,"login"); 

        $quantity = $_POST['dress_quantity'];
        $color = $_POST['color'];
        $size = $_POST['size'];

        $qry2="INSERT INTO product_color (product_size_id, product_color, product_quantity) VALUES (?, ?, ?)";
        $result2= $con -> prepare($qry2);
        $result2 -> bind_param(isi, $product_size_id, $color, $quantity);

        if($result2 -> execute()){
         echo '<script>alert("Record Added Successfully!")</script>';
         echo '<script>window.location="try.php"</script>';
        }else{
                die("Error While Adding Stock! Please Try Again.");
       }
   }
}

This might contain errors this is the basic idea and I'm not much fluent with mysqli_* so if there's any let me know to correct them.