sql-server - 从table2中选择ID插入到table1中
问题描述
我有两张桌子——Kontrahent 桌和 Orders 桌。首先，我将数据插入到Kontrahent表中，然后再插入到Orders表中。插入到订单中，在 IDAccount 列中，我必须从 Kontrahent 表中插入 IDKontrahent 列中的值。如何？
try {
//Wstawianie nowego kontrahenta
$query = "INSERT INTO dbo.Kontrahent (Nazwa,Odbiorca,Dostawca,NIP,Regon,Uwagi,KodPocztowy,Miejscowosc,UlicaLokal,AdresWWW,Email,Utworzono,Zmodyfikowano,Telefon,Fax,OsobaKontaktowa,Pracownik,IDKraju,NrKonta,SWIFT,NazwaBanku,IDPaymentType,Archiwalny,IDRodzajuTransportu,SupplyCity,UlicaDostawy,KodPocztowyDostawy,NazwaAdresuDostawy,OsobaKontaktowaDostawy,TelefonDostawy,IDPlatnikaVat,CzyFirma,CzyGlownaFirma,NazwaKonta,LimitKredytu,IDPriceList) VALUES ('$complete_billing_name',1,0,'','','','$billing_postcode','$billing_state','$billing_address','','$billing_email','$isoDate','$ModDate',$billing_phone,'',NULL,0,616,'','','',NULL,0,NULL,'','','','','','',NULL,NULL,NULL,NULL,NULL,NULL)";
$result = $conn->prepare($query);
var_dump($result);
unset($query);
} catch (Exception $e) {
die(print_r($e->getMessage()));
}
这不起作用：
try{
$select = "SELECT IDENT_CURRENT(Kontrahent) ";
$result22 = $conn->query($select);
$result22 ->execute();
$query = "INSERT INTO dbo.Orders (IDOrderType, IDAccount, Number, IDOrderStatus, IDPaymentType, Remarks, IDUser, IDWarehouse, IDCurrency, IDCompany) VALUES (15,$result22,$customer_id,2,1,NULL,1,10,1,1)";
$result = $conn->query($query);
var_dump($result);
unset($result);
} catch (Exception $e) {
die(print_r($e->getMessage()));
}
编辑：
当前代码如下所示：
// Inserting data into Order:
// IDOrder (broadcast automatically)
// Set OrderTypeID = 15 for orders from customers (14 orders from suppliers, 16 are offers)
// IDAccount - is the customer ID from the Contractor table
// Number - is the document number, e.g. order number from presta
// IDOrderStatus - is the current status of the order from the OrderStatus table (e.g. Open or you can add your own ..)
// IDPaymentType - this is the payment method for the order (from the PaymentTypes table)
// Remarks - Order notes
// IDUser - what user creates this entry, e.g. 1 = Admin (Users table)
// IDWarehouse - in which warehouse to create the order (ID from the Warehouse table)
// IDCurrency = 1 for PLN
// IDCompany = 1
// Get ID
$query = "SELECT IDENT_CURRENT('dbo.Kontrahent') AS ID";
$stmt = $conn->prepare($query);
if ($stmt->execute() === false) {
die("Error executing query.");
};
$result = $stmt->fetch(PDO::FETCH_ASSOC);
$id = $result;
var_dump($result);
print_r($id);
$stmt = null;
// Insert into Orders
$query = "
INSERT INTO dbo.Orders
(IDOrderType,
IDAccount,
Number,
IDOrderStatus,
IDPaymentType,
Remarks,
IDUser,
IDWarehouse,
IDCurrency,
IDCompany)
VALUES
(15, ?, ?, 2, 1, NULL, 1, 10, 1, 1)
";
$stmt = $conn->prepare($query);
$stmt->bindParam(1, $id, PDO::PARAM_INT);
$stmt->bindValue(2, $customer_id, PDO::PARAM_INT);
if ($stmt->execute() === false) {
die("Error executing query.");
};
print_r($stmt);
$stmt = null;
// Get ID Order from Orders
$query = "SELECT TOP 1 IDOrder FROM dbo.Orders ORDER BY IDOrder DESC";
//$query ="SELECT IDENT_CURRENT('dbo.Orders') AS IDTowaru";
$stmt = $conn->prepare($query);
if ($stmt->execute() === false) {
die("Error executing query.");
};
$result = $stmt->fetch(PDO::FETCH_ASSOC);
$idorder = $result;
var_dump ($idorder, $result);
$stmt = null;
搜索 KodKreskowy 的 IDTowaru。KodKreskowy 在商店和应用程序中只有一个相同的。
$query = "SELECT IDTowaru FROM dbo.Towar WHERE KodKreskowy = '$product_sku' ";
$stmt = $conn->prepare($query);
if ($stmt->execute() === false) {
die("Error executing query.");
};
$result = $stmt->fetch(PDO::FETCH_ASSOC);
$iditem = $result;
var_dump($iditem);
$stmt = null;
使用 Towar 表中的 IDItem 和 Orders 表中的 IDOrder 插入 OrderLines
// Inserting into OrderLines:
// IDOrderLine (broadcast automatically)
// IDOrder - Id of the order header from the Order table
// IDItem -ID of the item from the Item or Current Status table
// Quantity - Quantity of the item
// PriceNet - net price
// PriceGross - gross price including VAT
// IDVat - VAT rate ID from the VATRates table
// Remarks - any additional comments
// IDUser - what user creates this entry, e.g. 1 = Admin (Users table)
$query = "
INSERT INTO dbo.OrderLines
(IDItem,
IDOrder,
Quantity,
PriceNet,
PriceGross,
IDVat,
Remarks,
IDUser)
VALUES
(?, ?, ?, ?, ?, 1, 1, 1)
";
$stmt = $conn->prepare($query);
$stmt->bindParam(1, $iditem, PDO::PARAM_INT);
$stmt->bindParam(2, $idorder, PDO::PARAM_INT);
$stmt->bindValue(3, $quantity, PDO::PARAM_INT);
$stmt->bindValue(4, $product_price, PDO::PARAM_INT);
$stmt->bindValue(5, $product_price, PDO::PARAM_INT);
if ($stmt->execute() === false) {
die("Error executing query.");
};
$stmt = null;
} catch (Exception $e) {
die(print_r($e->getMessage()));
}
}
这是错误：
SQLSTATE[HY000]: General error: 20018 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_OrderLines_Towar". The conflict occurred in database "greenmonkey", table "dbo.Towar", column 'IDTowaru'. [20018] (severity 16) [ INSERT INTO dbo.OrderLines (IDItem, IDOrder,Quantity, PriceNet, PriceGross, IDVat, Remarks, IDUser) VALUES (1, 1, 1, 42, 42, 1, 1, 1) ]1
为什么 IDAccount 和 Number 为 1？
object(PDOStatement)#14917 (1) { ["queryString"]=> string(225) " INSERT INTO dbo.Orders (IDOrderType, IDAccount, Number, IDOrderStatus, IDPaymentType, Remarks, IDUser, IDWarehouse, IDCurrency, IDCompany) VALUES (15, ?, ?, 2, 1, NULL, 1, 10, 1, 1) " } array(1) { ["IDTowaru"]=> float(99) } array(1) { ["IDTowaru"]=> int(825) }
接下来，为什么 IDOrder 和 IDItem 都是 1？
[ INSERT INTO dbo.OrderLines (IDItem, IDOrder,Quantity, PriceNet, PriceGross, IDVat, Remarks, IDUser) VALUES (1, 1, 1, 42, 42, 1, 1, 1) ]1
解决方案
我认为你至少应该考虑以下几点：
您需要使用参数化语句来防止 SQL 注入问题并使用PDOStatement::bindParam设置每个参数值
您需要使用PDOStatement::fetch
SELECT从您的语句中获取结果
下一个示例，基于您的代码，可能有助于找到您的问题的解决方案：
<?php
try{
// Insert into Kontrahent
$query = "
INSERT INTO dbo.Kontrahent
(Nazwa, Odbiorca, Dostawca, NIP, Regon, Uwagi, KodPocztowy, Miejscowosc, UlicaLokal, AdresWWW, Email, Utworzono, Zmodyfikowano, Telefon, Fax, OsobaKontaktowa, Pracownik, IDKraju, NrKonta, SWIFT, NazwaBanku, IDPaymentType, Archiwalny, IDRodzajuTransportu, SupplyCity, UlicaDostawy, KodPocztowyDostawy, NazwaAdresuDostawy, OsobaKontaktowaDostawy,TelefonDostawy, IDPlatnikaVat, CzyFirma, CzyGlownaFirma, NazwaKonta, LimitKredytu, IDPriceList)
VALUES
(?, 1, 0, '', '', '', ?, ?, ?, '', ?, ?, ?, ?, '', NULL, 0, 616, '', '', '', NULL, 0, NULL, '', '', '', '', '', '', NULL, NULL, NULL, NULL, NULL, NULL)
";
$stmt = $conn->prepare($query);
$stmt->bindParam(1, $complete_billing_name, PDO::PARAM_STR);
$stmt->bindParam(2, $billing_postcode, PDO::PARAM_STR);
$stmt->bindParam(3, $billing_state, PDO::PARAM_STR);
$stmt->bindParam(4, $billing_address, PDO::PARAM_STR);
$stmt->bindParam(5, $billing_email, PDO::PARAM_STR);
$stmt->bindParam(6, $isoDate, PDO::PARAM_STR);
$stmt->bindParam(7, $ModDate, PDO::PARAM_STR);
$stmt->bindParam(8, $billing_phone, PDO::PARAM_STR);
if ($stmt->execute() === false) {
die("Error executing query.");
};
$stmt = null;
// Get ID
$query = "SELECT IDENT_CURRENT('dbo.Kontrahent') AS ID";
$stmt = $conn->prepare($query);
if ($stmt->execute() === false) {
die("Error executing query.");
};
$result = $stmt->fetch(PDO::FETCH_ASSOC);
$id = $row['ID'];
$stmt = null;
// Insert into Orders
$query = "
INSERT INTO dbo.Orders
(IDOrderType, IDAccount, Number, IDOrderStatus, IDPaymentType, Remarks, IDUser, IDWarehouse, IDCurrency, IDCompany)
VALUES
(15, ?, ?, 2, 1, NULL, 1, 10, 1, 1)
";
$stmt = $conn->prepare($query);
$stmt->bindParam(1, $id, PDO::PARAM_INT);
$stmt->bindValue(2, $customer_id, PDO::PARAM_INT);
if ($stmt->execute() === false) {
die("Error executing query.");
};
$stmt = null;
} catch (Exception $e) {
die(print_r($e->getMessage()));
}
?>
推荐阅读
- android - 使用 Download Manager + FileProvider 打开 APK 以更新应用
- c# - 依赖注入：从大型程序集中注入单个类
- flutter - 如何在应用打开后立即在 Flutter 上运行调用 URL 的方法？
- javascript - 在滚动时突出显示侧边栏项目
- c# - 如何从 HttpClientHandler 实现 ServerCertificateCustomValidationCallback
- android - 谷歌游戏控制台
- jquery - 禁用/隐藏 iframe pdfviewer 中的打印和下载按钮
- django - 如何在 Django 中获取用户的活动历史记录？
- python - 如何在 Tkinter Python 中运行代码时更改图像
- apache-nifi - 如何使用 getsftp 从变量中的动态数量的服务器中提取？