Inserting multiple rows into MySQL using a subquery

Question

I am having some trouble trying to insert into a new table based upon a WHERE condition in a subquery.

I have one table called productoptions which has four columns LineID, ProductSize, ProductColour, ProductID

I have a second table called productprice which has two columns LineID, Price

My application uses PHP post method to accept the user input 'productID' from a simple form. What I need it to do is insert a new row into the productprice table for every LineID that exists in the productoptions table based upon the productID given by the user in the form.

My PHP code is below:

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!";
            }
        }
    }

The form code:

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

Currently this throws the $errormsg

Am I even approaching this the correct way? Please be gentle I am only just getting into PHP and I can feel my understanding lacking in a number of areas!!!

Amended code with suggestions:

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!";
            }
        }
    }

This still throws the $errormsg - I have added the $successmsg above the SQL query and it does appear in the browser so it does seem to be an issue with the SQL code and not a form input error?

CORRECT CODE:

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!";
            }
        }
    }

I think you have the open parentheses between SELECT and LineID in the wrong spot? Maybe try "INSERT INTO productprice (LineID, Price) VALUES ((SELECT LineID FROM productoptions WHERE LineID = '" . $productID . "'), '" . $price . "')" — Pat Jones
– Pat Jones, Commented Oct 27, 2016 at 21:13
I don't think you need the subquery. Use insert into select. Old example, stackoverflow.com/questions/30290438/…. Also before Product already exists! you should check that the error was a duplicate and not something else. — chris85
– chris85, Commented Oct 27, 2016 at 21:19

ScaisEdge · Accepted Answer · 2016-10-28 08:42:37Z

3

You should perform an insert select (all the column in select and not in separated code)

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

edited Oct 28, 2016 at 8:42

answered Oct 27, 2016 at 21:19

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

basil3 Over a year ago

Thanks for the help... I think I understand this now but this still does not seem to work. I have amended my ID tag on the form as I was using that twice and thought it may be causing issues. I have also posted the $successmsg above the SQL query to see if it appears (and it does) so it does seem to be an issue with the SQL, This is the amended code that still throws the $errormsg - See amended code above

ScaisEdge Over a year ago

@basil3 i have update the answer ...... wrong ( in select ( removed ..

basil3 Over a year ago

brilliant..I had to change a small thing as I was actually querying the wrong column in the WHERE clause but it all makes sense now. I have added my question with the complete piece of code!

Jay Blanchard · Accepted Answer · 2016-10-28 21:03:24Z

0

First of all you need working SQL query.

INSERT INTO productprice (LineID,Price)
    SELECT LineID , YourPriceHere FROM productoptions 
    WHERE ProductID = YourProductHere;

You should make sure that your bare SQL works before writing it to PHP. To do this you can use command line mysql client or any other.

Also, concatinations makes your queries look really ugly and hard readable, so, if you don't using PDO to interact with your database, you could at least prepare sql queries like this:

$sql = 
   strtr(
     'SELECT * FROM productoptions WHERE ProductId = {ProductId}',
     ['ProductId' => $productId]
   );

Finally, will be good for you to reconsider your table and fields names to underscore, i.e. "product_options", "product_price", "product_id" and use camelCase in your php-code. Good luck :)

edited Oct 28, 2016 at 21:03

Jay Blanchard

34.5k17 gold badges82 silver badges130 bronze badges

answered Oct 27, 2016 at 21:53

lamik

991 silver badge7 bronze badges

1 Comment

basil3 Over a year ago

Thanks for the tips... I have already started renaming my database table and field names. I will have a read through my PHP and start to camelCase!

Collectives™ on Stack Overflow

Inserting multiple rows into MySQL using a subquery

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related