Php - Insert Multiple Rows in Ci

Question

I have an array ['abc','xyz'];

I want to insert two rows but in one go

I dont want

loop(){
 $this->db->insert()
 }

this will run insery query two times

Using CI Framework and this array comes from user

@Daan This is something manual taking values from input in array what can i do? — Neeraj Walia, Oct 30 '18 at 10:08
Last time I used CI I'm quite sure you could insert arrays of information to the DB — WKoppel, Oct 30 '18 at 10:09
Please read the manual before asking, I found this on google within 10 seconds: `insert_batch` https://www.codeigniter.com/userguide3/database/query_builder.html#inserting-data — Daan, Oct 30 '18 at 10:09
@Daan I know about this insert_batch() but how to convert my array into the format this method wants not manually keeping in mind that this array comes from user so its length can be upto 100 index also — Neeraj Walia, Oct 30 '18 at 10:11
@NeerajWalia Check my answer, haven't tested it. But should work fine. — Daan, Oct 30 '18 at 10:22
Duplicate - https://stackoverflow.com/questions/17875706/how-to-create-codeigniter-batch-insert-array — Manoj Singh, Oct 30 '18 at 13:31
@ManojSingh In your link there is no way defined like how to create that array dynamically it is created static , so its no way duplicate — Neeraj Walia, Oct 31 '18 at 07:18

score 1 · Answer 1 · answered Oct 30 '18 at 10:26

1

foreach ($this->input->post("name") as $value) {        
            $name[] = array(
                'name'=>$value,
            );
        }
$this->db->insert_batch("names",$name);

answered Oct 30 '18 at 10:26

Neeraj Walia

208
3
16

would you like to explain the code so that the OP decides why he needs to update the code to this one – Muhammad Omer Aslam Oct 30 '18 at 11:36
Thanks for your answer. Please provide some description to your code-example. – Sven Rojek Oct 30 '18 at 13:40

score 0 · Answer 2 · answered Oct 30 '18 at 10:12

0

yourModel.php

public function urfunctionName($data)
{
  foreach($data as $res)
  {
     $this->db->insert('table_name',$res);
  }
}

Hope it will work you

answered Oct 30 '18 at 10:12

Ramesh S

841
3
15
35

I told you not to do this in loop – Neeraj Walia Oct 30 '18 at 10:13
Show me your expected result – Ramesh S Oct 30 '18 at 10:15
1

I will get same results but your code will run query equal to length of my array like if array has 100 indexes it will run 100 times which is not good i want to insert 100 rows in one run thanks for help insert_batch() did it thankyou all – Neeraj Walia Oct 30 '18 at 10:25

score 0 · Answer 3 · answered Oct 30 '18 at 10:21

You'll need to build your 'batch' insert query first. After that call the insert_batch method.

$array = ['abc','xyz'];
$batchInsertArray = buildBatchInsertArray($array);

$this->db->insert_batch('myTable', $batchInsertArray);

function buildBatchInsertArray(array $array): array
{
    $batchInsertArray = [];

     foreach ($array as $item) {
       $batchInsertArray[] = [
           'columnName' => $item
       ];
    }

    return $batchInsertArray;
}

score 0 · Answer 4 · answered Oct 30 '18 at 11:40

$data = array(
        array(
                'title' => 'My title',
                'name' => 'My Name',
                'date' => 'My date'
        ),
        array(
                'title' => 'Another title',
                'name' => 'Another Name',
                'date' => 'Another date'
        )
);

$this->db->insert_batch('mytable', $data);

// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date'),  ('Another title', 'Another name', 'Another date')

Referance :: query_builder inserting-data

Php - Insert Multiple Rows in Ci

4 Answers4