basil3 basil3 - 1 month ago 8
MySQL Question

Inserting multiple rows into MySQL using a subquery

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

Answer

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 = '" . $productID . "'")) {
Comments