Matt Shaw asked 2 years ago
I am unable to set a value from “Something” to NULL when using a database lookup. My lookup includes a k value of NULL, and a v value of “—“. The column (“sheet_scanner”) in table “booking” is of type integer, being a foreign key, but displays text from the foreign table (“contact”).
The problem is that the generated update query puts quotes around null i.e. … sheet_scanner = ” …
$col[“isnull”] = true; doesn’t help
Full error message:
Couldn’t execute query. ERROR: invalid input syntax for type integer: “” LINE 1: UPDATE leatherhead.booking SET “sheet_scanner”=” WHERE “boo… ^ – UPDATE leatherhead.booking SET “sheet_scanner”=” WHERE “booking_date” IN (‘2023-03-10’)
The lookup is based on this view. It ensures that ‘—‘ is first, followed by contacts ordered by last_name
create or replace view contact_vw as
select k, v from
(select contact_id as k, concat(first_name,’ ‘, last_name) as v, last_name from contact
union
select null, ‘—‘, ‘aaaaa’) x
order by last_name;
Mike answered 2 years ago
I may be mistaken, but if I understand what you’re trying to do, I think you need to use a custom On Update / On Add function to handle this. The PHPGrid may-not execute a NULL condition in this case.
I handle it by setting a custom Update query, then checking for a NULL / BLANK condition, then setting the Update Query appropriately.
If($sheet_scanner<>” && $sheet_scanner <> ‘NULL’) {
$s = “sheet_scanner = ‘$sheet_scanner’, “;
} else {
$s = “sheet_scanner = NULL, “;
}
Thanks
Mike
Abu Ghufran Staff answered 10 months ago
Just to add a solution,
If you set value option as NULL:— it will try to put NULL value without quotes in update query.
$str = $g->get_dropdown_values("select distinct client_id as k, name as v from clients");
$col["editoptions"] = array("value"=>"NULL:;".$str);
