funny mysql issue i don't understand

Hi,
i'm running into a funny issue i can't understand with a simple query, this

Code:

$this->db->query("update table set field = NULL where id = xx");

doesn't do anything (whether the updated column is alone or aamong other cols, prepared query or not) and, of course, the field is set to be NULL by default
i must admit i do not understand at all why because pastnng exactly the same query in the phpmydamin sql tab query field it works like a charm!
(codeigniter 4.5.3)
if any of you had a lightful idea that would help me understand why my old brain is stuck
have a nice day

edited just because i forgot to say it's a date field

Try:

Code:

$this->db->query("update table set field IS NULL where id = xx");

Hi @JustJohnQ
and thanks a lot for your answer, too bad, i've tried and it doesn't work either
i'd already tried with set field = NULLwhere id = xx and field IS NOT NULL as i knew IS NULL or is not as conditions, i would have loved to see it work as a value but unfortunately, it doesn't
have a nice day

Show your table so that we can see how the field names are setup.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

hi @InsiteFX
and thanks a lot for your answer

here is my field `dye_date` date DEFAULT NULL

(just for you to know, i've been writing php sql query for more than 28 years now, yes started with php3 and i've made some tools with CI3 and CI4 eversince it exists, just to say i think i know a little where i'm going )

now i'v found something funny and a "workaround" to make the query work, it does but only if the only query in my model function!!!

if if put this query after a first one in the function, nothing happens, and beleive me it's not the only function that contains more than one query as too many/complex jointures sometimes ends with bad performance..., same thing if i add this field inside a longer query like

Code:

$why = NULL;
$q = "update $table set field_1 = '', field_2 = '', ..., field_why = ? where id = ?";
$this->db->query($q, array($field_1, $field_2,...., $why, $id));

but alone in it's own function

Code:

$value = NULL;
$q = "update $table set $col = ? where id = ?";
$this->db->query($q, array($value, $id));

it works! first time i run into this funny behaviour but well, in the particular tool i'm working on the workaround may be enough

thanks again for having taken time to answer :
have a nice day

Make sure you are adding the fields to the:

$allowedFields

This array should be updated with the field names that can be set during
save(), insert(), or update() methods. Any field names other than these will
be discarded. This helps to protect against just taking input from a form
and throwing it all at the model, resulting in potential mass assignment
vulnerabilities.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

Hi @InsiteFX
and thanks a lot again
i'll try this and see if it changes the behaviour when updating several field at the sametime as the funny thing is that updating this field alone in a model method works like a charm, be it with a value or NULL
have a nice day

@InsiteFX
just a word to say i've tried to use this allowedFields array but, too bad; it didn't change anything...
i must admit i don't understand where this comes from as why does it work as a single query in its own method and not as a second one in a method neither in a more complete update of several cols... maybe i'll end finding out why...
thanks again for your help
have a nice day