Hello,

I'm trying to do some UPDATE queries using a prepared statement with PHP 5.2.1 and MySQL 5.0.37-community-nt on Windows XP. One of the parameters may be NULL and needs to be entered as such, rather than the column's default of 00000 (unsigned zerofill int). The problem is, I can't seem to do this without the statement failing or the default being substituted.

The code is something like this:

$stmt = $db->prepare("UPDATE bookings SET extras = :ex, compoundRef = :cr WHERE ref = :ref;");
$stmt->bindParam(':ex',$ex);
$stmt->bindParam(':cr',$cr);
$stmt->bindParam(':ref',$ref);
foreach($refs as $k => $v) {
	$ex = $v['extras'];
	$cr = ($v['compoundRef'] == '') ? 'NULL' : $v['compoundRef'];
	$ref = $k;
	$debug[] = "$ref, $ex, $cr";
	$stmt->execute() || (print_r($debug) && die("update failed on ref $k"));
}
unset($stmt);

With the aid of the debug code above, I determined that the array member that caused the failure was the first one that had a NULL value for compoundRef.

Prior to this I'd not put the quotes around NULL, which caused the field to be updated to the default 00000 (also no good).

Can anyone advise if there is a right way to do this, that I'm missing? Alternatively, is there a way I can make that one parameter optional, so the statement does not use it if the value is null?

Thanks in advance for any help.

EDIT: Never mind, it was actually another part of my code that was puking when the NULL wasn't quoted. So un-quoted is actually fine...!