How to update table with column set NULL in codeigniter

Question

i have a problem with update column with set data to NULL, i have a table user with each one column is id_parent column, user can delete parent and can add parent, so if user have a parent, id_parent will set with user's parent id , but user can delete parent's data, so if user delete parent's data, id_parent column, will set to NULL. so how to set data to null in database not " " but NULL. here's my user table.

user

id_user | name | address | id_parent

Solution 1

use this to set null column.it will work, dont forget add false on third parameter SET on active record

$this->db->set('id_parent', 'NULL', false);

Solution 2

you can try this code in 'model', hope it'll work:

public function update_std_marks($id_user) {

    $this->db->set('id_parent', null);
    $this->db->where('id_user', $id_user);
    $this->db->update('user');
}

Solution 3

I was curious so I wanted to see how CodeIgniter's Active Record would handle different variations of syntax when working with a null value. I'll map out several test cases for where() and set() method calls.

Battery of where() tests:

1. ->where('col', null)                  // `col` IS NULL
   

2. ->where('col', null, false)           // col IS NULL
   

3. ->where('col', 'NULL')                // `col` = 'NULL'
   

4. ->where('col', 'NULL', false)         // col =  NULL
   

5. ->where('col IS NULL')                // `col` IS NULL
   

6. ->where('col IS NULL', null)          // `col` IS NULL
   

7. ->where('col IS NULL', null, false)   // col IS NULL
   

8. ->where('col', 'IS NULL')             // `col` 'IS NULL'
   

9. ->where('col', 'IS NULL', false)      // col = IS NULL
   

Properly quoted and formed: #1, #5, #6 (recommended)
Logically formed, but not quoted: #2, #7 (not recommended)
Inappropriate rendered syntax: #3, #4, #8, #9 (do not use)

---

Battery of set() tests:

1. ->set('col', null)                  // `col` = NULL
   

2. ->set('col', null, false)           // col =
   

3. ->set('col', 'NULL')                // `col` = 'NULL'
   

4. ->set('col', 'NULL', false)         // col = NULL
   

5. ->set('col IS NULL')                // `col IS` `NULL` = ''
   

6. ->set('col IS NULL', null)          // `col IS` `NULL` = NULL
   

7. ->set('col IS NULL', null, false)   // col IS NULL =
   

8. ->set('col', 'IS NULL')             // `col` = 'IS NULL'
   

9. ->set('col', 'IS NULL', false)      // col = IS NULL
   

Properly quoted and formed: #1 (recommended)
Logically formed, but not quoted: #4 (not recommended)
Inappropriate rendered syntax: #2, #3, #5, #6, #7, #8, #9 (do not use)

Solution 4

Your user table must have the attribute set to is_null in the id_parent field. Otherwise you can't.

The query should be something like

Update table user set id_parent = null where id_user = X

You can set that column nullable with this query:

ALTER TABLE user MODIFY id_parent int(11) null;

Try this for codeigniter:

$fields = array(
                        'id_parent' => array(
                                                         'name' => 'id_parent',
                                                         'type' => 'INT',
                                                ),
);
$this->dbforge->modify_column('user', $fields);

or simply:

$this->db->query('ALTER TABLE user MODIFY id_parent int(11) null;');

View more solutions

Comments

[nanasjr]

so i can user the alter table/? it's same with use active record in codeigniter?