Insert NULL numeric values... not freakin' zeros!

Joseph_Sliker

Arrrgh!

Nulls...the bane of my existence. I still don't get it.

I am trying to POST some data to a MySQL insert script from a form that allows the user to input multiple records (rows) at a time. The POST data is sent via individual arrays.

For example... (very abbreviated here... all the stuff that loops the inputs through each record/row works fine)

Each row of the input form will be like so:

The values then pass to the insert script just fine...

(Next page, Destination script...)

$NAME = $POST['NAME'];
$SCORE = $POST[SCORE];

The insert query works just fine...

INSERT INTO '.$TableName .'(NAME, SCORE) VALUES ('$NAME[$i]', '$SCORE[$i]', ....

EXCEPT....

Sometimes there is NO SCORE VALUE to report and I want a NULL entered into the destination row for that record, not a 0...but I always end up with a zero in the cell.

😕

Sxooter

By chance, is that field set to not null?

MySQL takes a null insert into a not null field as a sign to insert the default, and if there isn't a default, to make one up and insert that. i.e. inserts are not allowed to fail, so they get munged as necessary to make them fit.

Joseph_Sliker

Here is a (slightly edited) copy of the table definition.

Sxooter

OK, so what MySQL is doing is converting a blank into a 0 instead of a null.

Note that postgresql, and most other databases, would error out on trying to insert a blank into a numeric / int field.

You need to insert NULL like so:

insert into table (field1) values (NULL);

Notice the lack of quotes on the NULL.

blank != NULL by the way.

Joseph_Sliker

Thank you for your reply.

I understand what you are saying. I guess the problem that I am having is that I need to convert the empty-string array elements of $SCORE[] into NULLs.

I sort of halfway got this to work when I wrote the code for the data entry boxes like so...

But then, on my data entry form, the word "NULL" appeared in each row/each score box when the user calls up the form.

Is there a way to have the entry boxes remain empty and convert those blanks into NULL somewhere in the process of posting to the insert script?

AstroTeg

Sounds like you need to do some data validation before you submit the data to the database. Just test the fields for blank and then change them to null (as necessary) so they happily insert into the database.

Just a technicality, but score1 and score2 fields look awfully alike. You're table is not normalized and may cause design problems later on.

Joseph_Sliker

Thanks for your reply. Could you please expand a bit about the normalization problems you see?

(The fieldnames used in the code I supplied are not the exact ones in the database...I pulled out a few fields and renamed the score fields just to keep the problem as simple as possible. Each row is for a test that has several subtests. Typically they would be given all at once, but sometimes one or more may be omitted for various reasons. So I don't want a "0" to appear in such cases as it would mess up subsequent graphing and/or. aggregate calculations.)

I have put together a working solution...but, not being a real programmer, I'm always wondering if what I write is simply a kludge.

On the destination page (after posting the variables from the form)...

// Post the variable arrays
$NAME = $_POST['NAME'];
$SCORE1 = $_POST[SCORE1];
$SCORE2 = $_POST[SCORE2]; 

//Run this loop to change blanks to NULL
for ($i=0; $i<count($NAME); $i++) {
if 	($SCORE1[$i]=='') {$SCORE1[$i]='NULL';} else {$SCORE1[$i]=$SCORE1[$i];}
if 	($SCORE2[$i]=='') {$SCORE2[$i]='NULL';} else {$SCORE2[$i]=$SCORE2[$i];}
 }

... start code for the input query here ...

$sql 'insert into $TableName (NAME, SCORE1, SCORE2) values ';

//Run this so that rows with no scores at all are not saved (kid not tested)
if (($SCORE1[$i] != 'NULL') || ($SCORE2[$i] != 'NULL'))  {
  ...the Values part of the insert query...
}

... end of input query

This solution does appear to be working.

laserlight

You might be able to optimise it by removing the loop that changes blanks to NULL, and integrate it into the loop that runs the query.

As for database normalisation, there is an article on PHP Builder:
Database Normalization And Design Techniques

Joseph_Sliker

Can you be a bit more specific as to how to code the process of turning blanks into NULLs into the insert loop? It sounds like a good idea, but my feeble attempts at doing so were messy, miserable failures.

Otherwise, even though the code is maybe not quite as elegant and efficient as I try to shoot for, the advantage is I do actually understand how my current code works rather than just rotely applying the more sophisticated suggestions that so many here have kindly supplied me in the past. (I'm starting to "get it". It's so exciting!).

I think the normalization issue that was brought up had to do with a misunderstanding of what data my records need to hold. Putting each subtest score for a given test administration into a separate record would probably cause more redundancy than it would cure.

But, some of the initial tables that I designed are even further from normalized than this one... sort of my "belt AND suspenders" approach to storing data.

laserlight

It's hard to say for sure, since optimisation tends to be dependent on the implementation.

A very simple piece of optimisation is to remove the loop invariant.
In this case, you appear to be calling count() on each iteration.
To optimise that, you would assign the value obtained from count to an external variable, then use that variable in the loop condition.

Joseph_Sliker

I'm really focused on cleaning up the code for this script right now so I can use it as a model for several sets of tests. I'll post more complete code shortly.

As ever, thanks for the help.