Member Avatar for sami.asanga

Hi,

Im struggling to do a function like this.In the admin panel of my site,there is an option for the admin to select multiple orders using check boxes and marked them as accepted.And I need to update the product table at the same time according to the product quantities of the selected orders.How should I make this thing happen??
Do I need to put a for loop / a while loop to take the product Ids which need to be update??

Im so glad if some one help me to do this...Please..:)

  • 2 Contributors
  • 3 Replies
  • 123 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by ﻼim

Dani AI

Generated

As described, the goal is to let an admin select multiple orders and mark them accepted while decrementing product stock by the exact quantities chosen. provided a loop-based approach that updates each item one-by-one, but that pattern is fragile unless it also (1) aggregates quantities for the same product across all selected orders, (2) runs inside a database transaction, and (3) uses prepared statements and checks for failures. The reliable pattern is: validate input, compute totals per product, atomically apply stock changes, then record accepted/delivered rows, and commit.

A safe, practical workflow:

  1. Collect the selected order IDs and coerce them to integers.
  2. Single query: get product_id and SUM(quantity) grouped by product for those order IDs. This prevents double-decrement when the same product appears in multiple orders.
  3. Start a DB transaction (InnoDB). For each product attempt an atomic update that only succeeds when stock is sufficient (check affected rows). If any update fails, rollback and report which product(s) lack stock.
  4. If all updates succeed, insert delivery/accept records and mark the orders accepted, then commit.

Example sketch (PDO) — core idea, not a drop-in for your schema:

$pdo->beginTransaction();
// $ids is an array of validated integers
$stmt = $pdo->prepare('SELECT product_id, SUM(qty) AS need FROM order_items WHERE order_id IN (' . implode(',', $ids) . ') GROUP BY product_id');
$stmt->execute();
$items = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($items as $it) {
  $upd = $pdo->prepare('UPDATE products SET stock = stock - :need WHERE id = :id AND stock >= :need');
  $upd->execute([':need' => $it['need'], ':id' => $it['product_id']]);
  if ($upd->rowCount() === 0) { $pdo->rollBack(); throw new Exception("Insufficient stock for {$it['product_id']}"); }
}
// insert accept/delivery rows...
$pdo->commit();

Notes and cautions: use prepared statements (no mysql_*), enable exceptions for errors, test on a copy of the DB, and prefer row locks or the conditional UPDATE approach above to avoid race conditions. Log SQL errors and enable full error reporting while debugging.

Recommended Answers

All 3 Replies

Member Avatar for ﻼim

i hope this helps. ;)

$id=base64_decode($_GET['id']); //get the receipt ID and decode to base64
$check = 0;
	//ADMIN
	if($username == 'admin' && $password == $pass && $id != 0)
	{
		$query3=mysql_query("SELECT * from tbl_customer_order where receipt = $id");
		while($order2=mysql_fetch_object($query3))
		{
			$oquan=$order2->order_quantity;
			$oname=$order2->order_name;
			$obar=$order2->order_barcode;
			$query4=mysql_query("SELECT * from tbl_inventory where item_barcode = $obar");
			$item2=mysql_fetch_object($query4);
			$quantity=$item2->item_quantity;
			
			if($quantity < $oquan)
			{
				echo "<script type='text/javascript'>window.alert('$oname is at Low Quantity!')</script>";
				$check = 1;
				break;
			}
		}
		
		if($check == 0)
		{
			$date=date("M-d-Y");
		
			$query=mysql_query("SELECT * from tbl_item_order where receipt = $id"); //select the order
			$order=mysql_fetch_object($query);
			$quantity=$order->order_quantity;
			$price=$order->order_price;
			$customer=$order->customer_name;
		
			$query1=mysql_query("SELECT * from tbl_customer_order where receipt = $id"); //select the order
			while($order1=mysql_fetch_object($query1))//get the date from the query
			{		
				
				
				$cname=$order1->order_name;
				$cquan=$order1->order_quantity;
				$cprice=$order1->order_price;
				$ccat=$order1->order_category;
				$cbar=$order1->order_barcode;
				
				$query2=mysql_query("SELECT * from tbl_inventory where item_barcode = $cbar"); //select the item from inventory
				$item=mysql_fetch_object($query2);
				$idesc=$item->item_description;
				$isup=$item->item_supplier;
				$iprice=$item->item_sales_price;
				$iquan=$item->item_quantity;
				$isold=$item->item_sold;
				$isupprice=$item->item_supplier_price;
				$isalprice=$item->item_sales_price;
				$iiprice=$isalprice-$isupprice;
				$iprofit=$item->item_profit;
				
				$newquan=$iquan-$cquan;
				$newsold=$isold+$cquan;
				$newprofit=$iprofit+($iiprice*$cquan);
				
				mysql_query("UPDATE tbl_inventory set item_quantity = $newquan, item_sold = $newsold, item_profit = $newprofit where item_barcode = $cbar");			
				mysql_query("INSERT into tbl_delivered_item values(null, '$cname', '$idesc', $cbar, '$isup', $iprice, $price, $cquan, '$date', '$ccat', $id)"); //accepted order.insert all the items into the table tbl_delivered_item
			}		
		
	
			mysql_query("INSERT into tbl_accept_order values(null, $id, '$customer', '$date', $price, $quantity, '$username')"); //accepted order.insert data into the table tbl_accepted_order
			$query3=mysql_query("Select * from tbl_users where username='$customer'"); //update the table tbl_users
			$user=mysql_fetch_object($query3);
			$uquan=$user->quantity;
			$unum=$user->num_order;
			$uincome=$user->total_income;
			
			$newquan=$uquan+$quantity; //new quantity of orders in tbl_users
			$newnum=$unum+1; //new number of times ordered in tbl_users
			$newincome=$uincome+$price; //new total income in tbl_users
			
			mysql_query("UPDATE tbl_users set quantity=$newquan, num_order=$newnum, total_income=$newincome where username = '$customer'");
			mysql_query("UPDATE tbl_customer_order set paid = 1 where receipt = $id");
			mysql_query("UPDATE tbl_item_order set paid = 1 where receipt = $id");
		
			echo "<script type='text/javascript'>window.alert('Order Accepted!')</script>";
			echo "<META http-equiv='refresh' content='0;URL=orderitems.php'>";
		}
		
		else
		{
			echo "<META http-equiv='refresh' content='0;URL=orderitems.php'>";
		}
	}

goodLuck!

Member Avatar for sami.asanga

Thanks for your code..But it is still not worked..:(

Member Avatar for ﻼim

Thanks for your code..But it is still not worked..:(

would you mind showing your code for debugging?
thanks. Ϋ

Edited by ﻼim because: missing letter &quot;r&quot; .
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.