MySQL Forums
Forum List  »  PHP

Re: Php Mysql application.
Posted by: Allen Simba
Date: January 06, 2021 01:47AM

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
);

Options: ReplyQuote


Subject
Written By
Posted
January 05, 2021 03:09PM
January 05, 2021 03:23PM
Re: Php Mysql application.
January 06, 2021 01:47AM
January 06, 2021 10:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.