CODEWITHSUNDEEP
phpmysql
Louw Pieters
Louw Pieters

Reputation: 37

How do you execute multiple sql insert statements in one php page

Using php and mysql .. I want to execute two sql statements ... the first one to create a purchase order (PO) in the purchaseorders table ... the second to use that purchase order id to create multiple inserts into a 'podetails' table to capture the items that are contained with that PO. I cannot get multiple sql statements executed to do this. I might be approaching this completely wrong ... so will appreciate any help I can get.

If I only have the first part of the code to create the PO record - that works ... but when I add the second part to insert each record in the podetails table - nothing works. It no longer creates a PO record in the 'purchaseorders' table.

<?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;
        } 
    }                        
?>

I expected the output to be the PO number (after all podetail items records where created), but this code does not create any records.

Upvotes: 0

Views: 191

Answers (1)

ryantxr
ryantxr

Reputation: 4217

Your code is close to correct. I tweaked it a bit.

// 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";

I made these tables to run it locally.

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;

Upvotes: 1

Related Questions