php - 如何在php中匹配两行mysql同一个表

Question

我有一个表，如果需要，可以通过添加按钮在其中添加行。我的数据库中有两个表 product_size 和 product_color。 Product_color 表有包含尺码 ID 的列。我正在尝试这样做，如果两个或更多行值在尺寸表中相同，例如“小”，那么它应该为所有小颜色插入相同的 size_id 到颜色表。如果有人可以，请检查我的代码并提供帮助。

产品颜色表

代码:

 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代码:

<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 表格

Answer 1

首先，您需要创建一个表格，其中包含您想要的所有尺寸。尺码表应该只有尺码 ID 和尺码名称，然后使用该表创建您的尺码下拉列表。

然后在您的 HTML 表格页面上执行类似的操作。

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

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

然后在您的 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')" />

现在，当您接收到处理 PHP 的数据时，执行类似的操作。

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.");
       }
   }
}

这可能包含错误，这是基本思想，我对 mysqli_* 不是很流利，所以如果有任何错误，请告诉我以更正错误。