Search code examples
mysqlinsertauto-increment

MySQL Query Inserts Default Data instead of Input Data

I'm currently writing a web application for the private home loan sector. In this application, Loan Terms are created between a borrower, an originator, and a funding source, so that when they apply for a loan, they can choose the terms that match their needs best...

I'm using MySQL 5.4. The table has three foreign keys (borrowerId,originatorId,fundingSourceId). Existing rows can be updated, but my insert command is not inserting its data, only a new row with the table's default values. The table's format is as follows...

id                  int(11)    NOTNULL,PRIMARY,AUTO_INCREMENT
borrowerId          int(11)    NOTNULL,DEFAULT 0
originatorId        int(11)    NOTNULL,DEFAULT 0
fundingSourceId     int(11)    NOTNULL,DEFAULT 0
originatorFee       dec(10,2)  NOTNULL
fundingSourceFee    dec(10,2)  NOTNULL
interestRate        dec(5,3)   NOTNULL
penaltyRate         dec(5,3)   NOTNULL
loanDuration        int(11)    NOTNULL,DEFAULT 0
minimumInterestDays int(11)    NOTNULL,DEFAULT 0
disabled            tinyint(1) NOTNULL,DEFAULT 0
createdAt           datetime   NOTNULL
updatedAt           datetime   NULL
deletedAt           datetime   NULL

Here is my Insert command that is producing a row with zeroes across the board, instead of the data I enter (including Created At as 0000-00-00 00:00:00)

INSERT INTO loanterms
(borrowerId,originatorId,originatorFee,fundingSourceId,fundingSourceFee,
interestRate,penaltyRate,loanDuration,minimumInterestDays,disabled,createdAt)
VALUES (borrowerId = 64, originatorId = 3, originatorFee = 300.00, 
fundingSourceId = 11, fundingSourceFee = 300.00, interestRate = 15.00, 
penaltyRate = 20.00, loanDuration = 6, minimumInterestDays = 30, disabled = 0,
createdAt = now())

EDIT: The following query produces a row that contains all data properly... I had to insert the ID auto incrementing field into the query. Does this mean there is a problem with my auto-increment? How can I fix it?

INSERT INTO loanterms VALUES (124, 64, 3, 11, 300.00, 300.00, 15.00, 20.00, 6, 
30, 0, now(), NULL, NULL)

EDIT 2: Well thank you all for pointing out my stupid mistake... Sorry it took me a few minutes to realize it. This was a midnight query and I still haven't had my coffee yet today...

Solution

  • MySQL:

    Change: 

    INSERT INTO loanterms
(borrowerId,originatorId,originatorFee,fundingSourceId,fundingSourceFee,
interestRate,penaltyRate,loanDuration,minimumInterestDays,disabled,createdAt)
VALUES (borrowerId = 64, originatorId = 3, originatorFee = 300.00, 
fundingSourceId = 11, fundingSourceFee = 300.00, interestRate = 15.00, 
penaltyRate = 20.00, loanDuration = 6, minimumInterestDays = 30, disabled = 0,
createdAt = now())

    To: 

    INSERT INTO loanterms
set borrowerId = 64, 
    originatorId = 3, 
    originatorFee = 300.00, 
    fundingSourceId = 11, 
    fundingSourceFee = 300.00, 
    interestRate = 15.00, 
    penaltyRate = 20.00, 
    loanDuration = 6, 
    minimumInterestDays = 30, 
    disabled = 0,
    createdAt = now();