How to replace value with comma while using update query in codeigniter?

Question

$data = array(
             'recruiter_id'=>REPLACE(recruiter_id,201812101140,'')
         );
$where = "jid='".$this->input->post('jid')."'";
$this->db->where($where);
$this->db->update('job_registration',$data);
echo $this->db->last_query();

I have recruiter ids like 2018121011430, 201812101140, 201812101141. Now, I want to remove 201812101140 using an update query. Here is my query but it doesn't work. How can I remove 201812101140 from the column recruiter_id?

_Don't_ try to do this! _Normalize_ your data, and change your table structure so that a single `recruiter_id` appears on each record. — Tim Biegeleisen, Dec 10 '18 at 06:20
@TimBiegeleisen please look at my query once this query show me ` 0 rows affected` — Bruce, Dec 10 '18 at 06:35
You are completely ignoring everything I said in my comment, therefore I won't comment further. — Tim Biegeleisen, Dec 10 '18 at 06:37
Please read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad! — Nigel Ren, Dec 10 '18 at 06:45

score 0 · Answer 1 · answered Dec 10 '18 at 06:47

In mysql replace function syntax is REPLACE(field_name,string_to_find, string_to_replace).

You have to replace comma with space. Your query should look like this.

UPDATE job_registration SET recruiter_id=replace(recruiter_id,',','') WHERE job_id='jid1205161020' and recruiter_id like '%20181130070940%'

score -1 · Answer 2 · answered Dec 10 '18 at 06:27

-1

$data = array(
    'recruiter_id' => ''
);
$this->db->where('jid', $this->input->post('jid'));
$this->db->where('recruiter_id', 201812101140);
$this->db->update('job_registration', $data);

answered Dec 10 '18 at 06:27

EvE

734
3
13

@Bruce Can you post an example row which you would want to replace – EvE Dec 10 '18 at 06:44
1

Please add some explanation - what do you want to tell us with your source code? – Nico Haase Dec 10 '18 at 07:18

How to replace value with comma while using update query in codeigniter?

2 Answers2