I have a table like this
CREATE TABLE test(a int, b int);
The values to the table are inserted from dynamic input
$a, $b (PHP variables)
$query = "UPDATE test set a=$a where b = $b";
pg_query($db, $query);
This doesn't work when $a is empty (i.e when the user doesn't enter any value for a form field from the table). How to I get around this?
Test if $a is empty and if so set it to null:
$a = ($a == '') ? 'NULL' : "'$a'";
$query = "UPDATE test set a=$a where b='$b'";
Don't forget to quote the $b value, it was returning an error here until I quoted it.
I added quotation to $a in the check above if it's not null, so it will now work if $a is either NULL (empty), an integer or string.
NULL shouldn't be quoted otherwise it'll be inserted as a literal string, so I added quotation to $a when it's not null in the check above.UPDATE test set a=NULL where b='2' in the table generated with your SQL in the first post and it worked just fine. Make sure your field is accepting null values. Also, you can try putting a die($query); right after the $query line to make sure it looks like the one I just posted in this comment. Maybe I'm just too sleepy to notice something really obvious..$a and $query declared in my answer it shouldn't.If $a is empty, $query will look like:
UPDATE test SET a= where b = whatever
You need to explicitly add NULL to the query string:
UPDATE test SET a=NULL where b = whatever
set a='$a'? If $a is empty, it will attempt to insert an empty value. If your table accepts null values, it should work.NULLand empty''are very different values.if (!$value)both would be parsed as false.