PHP SQL - Issue with SQL statement that has empty field

[robin01]

Hello,

I have a user entries form that allows the user to upload 3 images (3 different field/upload buttons see pic.). With this there is a good change the user might not upload all 3 images which means there could
be a empty field or fields. this empty field or fields seems to be causing issues with the SQL statement.

Warning: Trying to access array offset on value of type null in.
Error: SQLSTATE[23000]: Integrity constraint violation: 1048 Column ‘table1’ cannot be null
Unable to connect to table to add item

Is there a SQL statement something like an if statement I that i can use to ignore the empty field or fields within the sql statement?

Part of the sql statement. (the sql statement otherwise works)

	$stmt->bindValue(':dishOrder', $_POST['orderType'], PDO::PARAM_STR);
	$stmt->bindValue(':image1', $imageArray1['fileName']);
	$stmt->bindValue(':image2', $imageArray2['fileName']);
        $stmt->bindValue(':image3', $imageArray3['fileName']);

[DaveMaxwell]

That error means that the field name table1 (which is NOT one of the fields you’re showing in the binding ) has a NOT NULL constraint on it.

The only way around it is to either a) remove the NOT NULL constraint from the database field or b) add a REQUIRED attribute to the appropriate fields in the form which will prevent the form from being submitted.

[mabismad]

@robin01

With this there is a good change the user might not upload all 3 images

You need to normalize the database design and only store data that exists. There should not be a series of numbered columns in a table. Since there can be more than one image per main data item, there should be second table for the image data. You would insert the main row of data in the primary table, get the last insert id from that query, and use it when you insert a row for each related image in the image table. You would only insert row(s) in the image table for actual uploaded images.

If at least n (1, 2,…) image(s) are a requirement, after you have error checked and validated all the uploaded image data, you would test how many images were uploaded and setup a message for the user if not enough were chosen.

[SamA74]

@DaveMaxwell

a) remove the NOT NULL constraint from the database field or b) add a REQUIRED attribute to the appropriate fields in the form which will prevent the form from being submitted.

This would depend upon whether the user has a choice to upload up to 3 images, or if the must upload 3 images.

@DaveMaxwell

That error means that the field name table1 (which is NOT one of the fields you’re showing in the binding

Though this observation may indicate a bigger problem.

Having said that, it’s hard to say without knowing the exact nature of the project, but I suspect the advice from @mabismad about database normalisation is the way forward.

[droopsnoot]

@SamA74

This would depend upon whether the user has a choice to upload up to 3 images, or if the must upload 3 images.

These bits of the post would suggest that uploading fewer than three images is acceptable to the OP:

@robin01

Is there a SQL statement something like an if statement I that i can use to ignore the empty field or fields within the sql statement?

and where it says “Image upload (max. 3 images)”

@DaveMaxwell

which is NOT one of the fields you’re showing in the binding

We don’t know that - the OP only showed what parameter names they have used, not which columns they refer to. Having a column called table1 makes my teeth itch, but it may be legitimate depending on what these are images of. Pictures of tables, for example.

[DaveMaxwell]

@SamA74

This would depend upon whether the user has a choice to upload up to 3 images, or if the must upload 3 images.

Agreed. Hence why I gave the either/or option…

Though this might be a case where normalization may or may not resolve the situation. In fact, it may make it harder to enforce the integrity of the data (the required/not required). You’d have to add triggers or some other mechanism with additional fields to enforce it.

[DaveMaxwell]

@droopsnoot

We don’t know that - the OP only showed what parameter names they have used, not which columns they refer to.

Agreed. Just pointed out the disparity and to ensure the proper code is being looked at…

@droopsnoot

Having a column called table1 makes my teeth itch, but it may be legitimate depending on what these are images of. Pictures of tables, for example

Same. I ignored that part for that reason despite the cringiness of the name…

[SpacePhoenix]

@DaveMaxwell

Though this might be a case where normalization may or may not resolve the situation. In fact, it may make it harder to enforce the integrity of the data (the required/not required). You’d have to add triggers or some other mechanism with additional fields to enforce it.

A transaction could be used where the 1st query of the transaction checks to see how many images the user has already uploaded

[DaveMaxwell]

Oh, absolutely. But that’s also a code bit of logic and eliminates a DB double check.

My point simply is this may be an instance where normalization isn’t necessarily the best approach. 90% of the time I’d agree with the advice. Just not sure I do in this instance.