Insert with prepared statement

[phppup]

I have a table with 8 field titles and have successfully inserted data using a prepared statement targeting all 8 fields.

I want to change my data collection strategy and INSERT into only 3 fields (where the other fields will populates via an UPDATE).

This code gave me an error

$sql = "INSERT INTO $table (id, first_name, company_name) VALUES (?,?,?)";
if($stmt = mysqli_prepare($link, $sql)){
 
    mysqli_stmt_bind_param($stmt, "sss", $id, $first_name, $company_name);

etc.

starting that: Field 'field4' doesn't have a default value

Am I obligated to provide a value for every field in the table, even if some are empty, or can I address ONLY the pertinent fields?

Additionally, what is the "best" way to address the id field? Since it's an auto increment field and it seems like providing a false value is wrong. Yet a value appears to be required.

[Barand]

Because your table has 8 columns but you only want to insert into 3 of them, it will attempt to insert NULL values (or the columns' specified default values) into the remaining 5.

Therefore, if a column is specified as NOT NULL, it should have a default value if you don't always populate it when inserting a new record.

In addition, the id column should be an auto_incremented INT value by default and also excluded from the insert.

[phppup]

@Barand  Is there a benefit to adding a DEFAULT value (of empty) versus removing the NOT NULL parameter?

[Barand]

Consider a student who has a maths exam at the end of each term. They score 60% in each of the first 2 terms but they are ill for the third so no score can be entered.

If the "score" column in the result table is defined

score int NOT NULL DEFAULT '0'

then not inputting the score inserts a value of 0%. When the average score is calculated at the end of the year it is 40% ((60 + 60 + 0) / 3).

On the other hand, if it is defined as

score int

thus allowing a NULL value if omitted, the the average is a much fairer 60% ((60 + 60) / 2).

Consider NULL to be the absence of known value.

Use NOT NULL when it is mandatory that a value is provided.

[phppup]

@Barand  Thanks for two genuinely educational and understandable responses.

Did some more reading too.

For clarification, do you technically need

quantity INT NOT NULL DEFAULT 0;
TYPE VARCHAR(10) NOT NULL DEFAULT "SUMMER";

Can the NOT NULL be omitted?

Is there a reason to implement the (seeming) redundancy [since the default essentially auto-remedies the value from being null]?

[Phi11W]

NOT NULL cannot be omitted if the field must never contain NULLs. 

Remember that the DEFAULT clause only applies when inserting new records and where this field value is not specified. 

Including NOT NULL prevents the value from being set to NULL at some later point, i.e. this statement would fail:

update ..  
  set quantity = NULL 
, TYPE = NULL 
where ... 

Without NOT NULL, it would work. 

 

Regards, 
   Phill W. 

[phppup]

@Phi11W  To expand on your reply, I understand that NULL and "" are different entities, but would one be preferred as a value?

My (limited) experience has been that strings stored in a table (name, address, etc) are relieved and displayed "as is" (more or less).

But numbers (even if inaccurately stored as VARCHAR) can be used within computations to generate totals (new numbers).

Question:

If a row in a table visually has a value of 2 in field1, a 4 in field2, and field3 appears empty, how will it effect the result of a math equation with regard to whether field3 is actually "" versus NULL?

Will field1 + field2 + field3 equal 6 in both instances? Or will NULL cause a recognizable error?

What about multiplication? Is either "" or NULL defined as zero? (I'm guessing NO, but....)

I appreciate everyone's help.

Thanks.

[Barand]

With a little experimentation you could answer those questions yourself.

[phppup]

@Barand  Yes, I knew that. *wink*

I just thought I'd ask the chefs because I don't have the time to toy with the recipe.

Nonetheless, your assistance has been greatly appreciated, and spot on, as usual.

Thanks  🙂