Re: Php Mysql application.
Thank you.
I tried to use INSERT..SELECT but it fails the query for this trial is shown below;
INSERT INTO `unpaid`(`TriId`, `StaffId`, `LoanNumber`, `OpeningBalance`, `AmountDeducted`, `ClosingBalance`, `Month`, `Year`)
SELECT TriId, LoanNumber, StaffId, (t.ApprovedAmounts as unpaid.OpeningBalance - t.MonthlyDeduction as unpaid.AmountDeducted) as ClosingBalance,
CURRENT_TIMESTAMP, CURRENT_TIMESTAMP FROM transactions t;
The two tables columns as created on database is shown below;
CREATE TABLE `unpaid` (
`Id` int(2) NOT NULL AUTO_INCREMENT,
`TriId` int(3) NOT NULL,
`StaffId` varchar(12) NOT NULL,
`LoanNumber` int(5) NOT NULL,
`OpeningBalance` decimal(10,2) NOT NULL,
`AmountDeducted` decimal(10,2) NOT NULL,
`ClosingBalance` decimal(10,2) NOT NULL,
`Month` datetime DEFAULT current_timestamp(),
`Year` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`Id`),
KEY `StaffId` (`StaffId`),
KEY `TriId` (`TriId`),
CONSTRAINT `unpaid_ibfk_1` FOREIGN KEY (`StaffId`) REFERENCES `staffdetails` (`StaffId`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `unpaid_ibfk_3` FOREIGN KEY (`TriId`) REFERENCES `transactions` (`TriId`) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE `transactions` (
`TriId` int(3) NOT NULL AUTO_INCREMENT,
`LoanNumber` int(5) NOT NULL,
`TriDate` datetime DEFAULT current_timestamp(),
`StaffId` varchar(12) NOT NULL,
`RequestDate` date DEFAULT current_timestamp(),
`Amounts` decimal(10,2) NOT NULL,
`Reason` text NOT NULL,
`Status` varchar(15) DEFAULT NULL,
`ApprovedAmounts` decimal(10,2) DEFAULT NULL,
`Installments` int(3) DEFAULT NULL,
`MonthlyDeduction` decimal(10,2) DEFAULT NULL,
`PayMode` varchar(10) DEFAULT NULL,
`ChequeNo` varchar(15) DEFAULT NULL,
`CompanyId` int(2) DEFAULT NULL,
PRIMARY KEY (`TriId`),
KEY `StaffId` (`StaffId`),
CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`StaffId`) REFERENCES `staffdetails` (`StaffId`) ON DELETE CASCADE ON UPDATE CASCADE
);