如何将多行数据插入到一个表字段中
16lz
2021-01-22
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.
我正在创建一个网站,以便用户可以在线购买市场产品并安排在第二天购买。篮子页面可以显示多个产品(产品ID),我需要获取这些数据并将其插入我的Order_Items表中,我有它工作,但它只插入最后一个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>£' . $price . '</td>';
echo '<td>£' . $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>£' . $_SESSION['total'] . '</td>';
?>
<td><input type="submit" name="addToOrder" value="Pay" formaction="orderitemssql.php" /></td>
</form>
</form>
Add To Orders SQL
添加到订单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
Oracle中的ORDER_ITEMS表
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));
1 个解决方案
#1
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:
为什么不使用单个语句进行插入?我不确定具体处理是什么,但SQL看起来像:
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]
更多相关文章
- (phpQuery)对网站产品信息采集代码的优化
- Mysql--可用的 MySQL 产品和专业服务
- 如何在产品和类别应用程序树中将1个表连接到(2个不同的表作为一个
- 为独立的“产品”打包django项目及其依赖项