PHP / MySQL: recursive array / recursive SELECT

Question

EXPLAIN

Let's say we have this transaction table :

+----+-------------+--------+----------------+----------------+---------------------+
| id | id_customer | amount | id_where_trans | id_money_from  |        date_trans   |
+----+-------------+--------+----------------+----------------+---------------------+
|  1 |          10 |     10 |            100 | NULL           | 2015-07-20 10:20:30 |
|  2 |          10 |     -2 |            100 | NULL           | 2015-07-21 09:10:11 |
|  3 |          10 |      7 |            120 | NULL           | 2015-07-24 18:22:25 |
|  4 |          10 |    -11 |            120 | here the magic | 2015-07-24 18:22:26 |
+----+-------------+--------+----------------+----------------+---------------------+

Read this in "human" language:

id 1 => Customer Alessandro (id_customer 10) charge 10€ on his account in shop A (id_where trans = 100)

id 2 => Customer Alessandro spent 2€ on Shop A

id 3 => Customer Alessandro charge 7€ on his account in shop B

id 4 => Customer ALessandro spend 11€ in shop B.

At the end of period (month, for example), Shop B need to receive 8€ (+10-2) from another shop, in our example shop A.

/end of human read.

You imagine that insert is a simple (for id 4, for example):

INSERT INTO transaction (id_customer, amount, id_where_trans, date) VALUES (10,-11,120,2015-07-24 18:22:26)

GOAL

My goal is set - during the insert, and if possible / simpler via PHP - the SQL splitted in two or more SELECT:

INSERT INTO transaction (id_customer, amount, id_where_trans, id_money_from, date) VALUES (10,-8,120,100,2015-07-24 18:22:26)

INSERT INTO transaction (id_customer, amount, id_where_trans, id_money_from, date) VALUES (10,-3,120,NULL,2015-07-24 18:22:27)



    +----+-------------+--------+----------------+------------+---------------------+
| id | id_customer | amount | id_where_trans | id_money_from |        date         |
+----+-------------+--------+----------------+------------+---------------------+
|  1 |          10 |     10 |            100 | NULL       | 2015-07-20 10:20:30 |
|  2 |          10 |     -2 |            100 | NULL       | 2015-07-21 09:10:11 |
|  3 |          10 |      7 |            120 | NULL       | 2015-07-24 18:22:25 |
|  4 |          10 |     -8 |            120 | 100        | 2015-07-24 18:22:26 |
|  5 |          10 |     -3 |            120 | NULL       | 2015-07-24 18:22:27 |
+----+-------------+--------+----------------+------------+---------------------+

If I can get a table with that informations, I can run a query that make my final job correctly.

Basically, I need to calculate the id_shop where money are taken for discharge (in this case, -8 from 100, that made previous charge, and so is NOT NULL, and -3 from 120,itself, THIS IS NULL).

Please note that FIRST I consume / discharge the previous charge (id 4 now has id_money_from 100), AFTER I will consume / discharge others amounts (id 5 in effect is NULL, because that -3 has taken from id 3)

ASSUMPTION / MANDATARY

1) id are INCREMENTAL, no concurrency. Date are INCREMENTAL, date of id 4 is >= of id 3 and so on.

2) If amount of last recharge is made from same id where transaction is done (see id 1 and id 2) insert NULL (I need mandatary NULL)

3) First charge made, first need to be zero-ed, and so on.

MY LOGIC / PSEUDOCODE

1) If a transaction is negative, make a recursive getting LAST positive charge which, summed to the negative susequential, is not zero.

getted this charge, if id_where_trans==id_money_from we need insert, simply insert, with NULL

INSERT INTO transaction (id_customer, amount, id_where_trans, id_money_from, date) VALUES (10,-2,120,NULL,2015-07-24 18:22:26)

Recursive start here

If this amount is <= of last recharge, walking in database and split negative in two or more id_where_trans (problaby in real scenario this will be impossible, but I need to think that amount from discharge (id_money_from) could be splitted by 2, 3, x id_where_trans). For example, if SHOP A charge +1, Shop B charge +1, Shop C charge +1 and SHOP D DISCHARGE -3, we need to insert 3 different rows.

$amount_to_discharge = x;

$last_positive = $query->("SELECT * FROM transaction WHERE "); // make the SUB-SUM

if ($last_positive['amount'] >= $amount_to_discharge) {
    $query->("INSERT INTO......");
} else {
   // start recursive
   $sql = "SELECT * FROM AMOUNT WHERE amount > 0 AND id < $last_positive['id']";
   $new_search = $query->sql($sql);
   // how implement correctly that recursive?

}

Thank you guy. Let me know if you need others explain!