Checkbox Submitting 0 When Not Selected

[PCHomepage]

I have a pair of checkboxes set up to deselect one of them if the other is selected so that only one can be submitted. It is also an option to not select either but when that is done, the value received is still 0 when it should be nothing or NULL. I didn't think I needed to to do anything special but after seeing the problem I added some code to try to force it but it made no difference.

I have this currently:

$OffSite = (GetParam("OffSite")) ? GetParam("OffSite") : NULL;

I also tried:

$OffSite = (GetParam("OffSite")) ? GetParam("OffSite") : "";

The checkboxes are:

<input type="checkbox" name="OffSite" id="opt1" value="1"  onchange="setChecked(this);">yes 
<input type="checkbox" name="OffSite" id="opt0" value="0"  onchange="setChecked(this);">no

Note that GetParam() is a custom function that trims any $_GET or $_POST value and does other simple pre-processing but it is not responsible for the problem as it makes no difference if I use $_POST instead of the function.

 

[vacunita]

Technically speaking, un-selected checkboxes do not get submitted.

So isset($_POST['OffSite']) will always return false if nothing was checked. That is if you were to print out $_POST. (print_r($_POST) There would be no Offsite array key if nothing was checked.

How are you determining that you are getting 0 instead of nothing?

What exactly does your GetParam function do?





----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[PCHomepage]

Because when I submit it with nothing selected, 0 gets entered into the field of the database when NULL is the default value for the field. I've not tried echoing it to the screen but just having done so, they do indeed some to be missing from the array when not selected so I'm not sure where the 0 value is coming from.

Array ( [StaffID] => 1 [Description] => Friday/Sundays only. 1 Sunday a month off [Limitations] => One Sunday a month off [DaysAvail] => Array ( [0] => Fri[] [1] => Sun[] ) [update] => AdminAvailability [ID] => 1 ) 1

Here is GetParam() but, as I tried it with the standard $_POST and got the same result, I do not believe that this custom function has anything to do with the issue and, indeed, based on the $_POST above, it does not:

function GetParam($parameter_name, $default_value = "") {
	$parameter_value = "";
	if (isset($_POST[$parameter_name]))
		$parameter_value = strip(trim($_POST[$parameter_name]));
	else if (isset($_GET[$parameter_name]))
		$parameter_value = strip(trim($_GET[$parameter_name]));
	else
		$parameter_value = $default_value;
	return $parameter_value;
}

 

[jpadie]

you are setting parameter value to "" by overriding the default_value argument with an empty string (which is then interpreted by mysql (for a numeric field) as a zero.

if you are going to have boolean fields, it's best to have them as radios or equivalent logic. if you are going to have tristate fields then use real values (1,2,3 - or whatever) to determine those values.

 

[PCHomepage]

Even when NULL is the default field value? It is a tinyint, not boolean.

 

[jpadie]

tinyint is just boolean. or rather boolean is just 1 or 0 under the bonnet

 

[jpadie]

but the basic issue is that an empty string is not the same as an express NULL value. to store null typically I actually provide a NULL in the parameters. for this I always use PDO or some other method of expressly binding values to parameters for database writing.

very rarely any value in using NULL values in a db (unless you are incredibly short on space) to designate an actual value. Better, imo, to use a known value that has meaning to you.

 

[PCHomepage]

Well, in this case, even after changing the field to varchar, it is not getting NULL when I am supposedly forcing the value if no selection has been made with this:

$OffSite = (GetParam("OffSite")) ? GetParam("OffSite") : NULL;

Without actually going into unnecessary detail as to why, I need the NULL value to be there.

 

[vacunita]

So what does your insert/update statement look like?

What exactly gets placed in your query?


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[jpadie]

Changing to varchar won't help!! An empty string is then just an empty string. Nothing like a null value!

 

[PCHomepage]

The PHP building the update query looks like this:

$OffSite = (GetParam("OffSite")) ? GetParam("OffSite", NULL) : NULL;
$Club = (GetParam("Club")) ? GetParam("Club", NULL) : NULL;
$DaysSelected = (isset($_POST['DaysAvail'])) ? implode(',', $_POST['DaysAvail']) : "";
$updateSQL = sprintf("UPDATE availability SET StaffID=%d, Description=%s, OffSite=%d, Club=%d, DaysAvail=%s, Limitations=%s WHERE ID=%d",
	GetSQLValueString(GetParam("StaffID"), "int"),
	GetSQLValueString(GetParam("Description"), "text"),
	GetSQLValueString($OffSite, "text"),
	GetSQLValueString($Club, "text"),
	GetSQLValueString($DaysSelected, "text"),
	GetSQLValueString(GetParam("Limitations"), "text"),
	GetSQLValueString(GetParam("ID"), "int"));

DBConnect($updateSQL, "Update");

The generated update query looks like this where OffSite and Club have had no selections made:

UPDATE availability SET StaffID=1, Description='Friday/Sundays only. 1 Sunday a month off', OffSite=0, Club=0, DaysAvail=NULL, Limitations='One Sunday a month off' WHERE ID=1

 

[PCHomepage]

Here's a follow-up. Because I tend to reuse functions and code that I wrote for other projects, I had forgotten that GetSQLValueString() was a custom function although I do not believe it is the full problem but it might be related.

That said, if I submit:

$OffSite = (GetParam("OffSite")) ? GetParam("OffSite", NULL) : "";
echo $OffSite;

With yes checked it gives 1, no checked gives 0 and both unchecked gives nothing.

With the function:

$OffSite = (GetParam("OffSite")) ? GetParam("OffSite", NULL) : "";
$OffSite = GetSQLValueString($OffSite, "int");
echo $OffSite;

With yes checked it gives 1 while no checked or both unchecked gives NULL.

Finally, using this oddball hybrid test conditional:

$OffSite = (GetParam("OffSite") == 1) ? 1 : GetParam("OffSite");
$OffSite = (GetParam("OffSite") == 0) ? 0 : GetParam("OffSite");
$OffSite = (GetParam("OffSite") == "" || GetParam("OffSite") == NULL) ? NULL : GetParam("OffSite");
echo $OffSite;

With yes checked, it gives 1, with no checked it gives 0 and with neither checked it gives NULL. However, it still seems to put 0 into the database when no is checked or when both are unchecked!

If I do not use the functions functions at all, it still gives 0 when both are unchecked.

 

[jpadie]

as said, php interprets a null as an empty string (in these circumstances).

to insert null into mysql, you should either use prepared statements (highly advised) or actually insert NULL for that field (OR pass absolutely nothing, not even an empty string).

INSERT INTO mytable (myID, myData) VALUES (NULL, NULL)

or in php

$sql = 'INSERT INT mytable (myID, myData) VALUES (NULL, NULL)';

 

[PCHomepage]

Actually, I have already tried using a prepared statement but am having difficulty getting it to work as it indicates that the number of elements do not match the number of bind variables but no matter how many times I've counted I get a matching count!

if (isset($_POST)) {
	$stmt = $mysqli->prepare('
		UPDATE availability
		SET
			StaffID = ?,
			Description = ?,
			OffSite = ?,
			Club = ?,
			DaysAvail = ?,
			Limitations = ?
		WHERE ID = ?
	');
	$stmt->bind_param(
		$_POST['StaffID'],
		$_POST['Description'],
		$_POST['OffSite'],
		$_POST['Club'],
		$_POST['DaysAvail'],
		$_POST['Limitations'],
		$_POST['ID']
	);
	$stmt->execute();
}

 

[jpadie]

If offsite is not set then there will be too few params.

You need to use constructs like

Isset($_POST['offsite']) ? 1 : NULL;

 

[PCHomepage]

Actually, this fixed the prepared statement issue and it also solved the original problem. I had left out the "isiissi", bit for the bind_param() function which it apparently uses as part of the count. Thank you.

if (isset($_POST)) {
	$StaffID = $_POST['StaffID'];
	$Description = $_POST['Description'];
	$OffSite= (isset($_POST['OffSite'])) ? $_POST['OffSite'] : NULL;
	$Club = (isset($_POST['Club'])) ? $_POST['Club'] : NULL;
	$DaysSelected = (isset($_POST['DaysAvail'])) ? implode(',', $_POST['DaysAvail']) : NULL;
	$Limitations = $_POST['Limitations'];
	$ID = $_POST['ID'];

	$stmt = $mysqli->prepare('
		UPDATE availability
		SET
			StaffID = ?,
			Description = ?,
			OffSite = ?,
			Club = ?,
			DaysAvail = ?,
			Limitations = ?
		WHERE ID = ?
	');

	$stmt->bind_param(
		[COLOR=blue]"isiissi",[/color] 
		$StaffID, 
		$Description, 
		$OffSite, 
		$Club, 
		$DaysSelected, 
		$Limitations, 
		$ID
	);

	$stmt->execute();
	$stmt->close();
}

 

[jpadie]

Ah yes. Another reason why I don't use mysqli... Much prefer pdo as a common abstracted interface.