Set value from Something to NULL

QuestionsSet value from Something to NULL
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;

 

 

2 Answers
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 9 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);
_________________________
Abu Ghufran - Dev Team
Grid 4 PHP Framework
 
Your Answer

17 + 12 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?