funny mysql issue i don't understand

funny mysql issue i don't understand - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: funny mysql issue i don't understand (/showthread.php?tid=92800)

Pages: 1 2

funny mysql issue i don't understand - virtualgadjo - 04-25-2025

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 Smile

have a nice day

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

RE: funny mysql issue i don't understand - JustJohnQ - 04-25-2025

Try:

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

RE: funny mysql issue i don't understand - virtualgadjo - 04-25-2025

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 Smile

have a nice day

RE: funny mysql issue i don't understand - InsiteFX - 04-25-2025

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

RE: funny mysql issue i don't understand - virtualgadjo - 04-25-2025

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 Big Grin

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 Smile

)

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 : Smile

have a nice day

RE: funny mysql issue i don't understand - InsiteFX - 04-25-2025

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.

RE: funny mysql issue i don't understand - virtualgadjo - 04-25-2025

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

RE: funny mysql issue i don't understand - virtualgadjo - 04-26-2025

@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

RE: funny mysql issue i don't understand - InsiteFX - 04-26-2025

I just found this not sure if this is your problem, but check it out anyways.

MySQL - 5.10.5 Handling NULL Dates

RE: funny mysql issue i don't understand - virtualgadjo - 04-27-2025

Hi @InsiteFX
and thakns a lot for crushing your brain along with mine Smile

i've tought it may be somerthing like this and have even tried things like where col IS NOT NULL as i try to set this field on null if it is not
but in this case why does it work that well alone in its own method and you know what, it didn't help either
knowing that msqli iq not the best friend of null values and not knowing how CI deals with it i've also tried the full mysqli qtatement like

Code:
$q = $this->db->prepare("update $table set id_color = ?, color = ?, tie_color = ?, flip = ?, dye_date = ? where id = ?");

$q->bind_param("issssi", $id_color, $value, $tie_color, $flip, $dye_date, $id);

$q->execute();

and you know what, same result, all the fields are updated as i want to... except for the date that is supposed to be set to NULL
but, big but and that's where i'm stuck

Code:
$q2 = "update $table set dye_date = ? where id = ?";

$this->db->query($q2, array($value, $id));

continues to work very well alone in its own method table and value being post values i receive in the controller (i'm sure you'd guessed Smile

) when it doesn't if i put this as a second statement in the same metjhd after the first one removing the dye_date field things from the first query and params, funny isn't it?
have a nice day