MySQL Forums
Forum List  »  PHP

Re: Php Mysql application.
Posted by: Peter Brawley
Date: January 06, 2021 10:31AM

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.

Options: ReplyQuote


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


Sorry, only registered users may post in this forum.

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.