PHP SQL - Issue with SQL statement that has empty field

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']);

upload

That error means that the field name table1 (which is NOT one of the fields you’re showing in the binding :shifty:) 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.

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.

4 Likes

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

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.

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

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

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. :slight_smile:

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.

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

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

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

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.