Take the 2-minute tour ×

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?

share|improve this question
Did you try set a='$a'? If $a is empty, it will attempt to insert an empty value. If your table accepts null values, it should work. –  Fabrício Matté May 12 '12 at 4:36
@FabrícioMatté: NULL and empty '' are very different values. –  Erwin Brandstetter May 12 '12 at 5:12
Oh I see. I haven't been working with PgSQL for a while so pardon my ignorance. I just meant that, when fetched in the php, if you compare it with if (!$value) both would be parsed as false. –  Fabrício Matté May 12 '12 at 5:25
add comment

3 Answers

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.

share|improve this answer
In postgres a='' throws an error. I have to explicitly set it to $a= null but I dont know how to dynamically pass null. I can pass 'null' with the quotes but not just null. –  pogo May 12 '12 at 4:47
I edited my answer. I'll setup a table on my PG server to test it meanwhile. –  Fabrício Matté May 12 '12 at 4:51
I finished editing it now, give it a try. 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. –  Fabrício Matté May 12 '12 at 5:05
I just did a 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.. –  Fabrício Matté May 12 '12 at 5:17
NULL shouldn't be quoted by the way. with the $a and $query declared in my answer it shouldn't. –  Fabrício Matté May 12 '12 at 5:21
show 3 more comments

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
share|improve this answer
add comment
ALTER TABLE `your_table_name` CHANGE `a` `a` INT( 11 ) NULL

Run the query above in MySQL.

share|improve this answer
Question is tagged PostgreSQL. Your answer is off topic. –  Erwin Brandstetter May 12 '12 at 5:10
add comment

Your Answer


