Codeigniter 4 query IF ISNULL

Question

I am migrating from Codeigniter 3 to Codeigniter 4 and the next query is problematic to me.

In Codeigniter 3, the query was:

$this->db->query("UPDATE myTable SET data = '$data', 
                    myData = IF ( ISNULL(myData), '$state', CONCAT('$state', '-', myData) ) 
                  WHERE id = '$id'");

I tried to change this with the next format:

$data = array(
    'myData' => "IF( ISNULL(myData), " . $state. ", CONCAT(" . $state. ", '-', myData')")
);

However, the function updates the content of $myData like a string "IF( ISNULL(myData), " . stateOfExample. ", CONCAT(" . stateOfExample. ", '-', myData')".

I have looked into the CI4 documentation but I could not find the way to develop this query. I find the way to do that with the where() CI function, but I think is not the best way to do that.

Ergec · Accepted Answer · 2022-01-11 14:00:12Z

1

If you put SQL functions in data, codeigniter won't understand that those are functions. It just adds them as string.

Put IF, CONCAT, ISNULL in sql and use data array just for variables.

$this->db->query("UPDATE myTable SET data = ?, 
    myData = IF ( ISNULL(myData), ?, CONCAT(?, '-', myData) ) 
    WHERE id = ?", array($data, $state, $state, $id));

answered Jan 11, 2022 at 14:00

Ergec

11.9k7 gold badges54 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sones1 Over a year ago

This kind of structures works in Codeigniter 3, but as i said i am migrating to Codeigniter 4, that's not follow the Codeigniter queries structure

Ergec Over a year ago

What do you mean by that? This is from the docs codeigniter.com/user_guide/database/queries.html#query-bindings

Sones1 Over a year ago

You are right, this is a valid query structure in CI4, but it does not work to me

Collectives™ on Stack Overflow

Codeigniter 4 query IF ISNULL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related