1

I am learning basic PHP and SQL (using PHPmy admin) and I am having trouble understanding how to handle values from previous rows to calculate data in a new row in the table.

How would I calculate values in a row based on the previously entered row? For example, I want to have each row:
1. Auto create rows until Col A ID = 20
2. Col B – Auto add 1 month to previous row date
3. Col D – Previous row value minus payment(Col C)

Do I use PHP or sql? How would I set it out? Please be gentle I am still very new at this.

Table example

gpgekko
  • 3,506
  • 3
  • 32
  • 35
melv
  • 11
  • 1
  • why not use auto_increment? http://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html or http://stackoverflow.com/questions/5665571/auto-increment-in-phpmyadmin –  Dec 15 '16 at 09:17
  • Specify your sample data and expected result also. – Mansoor Dec 15 '16 at 09:18
  • I am using auto increment for ID already. Can it be used for months as well? – melv Dec 15 '16 at 09:23
  • @mansoor There is an image of an example table attached, I want to know how to have the rows calculate date based on previous rows. – melv Dec 15 '16 at 09:24

1 Answers1

0
CREATE TABLE #Details(ID INT IDENTITY(1,1),_Date DATE ,Payment      INT,
 LoanAmount INT)

 DECLARE @Date DATE,@Payment INT,@LoanAmount INT,@PreLoanAmount INT

 IF NOT EXISTS(SELECT 1 FROM #Details)
 BEGIN
    INSERT INTO #Details(_Date ,Payment ,LoanAmount )
    SELECT @Date,@Payment,@LoanAmount
 END
ELSE
BEGIN
     SELECT TOP 1 @PreLoanAmount = LoanAmount FROM #Details ORDER BY ID DESC

    INSERT INTO #Details(_Date ,Payment ,LoanAmount )
    SELECT DATEADD(MONTH,1,@Date),@Payment,@PreLoanAmount - @Payment

END
Mansoor
  • 4,061
  • 1
  • 17
  • 27
  • Could you add an explanation? so that @OP can learn something and not just reuse your code. He is as stated new at programming. –  Dec 15 '16 at 09:36