how to delete a single semicolon separated value from database in Codeigniter

Question

My Database table is like below, table name is add_family :

id    ,   Head_id   ,Head_Name   , CustomerName    customer_id                   
1     ,   2589      ,   Mitesh   ,  anuj^anil   ,  456^259  
2     ,    2590     ,  vijay     ,  amit^ajay   ,  852^454  

if I want to delete any of customer name and its id then how can I delete it in CodeIgniter? e.g if I want to delete only CustomerName Anuj and his customer_id 456 where Head_id is 2586,

how can I delete it?

Answer 1

What you should do is use query builder class and load database library.

Here's an example from official docs:

$data = array(
        'CustomerName' => 'anil',//or NULL if you want to 'delete it'
        'customer_id' => '259'//or NULL if you want to 'delete it'
);

$this->db->where('Head_id', 2586);
$this->db->update('add_family', $data);

If you want to do it dynamically and you always want the part after the ^ you can use , for example, the php explode function like this:

$this->db->select('CustomerName, customer_id');
$this->db->where('Head_id', 2586); 
$query = $this->db->get('add_family');
$result = $query->row_array(); 

$data = array(
            'CustomerName' => explode('^', $result['CustomerName'])[1],
            'customer_id' => explode('^', $result['customer_id'])[1]
    );

$this->db->where('Head_id', 2586);
$this->db->update('add_family', $data);

Note that you should not update the ID of a table if it's the primary key.

Answer 2

Try something like this. Idea is that to get value from database of both field and replace customer name with empty string

$query = $this->db->get_where('add_family',array('id'=>1));
$name = str_replace("anuj","", $query['CustomerName']);
$id = str_replace(456, "", $query['customer_id ']);

$data = array(
        'CustomerName' => $name,//or NULL
        'customer_id' => $id//or NULL
);

$this->db->where('Head_id', 2586);
$this->db->update('add_family', $data);