Re: Php Mysql application.
1 Month` datetime DEFAULT current_timestamp(), Year` datetime DEFAULT current_timestamp() make no sense at all---you just need the one column, eg `ts` timestamp default current_)timestamp ... when you want to retrieve month or year from such timestamp, use the Month() or Year() functions.
2 In ...
INSERT INTO `unpaid`(`TriId`, StaffId`, LoanNumber`, ... )
SELECT TriId, LoanNumber, StaffId, ...
... the column names and values obviously have to be in matching order, otherwise the command produces nonsense.
3 I don't understand an `unpaid` table foreign key referencing a `transactions` table. What purpose would this serve?
4 INSERT ... SELECT ... (t.ApprovedAmounts as unpaid.OpeningBalance ... you can't alias a column value in one table with a reference to another table, and in any case here, there's no way for the server to know which `unpaid` row you're referencing. Let's see full pseudocode for this computation.
To insert and also do some arithmetic, it's often best to insert into an intermediate table, then join the intermediate table to the target table, compute, and insert the result.