[Resolved] [Resolved] Calculation with null

Joseph_Sliker

Hello again.

If one is using php to do a set of calculations that sometimes encounters Null inputs...how does one get Null to output rather than a zero?

For example, say a mess of reading and error scores are being entered, and the Words Read - Errors = Words Correct.

Sometimes the Words Read and Errors fields will be blank because the kid was not tested. The correct Words Correct score should be Null rather than zero.

Why enter it at all? Here's why...

The form that shoots this data into the MySQL is build to enter a bunch of scores for a kid tested at a certain point in time. (ie. not just the Reading, but Math and Writing as well. So sometimes some of the tests are omitted for sundry reasons...but the form is still used to enter the other data.

Here is the portion of the POST form that is used to pass the values to the script upon submit (this is a data editor page...the initial values in the boxes come from a query. If the user needs to, they can be edited at this point):

echo "<tr><th align=\"center\" colspan=\"2\">READING PROBE 1</th></tr>";
echo "<tr><th align=\"right\">Words Read/1 min.</th><td><input type=\"text\" name=ORF1WR size=\"5\" value=$Row[ORF1WR]></td></tr>";
echo "<tr><th align=\"right\">Errors</th><td><input type=\"text\" name=ORF1ERR size=\"5\" value=$Row[ORF1ERR]></td></tr>";
		echo "<tr><th align=\"right\">Words Read Correctly</th><td>(Recalculated on submit)</td></tr>";

I simultaneously pass the reading data and do the calculation in the Insert script page like so...

$ORF1WRC = $HTTP_POST_VARS['ORF1WR']-$HTTP_POST_VARS['ORF1ERR'];

but when I check the value of $ORF1WRC later, it is always zero when the WR and ERR scores are Null (at least I think they're null at that point unless I'm doing something wrong in the POST form.)

Suggestions welcome! Arrrgh!

Volitics

I would suggest using $_POST like so:

$ORF1WRC = $POST['ORF1WR']-$POST['ORF1ERR'];

instead of since $HTTP_POST_VARS since $_POST is newer.

Regarding your other question, you might want to set the MySQL column to have a 'NULL' default value.

If that doesn't work then you might use an "if" statement to assign a 'NULL' value to a zero value when it is extracted from the database.

swr

Use [man]isset[/man] to identify null versus non-null. See also [man]empty[/man].

if (isset($_POST['ORF1WR']) && isset($_POST['ORF1ERR']) {
    $ORF1WRC = $_POST['ORF1WR']-$_POST['ORF1ERR'];
}
else {
    $ORF1WRC = null;
}

Joseph_Sliker

Volitics:
Thanks for your suggestion. I changed to $_POST. This did not break anything, so that's okay. It did not solve the issue though. The MySQL columns in question are set to accept Nulls and have Null as default.

swr:
Your suggestion is working. At first I didn't get it and tried "empty" rather than isset. That didn't work until I found a missing close parenthesis ")" on the first line of the if statement. Here is how I reworked your code:

if (empty($_POST[ORF1WR]) && empty($_POST[ORF1ERR])) { 
    $ORF1WRC = 'null'; 
    } else { 
    $ORF1WRC = $_POST[ORF1WR]-$_POST[ORF1ERR]; 
}

The final problem I'm having is calculating a Median score and getting that to insert as a Null when necessary. The formula I have for that (a kludge, really, but it's reliable for this application) is still putting "0" in the destination field...I suspect it has something to do with whether or not I have quotes or single quotes or no quotes around the values.

This stuff seems to work best if the MySQL fields are set for CHAR type data, but I need this to store as INT data for subsequent queries and analysis.

I'm not going to mark this as "resolved" quite yet in hopes of getting a few more comments or suggestions about this stuff.

Thank you for your help!

swr

To insert a null into the database, use NULL with no quotes. This will insert a string, and a null:

insert into table ( foo, bar ) values ( 'NULL', NULL )

The foo column gets a non-null string that is equal to the word 'NULL', while the bar column gets a proper null (which is not a string at all). They are different, even though they both use the word "NULL".

Joseph_Sliker

This has been very helpful.

Thank you.