Context: The page is for menu item entry into an order. When a menu item button is pressed, data from 'theProducts' table is queried and inserted into 'theOrderItems' table. I also want the same button press to take the serving size value (theProducts.dbProdServing) and subtract that amount from the product's inventory (theInventory.dbInventoryAmt), and update that table accordingly.
The problem I'm having is trying to figure out how to have one button press execute two prepared statements with bound values at the same time, those being the statement that INSERTS product data into 'theOrderItems' table and the statement that UPDATEs 'theInventory' table.
Currently, the page runs the INSERT statement fine, but the UPDATE statement doesn't run at all.
There are three pages that handle the entire order system, 'insertOrder.php' opens the order, 'insertOrderItem.php' allows the adding of items to a specific order, and 'completeorder.php' just displays the total order. I'll only post the first two pages.
insertOrder.php
<?php
$pagetitle = 'Insert Order';
require_once 'header.php';
require_once 'connect.php';
$errormsg = "";
$showform = 1;
$sqlselectt = "SELECT * from theTables";
$resultt = $db->prepare($sqlselectt);
$resultt->execute();
$sqlselectc = "SELECT * from theCustomers";
$resultc = $db->prepare($sqlselectc);
$resultc->execute();
$sqlselects = "SELECT * from theStaff";
$results = $db->prepare($sqlselects);
$results->execute();
$sqlselectl = "SELECT * from theLocations";
$resultl = $db->prepare($sqlselectl);
$resultl->execute();
if( isset($_POST['thesubmit']) )
{
$formfield['ffOrderPickup'] = $_POST['orderPickup'];
$formfield['ffCustKey'] = $_POST['custKey'];
$formfield['ffTableKey'] = $_POST['tableKey'];
$formfield['ffStaffKey'] = $_POST['staffKey'];
$formfield['ffLocationKey'] = $_POST['locationKey'];
$formfield['ffOrderDate'] = $_POST['orderDate'];
$formfield['ffOrderTime'] = $_POST['orderTime'];
if(empty($formfield['ffCustKey'])){$errormsg .= "<p>The customer field is empty.</p>";}
if(empty($formfield['ffTableKey'])){$errormsg .= "<p>The table field is empty.</p>";}
if(empty($formfield['ffStaffKey'])){$errormsg .= "<p>The employee field is empty.</p>";}
if(empty($formfield['ffLocationKey'])){$errormsg .= "<p>The location field is empty.</p>";}
if(empty($formfield['ffOrderDate'])) {$errormsg .= "<p>The order entry date is not selected.</p>"; }
if(empty($formfield['ffOrderTime'])) {$errormsg .= "<p>The order entry time is not selected.</p>"; }
if($errormsg != "")
{
echo "<div class='error'><p>THERE ARE ERRORS!</p>";
echo $errormsg;
echo "</div>";
}
else
{
$sqlmax = "SELECT MAX(dbOrderKey) AS maxKey FROM theOrders";
$resultmax = $db->prepare($sqlmax);
$resultmax->execute();
$rowmax = $resultmax->fetch();
$maxKey = $rowmax['maxKey'];
$maxKey = $maxKey + 1;
try
{
$sqlinsert = 'INSERT INTO theOrders (dbOrderKey, dbCustKey, dbTableKey, dbStaffKey, dbLocationKey, dbOrderComplete, dbOrderDate, dbOrderTime, dbOrderMade, dbOrderPickup)
VALUES (:bvOrderKey, :bvCustKey, :bvTableKey, :bvStaffKey, :bvLocationKey, 0, :bvOrderDate, :bvOrderTime, 0, :bvOrderPickup)';
$stmtinsert = $db->prepare($sqlinsert);
$stmtinsert->bindvalue(':bvOrderKey', $maxKey);
$stmtinsert->bindvalue(':bvCustKey', $formfield['ffCustKey']);
$stmtinsert->bindvalue(':bvTableKey', $formfield['ffTableKey']);
$stmtinsert->bindvalue(':bvStaffKey', $formfield['ffStaffKey']);
$stmtinsert->bindvalue(':bvLocationKey', $formfield['ffLocationKey']);
$stmtinsert->bindvalue(':bvOrderDate', $formfield['ffOrderDate']);
$stmtinsert->bindvalue(':bvOrderTime', $formfield['ffOrderTime']);
$stmtinsert->bindvalue(':bvOrderPickup', $formfield['ffOrderPickup']);
$stmtinsert->execute();
echo "<p>Order Number: " . $maxKey . "</p>";
echo "<p>Location: " . $formfield['ffLocationKey'] . "</p>";
echo '<br><br><form action="insertOrderItem.php" method="post">';
echo '<input type="hidden" name="orderKey" value="' . $maxKey . '">';
echo '<input type="hidden" name="locationKey" value="' . $formfield['ffLocationKey'] . '">';
echo '<input type="submit" name="submit" value="Enter Order Items">';
echo '</form>';
$showform = 0;
}
catch(PDOException $e)
{
echo 'ERROR!!!' .$e->getMessage();
exit();
}
}
}
if ($visible == 1 && $showform == 1)
{
?>
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>" name="theform">
<fieldset><legend>Order Information</legend>
<table border>
<tr>
<th><label for="custKey">Customer:</label></th>
<td><select name="custKey" id="custKey">
<option value = "">Please Select a Customer</option>
<?php while ($rowc = $resultc->fetch() )
{
echo '<option value="'. $rowc['dbCustKey'] . '">' . $rowc['dbCustLast'] . '</option>';
}
?>
</select>
</td>
</tr>
<tr>
<th><label for="tableKey">Table:</label></th>
<td><select name="tableKey" id="tableKey">
<option value = "">Please Select a Table</option>
<?php while ($rowt = $resultt->fetch() )
{
echo '<option value="'. $rowt['dbTableKey'] . '">' . $rowt['dbTableKey'] . '</option>';
}
?>
</select>
</td>
</tr>
<tr>
<th><label for="staffKey">Employee:</label></th>
<td><select name="staffKey" id="staffKey">
<option value = "">Please Select an Employee</option>
<?php
while ($rows = $results->fetch() )
{
if($_SESSION['userid'] == $rows['dbStaffKey']) {
$selected = 'selected';
} else {
$selected = '';
}
echo '<option value="'. $rows['dbStaffKey'] . '" ' . $selected . '>' . $rows['dbStaffLast'] . '</option>';
}
?>
</select>
</td>
</tr>
<tr>
<th><label for="locationKey">Location:</label></th>
<td><select name="locationKey" id="locationKey">
<option value = "">Please Select an Location</option>
<?php
while ($rowl = $resultl->fetch() )
{
echo '<option value="'. $rowl['dbLocationKey'] . '" ' . $selected . '>' . $rowl['dbLocationCity'] . '</option>';
}
?>
</select>
</td>
</tr>
<tr>
<th>Pick a Delivery Type:</th>
<td><input type="radio" name="orderPickup" id="orderPickup"
value=1 <?php echo ' checked';?> />
<label for="pickup">Pickup</label>
<input type="radio" name="orderPickup" id="orderPickup"
value=0 />
<label for="inHouse">In-House</label>
</td>
</tr>
<tr>
<th><label for="orderDate">Entry Date:</label></th>
<td><input type="date" name="orderDate" id="orderDate" value="<?php if( isset($formfield['ffOrderDate'])){echo $formfield['ffOrderDate'];}?>" /></td>
</tr>
<tr>
<th><label for="orderTime">Entry Time:</label></th>
<td><input type="time" name="orderTime" id="orderTime" value="<?php if( isset($formfield['ffOrderTime'])){echo $formfield['ffOrderTime'];}?>" /></td>
</tr>
</table>
<input type="submit" name = "thesubmit" value="Enter">
</fieldset>
</form>
<br><br>
<?php
}
include_once 'footer.php';
?>
insertOrderItem.php
<?php
$pagetitle = 'Insert Order Items';
require_once 'header.php';
require_once 'connect.php';
$formfield['ffOrderKey'] = $_POST['orderKey'];
$formfield['ffProdKey'] = $_POST['prodKey'];
$formfield['ffProdPrice'] = $_POST['prodPrice'];
$formfield['ffProdServing'] = $_POST['prodServing'];
$formfield['ffLocationKey'] = $_POST['locationKey'];
$sqlselectc = "SELECT * FROM theCategories";
$resultc = $db->prepare($sqlselectc);
$resultc->execute();
$sqlselecti = "SELECT * FROM theInventory";
$resulti = $db->prepare($sqlselecti);
$resulti->execute();
if (isset($_POST['OIEnter'])) {
$rowi = $resulti->fetch();
$invRem = $rowi['dbInventoryAmt'] - $formfield['ffProdServing'];
$sqlinsert = 'INSERT INTO theOrderItems (dbOrderKey, dbProdKey, dbProdPrice)
VALUES (:bvOrderKey, :bvProdKey, :bvProdPrice)';
$stmtinsert = $db->prepare($sqlinsert);
$stmtinsert->bindValue(':bvOrderKey', $formfield['ffOrderKey']);
$stmtinsert->bindValue(':bvProdKey', $formfield['ffProdKey']);
$stmtinsert->bindValue(':bvProdPrice', $formfield['ffProdPrice']);
$stmtinsert->execute();
$sqlupdate = "UPDATE theInventory
SET dbInventoryAmt = :bvInventoryAmt
WHERE dbLocationKey = :bvLocationKey
AND dbProdKey = :bvProdKey";
$stmtupdate = $db->prepare($sqlupdate);
$stmtupdate->bindValue(':bvInventoryAmt', $invRem);
$stmtupdate->bindValue(':bvLocationKey', $formfield['ffLocationKey']);
$stmtupdate->bindValue(':bvProdKey', $formfield['ffProdKey']);
$stmtupdate->execute();
}
if (isset($_POST['DeleteItem'])) {
$sqldelete = "DELETE FROM theOrderItems WHERE dbOrderItemKey = :bvOrderItemKey";
$stmtdelete = $db->prepare($sqldelete);
$stmtdelete->bindValue(':bvOrderItemKey', $_POST['orderItemKey']);
$stmtdelete->execute();
}
if (isset($_POST['UpdateItem'])) {
$formfield['ffProdPrice'] = trim($_POST['newProdPrice']);
$formfield['ffOrderNotes'] = trim($_POST['newOrderNote']);
$sqlupdateoi = 'UPDATE theOrderItems
SET dbProdPrice = :bvProdPrice,
dbOrderNotes = :bvOrderNotes
WHERE dbOrderItemKey = :bvOrderItemKey';
$stmtupdateoi = $db->prepare($sqlupdateoi);
$stmtupdateoi->bindValue(':bvOrderItemKey', $_POST['orderItemKey']);
$stmtupdateoi->bindValue(':bvProdPrice', $formfield['ffProdPrice']);
$stmtupdateoi->bindValue(':bvOrderNotes', $formfield['ffOrderNotes']);
$stmtupdateoi->execute();
}
$sqlselecto = 'SELECT theOrderItems.*, theProducts.dbProdName, theProducts.dbProdServing, theCategories.*
FROM theOrderItems, theProducts, theCategories
WHERE theOrderItems.dbProdKey = theProducts.dbProdKey
AND theProducts.dbCatKey = theCategories.dbCatKey
AND theOrderItems.dbOrderKey = :bvOrderKey';
$resulto = $db->prepare($sqlselecto);
$resulto->bindValue(':bvOrderKey', $formfield['ffOrderKey']);
$resulto->execute();
if($visible == 1 && ($_SESSION['userpermit'] == 1 || $_SESSION['userpermit'] == 3 || $_SESSION['userpermit'] == 4))
{
?>
<fieldset><legend><b>Enter Items for Order Number: <?php echo $formfield['ffOrderKey']; ?></b></legend>
<table border>
<?php
$counter = 0;
echo '<tr><b>';
while ($rowc = $resultc->fetch()){
if ($counter == 2){
echo '</tr><tr>';
$counter = 0;
}
$counter++;
echo '<th valign = "middle" align = "center">' . $rowc['dbCatName'] . '<br> <table border>';
$sqlselectp = "SELECT * FROM theProducts WHERE dbCatKey = :bvCatKey";
$resultp = $db->prepare($sqlselectp);
$resultp->bindValue(':bvCatKey', $rowc['dbCatKey']);
$resultp->execute();
while ($rowp = $resultp->fetch()){
echo '<td>';
echo '<form action = "' . $_SERVER['PHP_SELF'] . '" method = "post">';
echo '<input type = "hidden" name = "orderKey" value = "' . $formfield['ffOrderKey'] . '">';
echo '<input type = "hidden" name = "prodKey" value = "' . $rowp['dbProdKey'] . '">';
echo '<input type = "hidden" name = "prodPrice" value = "' . $rowp['dbProdPrice'] . '">';
echo '<input type = "hidden" name = "prodServing" value = "'. $rowp['dbProdServing'] . '">';
echo '<input type = "submit" id="order" name = "OIEnter" value = "' . $rowp['dbProdName'] . '">';
echo '</form>';
echo '</td>';
}
echo '</table></th>';
}
echo '</tr>';
?>
</table>
</table>
</fieldset>
<br><br>
<table>
<tr>
<td>
<table border>
<tr>
<th>Item</th>
<th>Category</th>
<th>Description</th>
<th>Serving Size</th>
<th>Price</th>
<th>Notes</th>
<th></th>
<th></th>
</tr>
<?php
$ordertotal = 0;
while ($rowo = $resulto->fetch()){
$ordertotal = $ordertotal + $rowo['dbProdPrice'];
echo '<tr><td>' . $rowo['dbProdName']
. '</td><td>' . $rowo['dbCatName']
. '</td><td>' . $rowo['dbProdDesc']
. '</td><td>' . $rowo['dbProdServing']
. '</td><td>' . $rowo['dbProdPrice']
. '</td><td>' . $rowo['dbOrderNotes']
. '<td>';
echo '<form action = "' . $_SERVER['PHP_SELF'] . '" method = "post">';
echo '<input type = "hidden" name = "orderKey" value = "' . $formfield['ffOrderKey'] . '">';
echo '<input type = "hidden" name = "orderItemKey" value = "' . $rowo['dbOrderItemKey'] . '">';
echo '<input type = "submit" name = "NoteEntry" value = "Update">';
echo '</form></td><td>';
echo '<form action = "' . $_SERVER['PHP_SELF'] . '" method = "post">';
echo '<input type = "hidden" name = "orderKey" value = "' . $formfield['ffOrderKey'] . '">';
echo '<input type = "hidden" name = "orderItemKey" value = "' . $rowo['dbOrderItemKey'] . '">';
echo '<input type = "submit" name = "DeleteItem" value = "Delete">';
echo '</form></td></tr>';
}
echo '<tr><th></th><th></th><th>Total</th><th>' . $ordertotal . '</th><th></th><th></th><th></th></tr>';
?>
</table>
<?php
if(isset($_POST['NoteEntry'])){
$sqlselectoi = 'SELECT theOrderItems.*, theProducts.dbProdName
FROM theOrderItems, theProducts
WHERE theOrderItems.dbProdKey = theProducts.dbProdKey
AND theOrderItems.dbOrderItemKey = :bvOrderItemKey';
$resultoi = $db->prepare($sqlselectoi);
$resultoi->bindValue(':bvOrderItemKey', $_POST['orderItemKey']);
$resultoi->execute();
$rowoi = $resultoi->fetch();
echo '</td><td>';
echo '<form action = "' . $_SERVER['PHP_SELF'] . '" method = "post">';
echo '<table>';
echo '<tr><td>Price: <input type="text" name = "newProdPrice" value = "' .
$rowoi['dbProdPrice'] . '"></td></tr>';
echo '<tr><td>Notes: <input type="text" name = "newOrderNote" value = "' .
$rowoi['dbOrderNotes'] . '"></td></tr>';
echo '<tr><td>';
echo '<input type = "hidden" name = "orderKey" value = "' . $formfield['ffOrderKey'] . '">';
echo '<input type = "hidden" name = "orderItemKey" value = "' . $rowoi['dbOrderItemKey'] . '">';
echo '<input type = "submit" name = "UpdateItem" value = Update Item">';
echo '</td></tr></table>';
}
?>
</td></tr>
</table>
<br><br>
<?php
echo '<form action = "completeorder.php" method = "post">';
echo '<input type = "hidden" name = "orderKey" value = "' . $formfield['ffOrderKey'] . '">';
echo '<input type = "submit" name = "CompleteOrder" value = "Complete Order">';
echo '</form>';
}
include_once 'footer.php';
?>
