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?
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
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.