How can in insert multiple rows of data into one table field

Question

I'm creating a website so that users can buy market produce online and arrange to pick it up the day after. The basket page can display multiple products (product ID), i need to take this data and insert it into my Order_Items table i have it working but it only inserts the last product_ID.

Basket SQL

if ($_SESSION['Signedin']) {
   $query = "SELECT * FROM BASKET
INNER JOIN PRODUCT
ON BASKET.Product_ID=PRODUCT.Product_ID
INNER JOIN CUSTOMER
ON BASKET.Customer_ID=CUSTOMER.Customer_ID
INNER JOIN STALL
ON PRODUCT.Stall_ID=STALL.Stall_ID
WHERE CUSTOMER.Customer_ID=$_SESSION[CustID]";

  $runquery = oci_parse($connection, $query);

  oci_execute($runquery);   





$_SESSION['total'] = 0;

while($row = oci_fetch_assoc($runquery)) {  

$prodid = $row['PRODUCT_ID'];
$price = $row['PRICE'];
$quantity = $row['QUANTITYBASKET'];
$image = $row['IMAGEID'];
$prodname = $row['NAME'];
$stallname = $row['STALL_NAME'];

$subtotal = ($price * $quantity);

$_SESSION['total'] = $_SESSION['total'] + $subtotal;

echo'   <tbody>
      <tr>';

echo  '<td> <img src="' . $image . '" height="50" width="50"/></td>';
       echo  '<td>' . $prodname . '</td>';
     echo '<td>' . $stallname . '</td>';
        echo  '<td>' . $quantity . '</td>';
        echo '<td>&pound;' . $price . '</td>';    
      echo  '<td>&pound;' . $subtotal . '</td>';
        echo '<td><a href=removeproductsql.php?id='.$prodid.' class="btn btn-success" role="button">Remove</a></td>';             
 echo'     </tr>';
}
 }
 ?>

Pay Button including taking the collection time

 <form method="post">
            <select name="dateslot">
              <option value="1">Monday</option>
              <option value="2">Tuesday</option>
              <option value="3">Wednesday</option>
              <option value="4">Thursday</option>
              <option value="5">Friday</option>
              <option value="6">Saturday</option>
              <option value="7">Sunday</option>
            </select>

        </td>
        <td>
          <form method="post">
            <select name="timeslot">
              <option value="1">10:00-12:00</option>
              <option value="2">12:00-14:00</option>
              <option value="3">14:00-16:00</option>
              <option value="4">16:00-18:00</option>
            </select>

        </td>
        <?php
        echo '<td>&pound;' . $_SESSION['total'] . '</td>';
        ?>
        <td><input type="submit" name="addToOrder" value="Pay" formaction="orderitemssql.php" /></td>
      </form>
      </form>

Add To Orders SQL

<?php
ob_start(); //to make header work
//include connection
include ('PHP/connection.php');

//has form been submitted?

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

$total = $_SESSION['total'];
$quantity = $_SESSION['quantity'];
$day = $_POST['dateslot'];
$time = $_POST['timeslot'];
$customerid = $_SESSION['CustID'];
$prodid = $_SESSION['prodid'];




//Insert data
$query = "INSERT INTO ORDER_ITEMS
(Order_Items_ID, TotalPrice, ProductQuantity, Day_ID, Time_ID, Customer_ID, Product_ID)
VALUES
(OrderItems_seq.nextval, '$total', '$quantity', '$day', '$time', '$customerid', '$prodid')";

$runquery = oci_parse($connection,$query);
oci_execute($runquery);
header ('location:homepage.php');
} 
//to check if the if statement is working
{
    echo "Error";
}
ob_flush();
?>

ORDER_ITEMS Table in Oracle

CREATE TABLE ORDER_ITEMS(
    Order_Items_ID number (5) NOT NULL,
    TotalPrice number (5,2) NOT NULL,
    ProductQuantity number (5) NOT NULL,
    Day_ID number (5) references COLLECTION_DAY(Day_ID),
    Time_ID number (5) references COLLECTION_TIME(Time_ID),
    Customer_ID number (5) references CUSTOMER(Customer_ID),
    Product_ID number (5) references PRODUCT(Product_ID),
    Primary Key (Order_Items_ID));

Gordon Linoff · Accepted Answer · 2015-05-10 16:24:30Z

1

Why not do the insert using a single statement? I'm not sure what the exact processing is, but the SQL would look like:

INSERT INTO OrderItems (Order_Ites_ID, TotalPrice, ProductQuantity, Day_ID,
                        Time_ID, Customer_ID, Product_ID)
    SELECT <appropriate field>
    FROM BASKET
    INNER JOIN PRODUCT
    ON BASKET.Product_ID=PRODUCT.Product_ID
    INNER JOIN CUSTOMER
    ON BASKET.Customer_ID=CUSTOMER.Customer_ID
    INNER JOIN STALL
    ON PRODUCT.Stall_ID=STALL.Stall_ID
    WHERE CUSTOMER.Customer_ID=$_SESSION[CustID]

answered May 10, 2015 at 16:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

How can in insert multiple rows of data into one table field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related