php - 使用子查询将多行插入 MySQL

Question

我在尝试根据子查询中的 WHERE 条件插入新表时遇到了一些问题。

我有一个名为 productoptions 的表，它有四列 LineID、ProductSize、ProductColour、ProductID

我有第二个名为 productprice 的表，它有两列 LineID, Price

我的应用程序使用 PHP post 方法接受来自简单表单的用户输入“productID”。我需要它做的是根据用户在表单中给出的 productID，为 productoptions 表中存在的每个 LineID 在 productprice 表中插入一个新行。

我的 PHP 代码如下:

if (isset($_POST['btn-add-price'])) {
        $productID = mysqli_real_escape_string($con, $_POST['productID']);
        $price = mysqli_real_escape_string($con, $_POST['price']);


        if(empty($productID)) {
            $error = true;
            $num_error_two = "Please enter a value";
        }
        if(!is_numeric($productID)) {
            $error = true;
            $value_error_two = "Data entered was not numeric";
        }

        if(empty($price)) {
            $error = true;
            $num_error_three = "Please enter a value";
        }
        if(!is_numeric($price)) {
            $error = true;
            $value_error_three = "Data entered was not numeric";
        }
        if (!$error) {
            if(mysqli_query($con, "INSERT INTO productprice (LineID,Price) VALUES(SELECT(LineID FROM productoptions WHERE LineID = '" . $productID . "'), '" . $price . "')")) {
                $successmsg = "Successfully Added!";
            } else {
                $errormsg = "Product already exists!";
            }
        }
    } 

表单代码:

<form method="post" action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']);?>" class="col span-1-of-2 product-submit-form-form">

                    <div class="row">
                        <div class="col span-1-of-3">
                            <label for="name">Product ID</label>
                        </div>
                        <div class="col span-2-of-3">
                            <input type="text" name="productID" id="productID" placeholder="Product ID" required><br>
                            <span class="text-danger"><?php if (isset($value_error_two)) echo $value_error_two; ?></span> 
                            <span class="text-danger"><?php if (isset($num_error_two)) echo $num_error_two; ?></span> 
                        </div>
                    </div>

                    <div class="row">
                        <div class="col span-1-of-3">
                            <label for="name">Price</label>
                        </div>
                        <div class="col span-2-of-3">
                            <input type="text" name="price" id="price" placeholder="Price" required><br>
                            <span class="text-danger"><?php if (isset($value_error_three)) echo $value_error_three; ?></span> 
                            <span class="text-danger"><?php if (isset($num_error_three)) echo $num_error_three; ?></span>
                        </div>
                    </div>

                    <div class="row">
                        <div class="col span-1-of-3">
                            <label>&nbsp;</label>
                        </div>
                        <div class="col span-2-of-3">
                           <input type="submit" value="Add" name="btn-add-price">
                        </div>
                    </div>

                </form>

目前这会抛出 $errormsg

我的处理方式是否正确？请温柔点，我才刚刚开始接触 PHP，我能感觉到我在很多方面都缺乏理解!!!

修改后的代码和建议:

if (isset($_POST['btn-add-price'])) {
        $productIDTwo = mysqli_real_escape_string($con, $_POST['productIDTwo']);
        $price = mysqli_real_escape_string($con, $_POST['price']);

        //name can contain only alpha characters and space

        if(empty($productIDTwo)) {
            $error = true;
            $num_error_two = "Please enter a value";
        }
        if(!is_numeric($productIDTwo)) {
            $error = true;
            $value_error_two = "Data entered was not numeric";
        }

        if(empty($price)) {
            $error = true;
            $num_error_three = "Please enter a value";
        }
        if(!is_numeric($price)) {
            $error = true;
            $value_error_three = "Data entered was not numeric";
        }
        if (!$error) {
            if(mysqli_query($con, "INSERT INTO productprice (LineID,Price) SELECT(LineID, " . $price . " FROM productoptions WHERE LineID = '" . $productIDTwo . "')")) {
                $successmsg = "Successfully Added!";
            } else {
                $errormsg = "Product already exists!";
            }
        }
    }

这仍然会抛出 $errormsg - 我在 SQL 查询上方添加了 $successmsg 并且它确实出现在浏览器中所以它似乎是 SQL 代码的问题而不是表单输入错误？

正确代码:

if (isset($_POST['btn-add-price'])) {
        $productIDTwo = mysqli_real_escape_string($con, $_POST['productIDTwo']);
        $price = mysqli_real_escape_string($con, $_POST['price']);

        //name can contain only alpha characters and space

        if(empty($productIDTwo)) {
            $error = true;
            $num_error_two = "Please enter a value";
        }
        if(!is_numeric($productIDTwo)) {
            $error = true;
            $value_error_two = "Data entered was not numeric";
        }

        if(empty($price)) {
            $error = true;
            $num_error_three = "Please enter a value";
        }
        if(!is_numeric($price)) {
            $error = true;
            $value_error_three = "Data entered was not numeric";
        }
        if (!$error) {
            if(mysqli_query($con, "INSERT INTO productprice (LineID,Price) SELECT LineID, " . $price . " FROM productoptions WHERE ProductID = '" . $productIDTwo . "'")) {
                $successmsg = "Successfully Added!";
            } else {
                $errormsg = "Product already exists!";
            }
        }
    }

Answer 1

您应该执行 insert select (选择中的所有列而不是单独的代码)

if(mysqli_query($con, 
    "INSERT INTO productprice (LineID,Price) 
         SELECT LineID, " . $price . 
         " FROM productoptions WHERE LineID = '" . $productIDTwo . "'")) {