Mysql error adding blank numeric fields

PHP
1

I’m getting a MySql error trying to add form data to my database.
If the user leaves numeric fields blank on the form, php appears to be sending a blank string to Mysql for those numeric fields. The database will accept a null, which is what I want.
What is the best way to do this?

2

All form input values are strings, so what gets passed is an empty string, not NULL. You could have the code cast the values to whatever datatype or NULL as the case might be.

3

You would need to produce an insert statement like
insert into footable (age) values (NULL)

So, check the value of your variable. I would use trim() and then strlen() on the result. If length is 0, set the value to the string “NULL”

4

Thanks, I got it working. Form output is always a string, no getting around it, even for nulls. My problem was only with numeric fields. Actually they are Integer data values. I found I can test empty($formvalue) then change $formvalue to ‘NULL’. The same can be done for the character fields that weren’t giving an error, so that the database contains Null instead of a blank. I changed some of those.

While doing that, I also added checks on the numeric data. I found you can’t use is_int() on a string, but you can use is_numeric(), which is almost as good. Stops all bad data except decimals, exponents etc, including ‘$’ and ‘,’. Basically allows a float value thru, which probably inserts and is truncated - I didn’t test that. My data is going to a stored procedure CALL.

5

The manual shows you what values are considered “empty”. string 0 is one of them. Make sure that’s what you really want, because you might need to differentiate between the number zero and null.

Also, see ctype_digit() vs is_numeric()

6

Great advice there. Thanks.
I hadn’t done the testing yet, but yes, I do need zero and null. Ctype_digit is the answer. Too bad it’s not linked to is_int() in the manual. I never found it when I searched among the functions listed.

Had to pull the nulls on character fields too. My program code puts those in quotes. I’ll just accept empty strings for characters.