How to insert data using CI active record to field names with space?

Question

How to insert data to the fields with name containing space. eg :

$data = array(
    "First Name" => "Bob",
    "Email ID" => "[email protected]"
);
$this->db->insert("table_name", $data);

Insert batch is also not working.

$data = array(
    array(
        "First Name" => "Bob",
        "Email ID" => "[email protected]"
    ),
    array(
        "First Name" => "Joe",
        "Email ID" => "[email protected]"
    )
);
$this->db->insert_batch("table_name", $data);

This practice is one of those added on "what not to do"... [This](http://stackoverflow.com/a/14190833/1057527) might give you an idea how to do manage space in column name. — machineaddict, Nov 17 '14 at 07:33

score 2 · Accepted Answer · answered Nov 17 '14 at 06:29

2

Check the following code. Which worked for me. use $db->set method

foreach($data as $key=>$val)
{
$this->db->set($key, $val);
}
$this->db->insert('table_name');

answered Nov 17 '14 at 06:29

Mahendra Jella

5,450
1
33
38

1

Thank you. I'll check. I used static SQL using `$this->db->query()` for fixing the issue :) – Mansoorkhan Cherupuzha Nov 17 '14 at 06:37
1

I have checked in my local system. Working perfectly – Mahendra Jella Nov 17 '14 at 06:40
1

And where is the batch? – machineaddict Nov 17 '14 at 07:33
Loop the insert stmt accordingly – Mahendra Jella Nov 17 '14 at 08:04

score 0 · Answer 2 · answered Dec 26 '16 at 15:29

for me ...

foreach($data as $key=>$val)
{
$this->db->set($key, $val);
}
$this->db->insert('table_name');

.. changed nothing! :(

«Adj Close» gets to «'Adj' 'Close'» in the query. with ' as `.

So I had to do this:

foreach ($data as $k => $v) {
    if (strstr($k," ")){
       $this->db->set('`'.addslashes($k).'`', $v, false);
       unset($data[$k]);
    }
}
$returnDB = $this->db->insert($table, $data);

then it worked!

How to insert data using CI active record to field names with space?

2 Answers2