How to SELECT and INSERT until ordered quantity is completed using nested query in MySQL

Question

Keeping in view the above picture I want to run my query until the $orderQty is met. In the picture the upper table is of products and the lower table is of temp_salesee which is resulted after running my code.
Here is my code:

    $orderQty = 22;
    $trID     = round(microtime(true) * 1000);
    $proID    = 1;

$con->begin_transaction();
$stmt = $con->prepare("
INSERT INTO `temp_salesee` (
    SELECT
         null,
         $trID,
        `pid`,
        `pur_price`,
        (CASE
                WHEN qty_avbl <= $orderQty THEN qty_avbl
                WHEN qty_avbl >= $orderQty THEN $orderQty
                else 0
            END),
        `batch_number`
        FROM `products`
        WHERE `pid` = ?
        ORDER BY `batch_number` ASC
)
        ");

$stmt->bind_param('s', $proID);
$stmt->execute();
$con->commit();

In temp_salesee at 3rd row qty should be 2 so that totals qty would be equal to $orderQty at this point query should stop and 4th, 5th rows should not be inserted. And if the $orderQty is less than the qty_avbl as showing in 1st row of products table then only 1st row should be inserted in temp_salesee table having qty of 5.
thanks

Answer 1

I hope and imagine that there must be a more performant solution, but anyway, consider the following:

Schema (MySQL v8.0)

DROP TABLE IF EXISTS product_batches;

CREATE TABLE product_batches
(batch_number SERIAL PRIMARY KEY
,product_id INT NOT NULL
,quantity_available INT NOT NULL
);

INSERT INTO product_batches VALUES
( 1,1,15),
( 3,1, 5),
( 7,1,20),
(10,1,30),
(11,1,50);

---

Query #1

SELECT batch_number
     , product_id
  FROM 
     ( SELECT x.*
            , COALESCE(LAG(SUM(y.quantity_available),1) OVER (ORDER BY batch_number),0) running
         FROM product_batches x 
         JOIN product_batches y
           ON y.product_id = x.product_id 
          AND y.batch_number <= x.batch_number 
        GROUP 
           BY x.batch_number
      ) a 
  WHERE running <=22;

batch_number	product_id
1	1
3	1
7	1

---

View on DB Fiddle

Edit:

Not tested extensively, but for older versions, I think you can do something like this (performance is probably terrible though), so seriously consider upgrading:

   SELECT x.*
        , z.total - SUM(y.quantity_available) running
     FROM product_batches x 
     JOIN product_batches y
       ON y.product_id = x.product_id 
      AND y.batch_number >= x.batch_number
     JOIN (SELECT product_id, SUM(quantity_available) total FROM product_batches GROUP BY product_id) z
       ON z.product_id = x.product_id
    GROUP 
       BY x.batch_number
   HAVING running <= 22;

Edit 2:

Perhaps this is clearer:

SELECT x.*
        , GREATEST(SUM(y.quantity_available)-22,0) balance
     FROM product_batches x 
     JOIN product_batches y
       ON y.product_id = x.product_id 
      AND y.batch_number <= x.batch_number
    GROUP 
       BY x.batch_number
   HAVING balance < quantity_available

https://www.db-fiddle.com/f/DHED9dyxR2gMWaStyBZbN/0