[Resolved] Pesky NULL vs. '0' (zero) problem

[Joseph_Sliker]

I got great help here in a recent/prior thread showing me how to insert real NULLs into an int column of a MySQL database when a form with an empty input box is submitted.

But now I am having problems with an update form/update query.

The user can view their data as submitted so far and there an "Edit" button at each row that will give them a form allowing them to edit that record/row.

(VASTLY simplified Form)

print ("<form method=\"POST\" action=\"WRITscript.php\"><tr><td><input type=\"hidden\" name=\"DATAPOINTID\" value=\"$DATAPOINTID\"></td><td><input type=\"text\" name=WRIT1 size=\"3\" value=$Row[WRIT1]><input type=\"hidden\" name=\"ACTION\" value=\"UPDATE\"><input type=\"submit\" value=\"SUBMIT AND SAVE my changes.\" name=\"B1\"></form></td></tr>\n");

The values seem to pass just fine to the script and are recieved at WRITscript.php:

$DATAPOINTID = $_POST[DATAPOINTID])) 
//convert empty boxes submitted to NULL
// I THINK THE PROBLEM IS HERE BECAUSE BOTH '0' AND EMPTY STRINGS EVAL AS "TRUE" USING THE "empty" function
If (empty($_POST[WRIT1])) {
	$WRIT1 = 'null'; 
    } else { 	
$WRIT1 = $_POST[WRIT1];
}

and the (simplified) update query is like so:

$sql = "UPDATE DATAWRIT SET 
WRIT1 = '$WRIT1', 
WHERE DATAPOINTID = '$DATAPOINTID' LIMIT 1";

The problem is, if the user enters an actual '0', it gets stored as a NULL, just as if the box was left empty. I want the '0' to be stored as such, if that is what is entered.
Is there a method to accomplish this?
Thanks yet again!

[bastien]

try isset

If ((empty($_POST[WRIT1]) )&&(!isset($_POST[WRIT1])))  { 

[laserlight]

No need to use empty() in bastien's example, in that case.

[Joseph_Sliker]

Wow! What quick responses! Thank you.

I tried both of these:

If ((empty($POST[WRIT1]))&&(!isset($POST[WRIT1]))) {
$WRIT1 = 'null';
} else {
$WRIT1 = $_POST[WRIT1];
}

AND

If (!isset($POST[WRIT1])) {
$WRIT1 = 'null';
} else {
$WRIT1 = $POST[WRIT1];
}

BUT I keep getting the same result: If I empty any value from the WRIT1 box before submit, it still stores as a '0'. Furthermore, if I call for a record that already contains null in the WRIT1 box, when I submit, the value changes from null to '0'
🙁

[laserlight]

Check that the field was (not) defined as NOT NULL

[Joseph_Sliker]

| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| STUDENTID | int(11) | YES | MUL | NULL | |
| WRIT1 | tinyint(3) unsigned | YES | | NULL | |
| DATAPOINTID | int(11) | | PRI | NULL | auto_increment |

[laserlight]

Oh, it is obvious.

You must set
WRIT1=NULL

But you're setting
WRIT1='NULL'

[Joseph_Sliker]

It is now written like this:

If (!isset($_POST[WRIT1])) { 
$WRIT1 = NULL; 
} else { 
$WRIT1 = $_POST[WRIT1]; 
}

The query string has been written like this:

$sql = "UPDATE DATAWRIT SET WRIT1 = '$WRIT1' WHERE DATAPOINTID = '$DATAPOINTID' LIMIT 1";
and this...
$sql = "UPDATE DATAWRIT SET WRIT1 = $WRIT1 WHERE DATAPOINTID = '$DATAPOINTID' LIMIT 1";
and this...
$sql = "UPDATE DATAWRIT SET WRIT1 = "$WRIT1" WHERE DATAPOINTID = '$DATAPOINTID' LIMIT 1";

But I'm still getting '0'

[laserlight]

That null value is a different null value.

You could try:

if (isset($_POST['WRIT1']))
	$WRIT1 = "'" . $_POST['WRIT1'] . "'";
else
	$WRIT1 = "NULL";

//...

$sql = "UPDATE DATAWRIT
	SET WRIT1 = {$WRIT1}
	WHERE DATAPOINTID = '{$DATAPOINTID}' LIMIT 1";

I've added the braces which may speed up variable interpolation in the string.

[Joseph_Sliker]

I tried the mods you suggested, but still the same result:

I added some echo statements to the update script to see what happens in the process. (ie.

echo "$WRIT1"; echo "<BR>";
echo $sql; 

When I enter a '0', the aforementioned echoes show this:

'0'
UPDATE DATAWRIT SET WRIT1 = '0' WHERE DATAPOINTID = '92' LIMIT 1;

When I clear the WRIT1 box on the submit form I get this:

''
UPDATE DATAWRIT SET WRIT1 = '' WHERE DATAPOINTID = '92' LIMIT 1

I got the same results with the code from the previous attempts...so it seems to not be passing the characters that say "NULL".

[Joseph_Sliker]

I realized the original INSERT query that puts the records in in the first place was working fine (it put in '0's and NULLs just as expected).
The critical part of the process in that part of the script goes like this:
The POST values are first passed into variables:

$WRIT1 = $_POST['WRIT1'];

Then the contents of the variables are evaluated like this:

if ($WRIT1[$i]=='') {$WRIT1[$i]='NULL';} else {$WRIT1[$i]=$WRIT1[$i];}

(in the case above, the form is submitting multiple rows at once...for a whole room full of kids)
So, since I'm only dealing with a single row in my Edit/Update form, I changed the code to this:

if ($WRIT1=='') {$WRIT1='NULL';} else {$WRIT1=$WRIT1;}

And now it works!
Done this way, empty and/or isset were not even needed.