php - 如何在一个php页面中执行多个sql插入语句

使用php和mysql ..我要执行两个sql语句...第一个在Purchaseorders表中创建采购订单（PO）...第二个使用该采购订单ID在'podetails中创建多个插入'表以捕获该PO包含的项目。我无法执行多个sql语句来执行此操作。我可能正在解决完全错误的问题，因此，我将不胜感激。

如果我只有代码的第一部分来创建PO记录，那行得通...但是当我添加第二部分将每个记录插入podetails表中时，则行不通。它不再在“采购订单”表中创建PO记录。

<?php
    // establish DB connection (this includes creates $conn variable)
    include_once 'dbh.inc.php';

    session_start();
    $userId = $_SESSION['u_Id'];
    $supplierId = $_POST['supplierId'];
    $order_items = $_POST['orderitems'];

    // create PO record
    $sql = "INSERT INTO purchaseorders (PODate, supplierId, userId)
    VALUES (?,?,?);";
    $stmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt, $sql)) {
        header("Location: ../place.php?error=sqlerror");
        echo "SQl Connect error!";
    }
    else {
        $date = date('Y-m-d H:i:s');
        mysqli_stmt_bind_param($stmt, "sss", $date, $supplierId,
        $userId);
        mysqli_stmt_execute($stmt);
    }

    // // get last PO record id
    $POId = mysqli_insert_id($conn);

    // for each record in grid - create PO details record
    foreach ($order_items as $item) {
        $sql1 = "INSERT INTO podetails (PONumber, productId, poquantity,
                 pocostPrice, delivered, paidStatus) VALUES
                 (?,?,?,?,?,?);";
        $stmt1 = mysqli_stmt_init($conn);
        if (!mysqli_stmt_prepare($stmt1, $sql1)) {
            echo "SQl Connect error!";
        }
        else {
            mysqli_stmt_bind_param($stmt1, "ssssss", $POId,
                 $item['prodId'], $item['qty'], $item['costPrice'],
                 "false", "false");
            mysqli_stmt_execute($stmt1);
            echo "PO Placed: PO Number = ".$POId;
        }
    }
?>

我希望输出是PO编号（在创建了所有podetail项记录之后），但是此代码不会创建任何记录。

最佳答案

您的代码接近正确。我稍微调整了一下。

// establish DB connection (this includes creates $conn variable)

$conn = mysqli_connect("127.0.0.1", "app", "aaaa", "sss");
if ( ! $conn ) {
    throw new Exception('Connection failed');
}

$userId = 123;
$supplierId = 200;
$order_items = [
    ['prodId' => 2000, 'qty' => 5, 'costPrice' => 1.25],
    ['prodId' => 2001, 'qty' => 7, 'costPrice' => 2.25],
    ['prodId' => 2002, 'qty' => 9, 'costPrice' => 1.20],
    ['prodId' => 2003, 'qty' => 15, 'costPrice' => 3.25],
    ['prodId' => 2004, 'qty' => 25, 'costPrice' => 5.22],
    ['prodId' => 2005, 'qty' => 35, 'costPrice' => 5.25],
    ['prodId' => 2006, 'qty' => 45, 'costPrice' => 11.51],
];

// create PO record
$sql = "INSERT INTO purchaseorders (PODate, supplierId, userId) VALUES (?,?,?);";
$stmt = mysqli_stmt_init($conn);
if (!mysqli_stmt_prepare($stmt, $sql)) {
    // header("Location: ../place.php?error=sqlerror");
    throw new Exception("SQL error! " . mysqli_error($conn));
}
else {
    $date = date('Y-m-d H:i:s');
    mysqli_stmt_bind_param($stmt, "sss", $date, $supplierId, $userId);
    mysqli_stmt_execute($stmt);
}

// // get last PO record id
$POId = mysqli_insert_id($conn);
echo "POId = " . print_r($POId, true) . "\n";

$sql1 = "INSERT INTO podetails (PONumber, productId, poquantity,
            pocostPrice, delivered, paidStatus) VALUES
            (?,?,?,?,?,?);";
$stmt1 = mysqli_stmt_init($conn);
if (!$stmt1) {
    throw new Exception("SQl Connect error! " . mysqli_error($conn));
}

if (!mysqli_stmt_prepare($stmt1, $sql1)) {
    throw new Exception("SQl Connect error! " . mysqli_error($conn));
}
// for each record in grid - create PO details record
foreach ($order_items as $item) {
    $delivered = "false";
    $paidStatus = "false";
    if ( ! mysqli_stmt_bind_param($stmt1, "ssssss", $POId,
    $item['prodId'], $item['qty'], $item['costPrice'], $delivered, $paidStatus) ) {
        throw new Exception("SQl error! " . mysqli_error($conn));
    }
    if( ! mysqli_stmt_execute($stmt1) ) {
        throw new Exception("SQl error! " . mysqli_error($conn));
    }
}

echo "PO Placed: PO Number = ".$POId . "\n";

我制作了这些表以在本地运行它。

CREATE TABLE `purchaseorders` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `PODate` date DEFAULT NULL,
  `supplierId` int(10) unsigned DEFAULT NULL,
  `userId` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

CREATE TABLE `podetails` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `PONumber` int(10) unsigned DEFAULT NULL,
    `productId` int(10) unsigned DEFAULT NULL,
    `poquantity` int(11) DEFAULT NULL,
    `pocostPrice` decimal(9,2) DEFAULT NULL,
    `delivered` varchar(20) NOT NULL DEFAULT 'false',
    `paidStatus` varchar(20) NOT NULL DEFAULT 'false',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;

关于php - 如何在一个php页面中执行多个sql插入语句，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/56209582/