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

Question

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.

You are on the right track. This just needs some debugging. Can I get the table create statements and some sample input. — ryantxr
– ryantxr, Commented May 19, 2019 at 16:14
Hi ryantxr ... Does that help? I don't have a lot of test data as the PHP never ran ... but this is the type of data it SHOULD create if running correctly. You can see a 1 to many relationship ... i.e. One purchase order ... many items to purchase. — Louw Pieters
– Louw Pieters, Commented May 19, 2019 at 17:12

ryantxr · Accepted Answer · 2019-05-19 18:34:44Z

1

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;

edited May 19, 2019 at 18:34

answered May 19, 2019 at 16:43

ryantxr

4,2271 gold badge15 silver badges25 bronze badges

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

6 Comments

Louw Pieters Over a year ago

Hi ryantxr .. I tried your code and the first part works i.e. inserting into 'purchaseorders' table ... but it does not insert the details into "podetails". I get no error at first .. just the output "PO Placed: PO Number = ".. but when I debug I get "Property access is not allowed" when executing this line: "$stmt1 = mysqli_stmt_init($conn);"

ryantxr Over a year ago

Hmmm. It is running on my system.

ryantxr Over a year ago

I added a check after the function call.

ryantxr Over a year ago

I checked for failure on all he mysqli calls and they all process. There has to be something on your side that is different.

Louw Pieters Over a year ago

Thanks for your efforts and help so far - btw!

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related